MySql Page

[driveflexfuel]

I am creating a special shopping cart script and I am having a hard time learning MySQL. I have a database with one table and 3 fields. I simply wish to display all entries with a delete link after each one. I started this code if someone could help me along it would be greatly appreciated. I got kinda lost. I am offering up 500 credits for a sample page.

    $db_server = "localhost";
    $db_name = "database";
    $db_user = "username";
    $db_pass = "password";
    
    $con1 = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
    mysql_select_db($db_name, $con1);
    
    $query_Results = ("SELECT * FROM customers ORDER BY Field3");
    $SearchResults = mysql_query($query_Results) or die(mysql_error());
    $row_SearchResults = mysql_fetch_assoc($SearchResults);
    $totalRows_SearchResults = mysql_num_rows($SearchResults);

 

[sarvar]

I'm just throwing this outta my head, so if you have problems with this post it and improve it to your needs. Like name of the items, price, etc.

DB
 *cart
   -ip
   -id

 *items
   -id
   -name
   -desc

// Get user ip
$ip = $_SERVER['REMOTE_ADDR'];

// Select customer items
$select_items = mysql_query("SELECT * FROM `cart` WHERE `ip` = '".$ip."'");

$array_items = array();
while($row = mysql_fetch_array($select_items) {

  $array_items[] = $row['id'];

}

foreach ($array_items AS $shop_items) {

$get_item_info = mysql_query("SELECT * FROM `items` WHERE `id` = '".$shop_items."'");
$get_item_rst = mysql_fetch_assoc($get_item_info);

echo '$get_item_rst['name'] -- $get_item_rst['desc']          <a href="remove.php?id=$shop_items">REMOVE</a>';

}

Not tested. You may want to improve it.

 

[gomarc]

The table and data we are using for this example:

-- phpMyAdmin SQL Dump
-- version 2.11.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 21, 2009 at 12:17 PM
-- Server version: 5.0.67
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `help`
--

-- --------------------------------------------------------

--
-- Table structure for table `scart`
--

CREATE TABLE IF NOT EXISTS `scart` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `item` varchar(10) NOT NULL,
  `Description` varchar(30) NOT NULL,
  `price` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `scart`
--

INSERT INTO `scart` (`id`, `item`, `description`, `price`) VALUES
(10031, '64L-00003', 'Microsoft LifeCam VX-1000 Webc', '22.49'),
(10032, 'D6600029', 'Microsoft Optical Wheel Mouse-', '14.49'),
(10033, 'Q8061A#ABA', 'HP Officejet 6310 All-in-One p', '67.58'),
(10034, 'SDSDRH-004', 'SanDisk 4GB Ultra II Secure Di', '14.99');

The php file (show_cart.php) to show/remove items:

<?php

$db_server = "localhost";
$db_name = "database";
$db_user = "username";
$db_pass = "password";

$con1 = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
    mysql_select_db($db_name, $con1);
    
$query_Results = ("SELECT * FROM scart ORDER BY id");
$SearchResults = mysql_query($query_Results) or die(mysql_error());
$row_SearchResults = mysql_fetch_assoc($SearchResults);
$totalRows_SearchResults = mysql_num_rows($SearchResults);
mysql_close();

?>

<h2>Display Shoping Cart</h2>
<table width='550' cellpadding='0' cellspacing='1'>

    <tr style='height: 20px;'>
            <th width='50'>&nbsp;</th>
            <th width='100'>Item Code</th>
            <th width='300'>Description</th>
            <th width='50'>Price</th>
            <th width='50'>Action</th>
  </tr>


<?php

// Loop results
$i=0;
while ($i < $totalRows_SearchResults) 
{
 
 $tb_id    = mysql_result($SearchResults, $i, 0);
 $tb_item  = mysql_result($SearchResults, $i, 1);
 $tb_desc  = mysql_result($SearchResults, $i, 2);
 $tb_price = mysql_result($SearchResults, $i, 3);
 
 $tb_orde = $i + 1
 
?>
<tr>
 <td align='center'>&nbsp;<?php echo $tb_orde; ?></td>
 <td>&nbsp;<?php echo $tb_item; ?> </td>
 <td>&nbsp;<?php echo $tb_desc; ?> </td>
 <td>&nbsp;<?php echo $tb_price; ?> </td>
 <td>&nbsp;<a href="remove.php?rid=<?php echo $tb_id; ?>">Remove</a></td>
</tr>

<?php
 
++$i;
}

?>

</table>

The remove.php file is

<?php

$record = $_GET["rid"];

$db_server = "localhost";
$db_name = "database";
$db_user = "username";
$db_pass = "password";

$con1 = mysql_connect($db_server, $db_user, $db_pass) or die(mysql_error());
    mysql_select_db($db_name, $con1);
    
$query_Results = ("DELETE FROM scart WHERE id = $record");
$SearchResults = mysql_query($query_Results) or die(mysql_error());
mysql_close();

//Go Back to cart
include("show_cart.php");

?>

Edit:
Please note that the previous code is not intended to be open to the www, it’s just intended to be a demonstration. This code would be open to all kind of abuse and exploitation.

Actually, it’s not a good idea for a user to be able to “delete” records from a table or database. It’s much better to mark the records for deletion and hide them so the user can’t see them anymore.

 

[driveflexfuel]

works like a charm thanks a lot.

 

[gomarc]

Thanks for the credits driveflexfuel!