Updating SQL table using PHP problem

[samus]

Hey there again,

 

I am trying to make a shopping cart from scratch (college assessment) however I am having a problem with updating the quantity of an existing item on the customers cart.  Any help would be great.

 

On the home page, each item (from the database) has an "ADD ITEM" button in which it shall direct the user to the "cart" page, along with hidden values of quantity = '1' and the item's unique item number.

 

On the cart page I have successfully created a function that shall first search through the cart table (which contains, unique id (auto_increment), username, item, name, cost, description, image, imagelarge, quantity)

for the row which contains the "username" AND "item" previously POSTED from the home page.

 

With the results I define the function to only UPDATE the row when the row number is more than 0 (since then I would have to INSERT the order if there wasn't any rows already in the table).

 

Here is the following code.

 

<?php
//call to database connection file
include ("connect_inc.php");
// getting the unique username from cookie
$username=$_COOKIE['username'];
// getting posted data from previous page to know what exactely to order
$quantity = $_POST['quantity'];
$item = $_POST['item'];
// testing
echo "$item";
?>

Table contents....

<?php
//query the data in the cart table to later manipulate
$query3="SELECT * FROM cart WHERE username = '$username' AND item = '$item'";
$resultagain=mysql_query($query3) OR DIE("Your SQL: " . $query3 . "<br/> Produced this Error: " . mysql_error());
$nums=mysql_num_rows($resultagain);

if ($nums > 0){

$i=0;

	//set the results into variables
	$id =mysql_result ($resultagain, $i, "id");
	$username = mysql_result ($resultagain, $i, "username");
	$item = mysql_result ($resultagain, $i, "item");	
	$oldquantity=mysql_result ($resultagain, $i, "quantity");
	$newquantity= $quantity + $oldquantity;

	echo "$id";

//Update data in Cart
$query4 ="UPDATE cart SET quantity = '$newquantity' WHERE  id = '$id'";
$result4=mysql_query($query4) OR DIE("Your SQL: " . $query4 . "<br/> Produced this Error: " . mysql_error());

	if (mysql_affected_rows() == 1){
			echo "<font='black'>This item has been added to your cart again!</font>";
			echo "<table width='400px'  border='0' cellspacing='1' cellpadding='0'>";
			echo "<tr>";
			echo "<td rowspan='4'><img src='$image' height='100px' width='100px' title='click image for larger picture' onclick=\"javascript: openwindow(\"$largerimage\" \"height=500px, width=500px\")\" ></td>";
			echo "<td><b>$name</b></td>";
			echo "</tr>";
			echo "<tr>";
			echo "<td>£ $cost</td>";
			echo "</tr>";
			echo "<td>$description</td>";
			echo "</tr>";
			echo "</tr>";
			echo "</table>";
			}
			else { echo "echo?";}
			}
else 
	{ 

//query to the cd table, to gain the item variables
$query="SELECT * FROM cds WHERE item = '$item' ";
$result=mysql_query($query)  OR DIE("Your SQL: " . $query . "<br/> Produced this Error: " . mysql_error());

	$i=0;
	//set the results into variables
	$item=mysql_result ($result, $i, "item");
	$name=mysql_result ($result, $i, "name");
	$cost=mysql_result ($result, $i, "cost");
	$description=mysql_result  ($result, $i, "description");
	$image=mysql_result ($result, $i, "image");
	$largerimage=mysql_result ($result, $i, "largerimage");

//insert the data in the cart table to later manipulate
$query2="INSERT INTO cart (username, item, name, cost, description, quantity) VALUES ('$username', '$item', '$name', '$cost', '$description', '$quantity')";
$results=mysql_query($query2) OR DIE("Your SQL: " . $query2 . "<br/> Produced this Error: " . mysql_error());


//Just to confirm the data has been inserted into the table
	if (mysql_affected_rows() == 1) {

			echo "<font='black'>This item has been added to your cart!</font>";
			echo "<table width='400px'  border='0' cellspacing='1' cellpadding='0'>";
			echo "<tr>";
			echo "<td rowspan='4'><img src='$image' height='100px' width='100px' title='click image for larger picture' onclick=\"javascript: openwindow(\"$largerimage\" \"height=500px, width=500px\")\" ></td>";
			echo "<td><b>$name</b></td>";
			echo "</tr>";
			echo "<tr>";
			echo "<td>£ $cost</td>";
			echo "</tr>";
			echo "<td>$description</td>";
			echo "</tr>";
			echo "<tr>";
			echo "<td>$quantity</td>";
			echo "</tr>";
			echo "</table>";
			}
			else 
			{echo "goodbye";}

}
?>

 

Now what I am getting is that, I can successfully insert a new row with the right values.  However, when the row is already there, it DOES UPDATE the quantity, but ALSO inserts a new row, with no values except the username and quantity = 0.

 

How can I get rid of this... ghost row? 

[Corona4456]

You can do several things but the easiest ways would be:

 

• Make the rows to be unique by forcing one of fields to be unique, in this case 'item'.  This will produce an error when you try to insert two items of the same name and then you can handle it that way.
  
• Do a quick SELECT check on the database for the specific item before you add it to the database.  If it exists then just update the quantity
  
• Do a delete query on the specific item where the quantity is 0? This one is iffy since I don't know if you insert other items with a quantity of 0, if so then this method wouldn't work.
  

[samus]

Yeah, I already made the function do exactely what your first two points were, and I was considering to that last point since, I have no clue as to why a ghost row is being made.

 

I was wondering if the error (i.e. that ghost row) is because I haven't updated the table right, but it seems correct if it IS updating, BUT it is ALSO insert a new row but with out any values (as I said) except username and quantity = 0

 

*confused big time*

[Corona4456]

Hmm... that's odd... if you make a field unique then it shouldn't be able to have 2 rows with the same item in it.