[RESOLVED] SQL UPDATE or INSERT on compound key.

jerry_louise

// Instert a frech product to the order
		$result = mysql_query("UPDATE `Order Items`  SET `Qty`=`Qty`+1 WHERE `ProductID` = '$productid'");
		if (mysql_affected_rows()==0) 
		{
			$result = mysql_query("INSERT INTO `Order Items` (`OrderNumber`,`ProductID`,`Qty`) VALUES 
				('$ordernumber','$productid','1')");
		}

This has been buging me till daylight, how do i get it to update if a record is already found with a compound key or ProductID and OrderNumber?

bradgrafelman

That depends... can you tell us a little bit more about your DB structure and/or what you're trying to accomplish?

If you want only one single row for a given OrderNumber/ProductID combination, you could add a UNIQUE key to both of those columns (one key, two columns) and use the INSERT INTO.. ON DUPLICATE KEY syntax to update the quantity when a duplicate row is found.

jerry_louise

Sure the Database has 5 tables.

Authors
Customers
Order Items
Orders
Products

The concentration of this is Order Items and Orders. In the Order items both ProductID and OrderNumber are the key and it has to be made as a compound key. Unfortunately my lecturer does not know PHP and refuses to learn it even though this is SQL.

Order Items
OrderNumber
ProductID
Qty

Orders
OrderNumber
UserID
OrderDate
DeliveryMethod
DeliveryCharge

bradgrafelman

As I suspected, it looks like my model above holds true; you first INSERT a row into "Orders" containing basic information about the order and also generating an "OrderNumber" at that time.

Assuming that you correctly set either a PRIMARY or UNIQUE KEY on the "OrderNumber" and "ProductID" columns, all you need to do is utilize the "ON DUPLICATE KEY UPDATE" syntax above to update Qty=Qty+1 when a duplication occurs.

EDIT: In other words, you get rid of the UPDATE query you're doing and simply execute the same INSERT query you already have with the addition of the "ON DUPLICATE KEY UPDATE" syntax tacked onto the end of it.

jerry_louise

Well it turned out to be simple with my method i just forgot something

$update = mysql_query("UPDATE `Order Items`  SET `Qty`=`Qty`+1 WHERE `ProductID` = '$productid' [B]AND `OrderNumber` = '$ordernumber'[/B]");

bradgrafelman

While that will work, note that it'd be cleaner and more efficient to go with the single-query approach.

jerry_louise

I will look again and Duplicate on primary key later when i have more time, i know it does not work on every version of MySQL.