title: ON DUPLICATE KEY UPDATE implementation/design
body: <p>I have a table, sessionBasket, that holds a list of the items in the shopping baskets of visitors to my site. It looks like:</p>
  
  <pre><code>id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  usersessid VARCHAR
  date_added DATETIME
  product_id INT
  qty INT
  </code></pre>
  
  <p>My 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. </p>
  
  <p>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.</p>
  
  <p>Here are the separate queries:</p>
  
  <pre><code>//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");
  </code></pre>
  
  <p>Please no replies about SQL injection - my sanitizing is much more thorough than these snippets let on!</p>
  
  <p>Any help shrinking these down would be fantastic.  It's possible my tables aren't normalized enough.  I was thinking it would be possible to create a new unique field which contains the usersessid and product_id concatenated which would collapse the unique indices into one instead of two fields, but it's not ideal.</p>