Updating SQL if item exists

[petenaylor]

Hi all

 

I am trying to write a piece of code that checks to see if an item is in the SQL database and updates the quantity if it is, or adds it as a new line if it isn't. Here's my code:

 

if (isset($_GET['add'])) {

$id = $_GET['id'];
$qty = $_GET['qty'];

$date = date ('D M j G:i:s');

$sql_products = mysql_query("SELECT * FROM `items` WHERE `id` = '".$id."'");
$result_products = mysql_fetch_array($sql_products);
$product_title = $result_products['title'];
$product_price = $result_products['price'];

$sql_inbasket = mysql_query("SELECT * FROM `store_basket` WHERE `id` = '".$id."' AND sessionid = '".$sessionid."'");
$isiteminbasket = mysql_num_rows($sql_inbasket);

if ($isiteminbasket == 0) {

mysql_query ("INSERT INTO `store_basket` SET sessionid = '".$sessionid."', productid = '".$id."', item = '".$product_title."', qty = '".$qty."', price = '".$product_price."', date = '".$date."'");

header("Location: basket.php");
}

else {

mysql_query("UPDATE `store_basket` SET qty = qty + 1 WHERE id = '".$id."' AND sessionid = '".$sessionid."'") ;

header("Location: basket.php");
}

}

 

Every time I try and add the same item to the basket, it adds as a new line instead of updating the existing line.

 

Many thanks for your help!

 

Pete

[silkfire]

How does your database look like?

[monkeytooth]

Im going to assume this is for multi user use. So, what you should do, is store it in the table with the a unique token to the user (maybe thats what the session bit is for). But before updating or inserting, do a query on the DB for that unique token to the person AND the id of the product in question, the query in this case would be a simple count query. Maybe on the table if your are having it keep track of how many the person is buying of any one column set a separate column for that amount. But if the count for the 2 unique variables is found then update the count, if not then insert into.

[petenaylor]

Here is what my table in the SQL looks like:

 

[attachment deleted by admin]

[petenaylor]

Fixed it! Here's the working code:

 

if (isset($_GET['add'])) {

$id = $_GET['id'];
$qty = $_GET['qty'];

$date = date ('D M j G:i:s');

$sql_products = mysql_query("SELECT * FROM `items` WHERE `id` = '".$id."'");
$result_products = mysql_fetch_array($sql_products);
$product_title = $result_products['title'];
$product_price = $result_products['price'];


$sql_inbasket = mysql_query("SELECT * FROM `store_basket` WHERE productid = '".$id."' AND sessionid = '".$sessionid."'");
$isiteminbasket = mysql_num_rows($sql_inbasket);

if ($isiteminbasket == 0) {

mysql_query ("INSERT INTO `store_basket` SET sessionid = '".$sessionid."', productid = '".$id."', 

item = '".$product_title."', qty = '".$qty."', price = '".$product_price."', date = '".$date."'");

header("Location: basket.php");	

}

else {

mysql_query("UPDATE `store_basket` SET qty = qty + 1 WHERE productid = '".$id."' AND sessionid = '".$sessionid."'") ; 

header("Location: basket.php");		

}
}