title: Array keys as mysql column names for insert?
body: <p>I am using an API that returns search results as json.  Then, I need to write this to a MYSQL table.  I've done this successfully before, but this time the situation is different, and I think that's because of the structure of the resulting array:  the key names are dynamic in that, if no data exists for a particular key, the key is not listed in the array.  Here is an example vardump of the array:</p>
  
  <pre><code>array
    0 =&gt; 
      array
        'title' =&gt; string 'Funny but not funny' (length=19)
        'body' =&gt; string 'by Daniel Doi-yesterday while eating at Curry House...
        'url' =&gt; string 'http://danieldoi.com/2012/11/20/funny-but-not-funny/' 
        'source_site_name' =&gt; string 'WordPress.com' (length=13)
        'source_site_url' =&gt; string 'http://www.wordpress.com' (length=24)
        'query_topic' =&gt; string 'thanksgiving' (length=12)
        'query_string' =&gt; string 'blogs=on&amp;topic=thanksgiving&amp;output=json' (length=39)
    1 =&gt; 
      array
        'title' =&gt; string 'Travel Easy this Holiday Season...' (length=34)
        'body' =&gt; string 'Give yourself a few gifts and get this holiday season off...
        'url' =&gt; string 'http://facadebeauty.wordpress.com/2012/11/20
        'date_published' =&gt; string 'Tue, 20 Nov 2012 18:22:35 +0000' (length=31)
        'date_published_stamp' =&gt; string '1353435755' (length=10)
  </code></pre>
  
  <p>Notice how the order/inclusion of the keys is subject to change.  </p>
  
  <p>My proposed solution was to use the array keys as column names, turning them into a variable to use in a query statement, but this isn't working for me.  Here's my attempt:</p>
  
  <pre><code>$jsonString = file_get_contents("http://search-query-URL&amp;output=json");
  $array = json_decode($jsonString, true);
  
  // database connection code snipped out here
  
  $table = "results";
  
  foreach($array as $arr_value) {
          foreach ($arr_value as $value) {
            $colName = key($arr_value);
            $colValue = ($value);
            $insert="INSERT INTO $table ($colName) VALUES ('$colValue')";
  
            mysql_query($insert) OR die(mysql_error());
            next($arr_value);
                }
           }
  </code></pre>
  
  <p>Any suggestions on where to look next?  Thank you!</p>
  
  <p><strong>UPDATE 11/27:</strong></p>
  
  <p>Here I am trying to adapt David's suggestion.  I'm receiving the following error: "Database Connection Error (1110) Column 'title' specified twice on query."</p>
  
  <p>Here's my code as it stands now:</p>
  
  <pre><code>$mysqli = mysqli_connect("localhost");
  mysqli_select_db($mysqli, "mydatabase");
  
  foreach ($array as $column) {
      foreach ($column as $key =&gt; $value) {
      $cols[] = $key;
      $vals[] = mysqli_real_escape_string($mysqli, $value);
      }
  }
  
  $colnames = "`".implode("`, `", $cols)."`";
  $colvals = "'".implode("', '", $vals)."'";
  $mysql = mysqli_query($mysqli, "INSERT INTO $table ($colnames) VALUES ($colvals)") or die('Database Connection Error ('.mysqli_errno($mysqli).') '.mysqli_error($mysqli). " on query: INSERT INTO $table ($colnames) VALUES ($colvals)");
  mysqli_close($mysqli);
  if ($mysql)
  return TRUE;
  else return FALSE;
  </code></pre>
  
  <p><strong>Final Upate - WORKING!</strong></p>
  
  <p>It's working.  Here's what we've got:</p>
  
  <pre><code>$mysqli = mysqli_connect("localhost");
  mysqli_select_db($mysqli, "mydatabase");
    foreach ($array as $column) {
  foreach ($column as $key =&gt; $value) {
      $cols[] = $key;
      $vals[] = mysqli_real_escape_string($mysqli, $value);
      }
    $colnames = "`".implode("`, `", $cols)."`";
    $colvals = "'".implode("', '", $vals)."'";
    $mysql = mysqli_query($mysqli, "INSERT INTO $table ($colnames) VALUES ($colvals)") or die('Database Connection Error ('.mysqli_errno($mysqli).') '.mysqli_error($mysqli). " on query: INSERT INTO $table ($colnames) VALUES ($colvals)");
  
    unset($cols, $vals);
  }
    mysqli_close($mysqli);
  if ($mysql)
  return TRUE;
  else return FALSE;  
  </code></pre>