title: insert on duplicate key update - in mysqli loop
body: <p>I have a database with a primary key 'id' and a Unique constraint made up of price_date , fund_id , currency_id, and class_id</p>
  
  <pre><code>$query = "INSERT INTO `price_data` (`price_date`, `fund_id`, `currency_id`, `class_id`, `nav`, `nav_change`) VALUES";
  
  
  $format = " ('%s', '%s', '%s', '%s', %f, %f),";
  
  // Go over each array item and append it to the SQL query
  foreach($prices as $price) {
      $query .= sprintf(
          $format,
          $mysqli-&gt;escape_string($price['PriceDate']),
          $mysqli-&gt;escape_string($price['Fund']),
          $mysqli-&gt;escape_string($price['Currency']),
          $mysqli-&gt;escape_string($price['Class']),
          $mysqli-&gt;escape_string($price['NAV']),
          $mysqli-&gt;escape_string($price['NavChange'])
      );
  }
  // The last VALUES tuple has a trailing comma which will cause
  // problems, so let us remove it
  $query = rtrim($query, ',');
  
  // MySQLi::query returns boolean for INSERT
  $result = $mysqli-&gt;query($query);
  </code></pre>
  
  <p>I want to change the query to something like </p>
  
  <pre><code>INSERT INTO `price_data`(price_date , fund_id , currency_id , nav , nav_change) VALUES () ON DUPLICATE KEY UPDATE nav='',nav_change='';
  </code></pre>
  
  <p>My attempt to append this to the original query isn't getting the values for nav and nav_chnge to the query.  </p>
  
  <pre><code>$query .= ' ON DUPLICATE KEY UPDATE nav=$format$mysqli-&gt;real_escape_string($data[\'NAV\']),nav_change=$format,$mysqli-&gt;real_escape_string($data[\'NavChange\'])';
  </code></pre>
  
  <p>If I echo the resulting query it looks like the following</p>
  
  <pre><code>INSERT INTO `price_data` (`price_date`, `fund_id` , `currency_id`, `class_id`, `nav`, `nav_change`) VALUES ('2013-04-29', 'AAAA', '0', 'A', 11.793300, 0.054000), ON DUPLICATE KEY UPDATE nav=$format$mysqli-&gt;real_escape_string($data["NAV"]),nav_change=$format,$mysqli-&gt;real_escape_string($data["NavChange"])
  </code></pre>
  
  <p>Thanks for any help.</p>