chibineku Posted October 2, 2009 Share Posted October 2, 2009 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 More sharing options...
justsomeguy Posted October 2, 2009 Share Posted October 2, 2009 Check here:http://dev.mysql.com/doc/refman/5.0/en/ins...-duplicate.html Link to comment Share on other sites More sharing options...
chibineku Posted October 3, 2009 Author Share Posted October 3, 2009 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 More sharing options...
chibineku Posted October 5, 2009 Author Share Posted October 5, 2009 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 More sharing options...
chibineku Posted October 5, 2009 Author Share Posted October 5, 2009 Dude, sweet. I got it. Awesome. These guys thought of everything. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.