Jump to content

Mysql Insert Or Update Query Help


chibineku
 Share

Recommended Posts

I have a table, sessionBasket, that holds a list of the items in the shopping baskets of visitors to my site. It looks like:id INT NOT NULL AUTO_INCREMENT PRIMARY KEYusersessid VARCHARdate_added DATETIMEproduct_id INTqty INTMy add to basket script first checks for the presence of an item with the current product_id in this table associated with the usersessid and if it finds one, it updates the qty. If not, a separate query inserts a new row with the relevant information. I have since discovered there is a condition ON DUPLICATE KEY UPDATE but I'm not sure what I need to change to cause this to function correctly. I would need two keys here - product_id and usersessid, and if there is a row with both of these matching the ones I'm trying to insert, the update condition is made. I am sure there is a better way to do it than I already do. In addition, I check the product_id is valid in case it is spoofed somehow, so overall I make two queries just to check stuff, and then another to do the update/insert.Here are the separate queries:

//do select query to verify item id$check_sql = "SELECT * FROM aromaProducts1 WHERE id='".intval($_GET["productid"])."'";$check_res = mysqli_query($mysqli, $check_sql) or  error_log(mysqli_error($mysqli)."\r\n");  //do select query to check for item id already in basket  $duplicate_sql = "SELECT qty FROM sessionBasket WHERE product_id='".intval($_GET["productid"])."' AND usersessid='".session_id()."'";  $duplicate_res = mysqli_query($mysqli, $duplicate_sql) or  error_log(mysqli_error($mysqli)."\r\n");	//item in basket - add another	$add_sql = "UPDATE sessionBasket SET qty=qty+".intval($_GET["qty"])."  WHERE usersessid='".session_id()."'AND product_id='".intval($_GET["productid"])."'";	$add_res = mysqli_query($mysqli, $add_sql) or  error_log(mysqli_error($mysqli)."\r\n");  //insert query  $insert_sql = "INSERT INTO ".$table." (userid, usersessid, date_added, product_id, qty, notes) VALUES (  '".$userid."',  '".session_id()."',  now(),  '".htmlspecialchars($productid)."',  '".intval($_GET["qty"])."',  '".htmlspecialchars($notes)."')";  $insert_res = mysqli_query($mysqli, $insert_sql) or  error_log(mysqli_error($mysqli)."\r\n");

Please no replies about SQL injection - my sanitizing is much more thorough than these snippets let on!Any help shrinking these down would be fantastic - I'm a noob at database queries (though I just got The Manga Guide To Databases, so I'm well on my way...uh...or not).

Link to comment
Share on other sites

Well, that page is quite clear: it is best to avoid using ON DUPLICATE KEY UPDATE on tables with more than one unique field. I have been wondering if perhaps there is a better way of oranising my tables, but until then, the way I have it is the best it's going to get, I suppose.

Link to comment
Share on other sites

Hm. I still feel like this function can be made to work with tweaks to my table, but since I use two identifiers to decide what row to update, I'm not able to work out what to do. Perhaps my tables aren't normalized enough? From the brief structure I mentioned in the first post, I need to make sure that userid and product_id match to do an update, and if not, then I do an insert. Perhaps I could concatenate the userid and product id to create a single unique reference?

Link to comment
Share on other sites

Dude, sweet. I got it. Awesome. These guys thought of everything.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...