title: Prepared Statement with ON DUPLICATE KEY
body: <p>I think this one should be pretty easy but I am having trouble getting it right.  I have searched a bit but being new to prepared statements I can't quite figure out the syntax from looking at other examples I have found here and elsewhere.  Anyhow here is my code.</p>
  
  <pre><code>if($stmt = $mysqli -&gt; prepare("INSERT INTO user_info (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, ?,?) 
      ON DUPLICATE KEY UPDATE (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, ?,?)")) {
      $stmt -&gt; bind_param("sssssssi",$city, $state, $website, $public_contact, $user, $zipcode, $pic, $emailme);
      $stmt -&gt; execute();
      $stmt -&gt; bind_result($result);
      $stmt -&gt; close();
  }
  </code></pre>
  
  <p>user is the unique.  This IMO is just a syntax problem so could somebody help me out with the correct syntax?  Much appreciated.    </p>
  
  <p>ETA: just to help trouble shooting this does work as intended when I remove the ON DUPLICATE KEY UPDATE  part but obviously, it only allows the one record per user and will not update</p>
  
  <p>UPDATE:  was never able to find a working syntax to use the ON DUPLICATE KEY UPDATE so what I did instead (admittedly probably not the most efficient way) was check the table before hand for the user.  If the user exist I run and UPDATE, if not I run a INSERT.  Below is my working code.  Hope this helps somebody who gets stuck in my situation.  </p>
  
  <pre><code> $sql = "SELECT * FROM user_info WHERE user='$user'";
  
   if ($result=mysqli_query($mysqli,$sql))
   {
   /* Return the number of rows in result set */
   $rows=mysqli_num_rows($result);
   /* Free result set */
   mysqli_free_result($result);
   } 
  
   if($rows == 0) { 
  if($stmt = $mysqli -&gt; prepare("INSERT INTO user_info (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, ?,?) ")) {
  
  $stmt -&gt; bind_param("sssssssi",$city, $state, $website, $public_contact, $user, $zipcode, $pic, $emailme);
  $stmt -&gt; execute();
  $stmt -&gt; bind_result($result);
  $stmt -&gt; close();
  }
  
  } else {
  
  if($stmt = $mysqli -&gt; prepare("UPDATE user_info SET city=?, state=?, website=?, public_contact=?, zipcode=?, pic=?, emailme=? WHERE user='$user'")) {
  
  $stmt -&gt; bind_param("ssssssi",$city, $state, $website, $public_contact, $zipcode, $pic, $emailme);
  $stmt -&gt; execute();
  $stmt -&gt; bind_result($result);
  $stmt -&gt; close();
  }
      }
  </code></pre>