Geek Answers Handbook

ON DUPLICATE KEY UPDATE implementation / design

I have a sessionBasket table that contains a list of items in the shopping carts of visitors to my site. It looks like this:

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
usersessid VARCHAR
date_added DATETIME
product_id INT
qty INT

My adding to the cart script first checks for the element with the current product_id in this table associated with useressid, and if it finds it, it updates the quantity. If not, a separate request inserts a new line with the relevant information.

Since then, I have discovered that there is an ON DUPLICATE KEY UPDATE clause, but I'm not sure what I need to change to make this function correctly. I need two keys here - product_id and usersessid, and if there is a line with both of those matching the ones I'm trying to insert, the update condition is met. I am sure there is a better way to do this than I already do. In addition, I check that product_id is valid if it is somehow faked, so in general I make two requests only for verification, and then another for update / insert.

Here are the individual 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 do not respond to SQL injection - my sanitation is much more thorough than these fragments allow!

, , . , . , , useressid product_id, , .

+1
1

(usersessid, product_id). , id, , (usersessid, product_id). UPDATE INSERT:

INSERT INTO sessionBasket (userid, usersessid, date_added, product_id, qty, notes)
VALUES (?, ?, now(), ?, ?, ?)
ON DUPLICATE KEY UPDATE qty = qty + ?

, , :

CREATE UNIQUE INDEX sessionBasket_uniq ON sessionBasket (usersessid, product_id);

:

ALTER TABLE sessionBasket ADD CONSTRAINT sessionBasket_pkey PRIMARY KEY (usersessid, product_id);
+4

More articles: