Array classes as mysql column names to insert?

Question

I am using an API that returns search results as json. Then I need to write this to the MYSQL table. I did this successfully before, but this time the situation is different, and I think that it is due to the structure of the resulting array: the key names are dynamic in this case, if there is no data for a specific key, the key is not listed in the array. The following is an example of a vardump array:

array 0 => array 'title' => string 'Funny but not funny' (length=19) 'body' => string 'by Daniel Doi-yesterday while eating at Curry House... 'url' => string 'http://danieldoi.com/2012/11/20/funny-but-not-funny/' 'source_site_name' => string 'WordPress.com' (length=13) 'source_site_url' => string 'http://www.wordpress.com' (length=24) 'query_topic' => string 'thanksgiving' (length=12) 'query_string' => string 'blogs=on&topic=thanksgiving&output=json' (length=39) 1 => array 'title' => string 'Travel Easy this Holiday Season...' (length=34) 'body' => string 'Give yourself a few gifts and get this holiday season off... 'url' => string 'http://facadebeauty.wordpress.com/2012/11/20 'date_published' => string 'Tue, 20 Nov 2012 18:22:35 +0000' (length=31) 'date_published_stamp' => string '1353435755' (length=10) 

Please note that the order / inclusion of keys is subject to change.

My suggested solution was to use array keys as column names, turning them into the variable used in the query expression, but this does not work for me. Here is my attempt:

 $jsonString = file_get_contents("http://search-query-URL&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); } } 

Any suggestions on where to look next? Thanks!

UPDATE 11/27:

Here I am trying to adapt David's suggestion. I get the following error: "Error connecting to database (1110)" Header name "specified twice in the request.

Here is my code in its current form:

 $mysqli = mysqli_connect("localhost"); mysqli_select_db($mysqli, "mydatabase"); foreach ($array as $column) { foreach ($column as $key => $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; 

Final Upate - WORK!

He works. Here is what we have:

 $mysqli = mysqli_connect("localhost"); mysqli_select_db($mysqli, "mydatabase"); foreach ($array as $column) { foreach ($column as $key => $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; 

Answer 1

I actually only have this feature sitting around because I use it all the time. What this means: combine all the associative pairs in your array to insert into the table and paste them as a single insert. To be clear: this is not what you are doing above, since it looks like you are trying to insert each value into your own insert, which is likely to give you more rows than you want.

 function mysqli_insert($table, $assoc) { $mysqli = mysqli_connect(PUT YOUR DB CREDENTIALS HERE); mysqli_select_db($mysqli, DATABASE NAME HERE); foreach ($assoc as $column => $value) { $cols[] = $column; $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; } 

As MarcB notes above, there is a risk that your target table will not have a column that shows your result. But we will sanitize the insert (with mysqli_real_escape_string ), so we should not have problems with injection vulnerabilities.

Answer 2

Here is a single line:

 $query = 'INSERT INTO '.$table.'(`'.implode('`, `', array_keys($array)).'`) VALUES("'.implode('", "', $array).'")'; 

Answer 3

I noticed the answer that was above, but since I already wrote this, I thought that I would publish it. Note. I hope you trust where you get the data from, otherwise it will cause all kinds of problems. I also added comments throughout the code, I hope it helps to understand how this is done.

 $jsonString = file_get_contents("http://search-query-URL&output=json"); $array = json_decode($jsonString, true); // database connection code snipped out here $table = "results"; foreach($array as $sub_array) { // First, grab all keys and values in separate arrays $array_keys = array_keys($sub_array); $array_values = array_values($sub_array); // Build a list of keys which we'll insert as string in the sql query $sql_key_list = array(); foreach ($array_keys as $key){ $sql_key_list[] = "`$key`"; } $sql_key_list = implode(',', $sql_key_list); // Build a list of values which we'll insert as string in the sql query $sql_value_list = array(); foreach ($array_values as $value){ $value = mysql_real_escape_string($value); $sql_value_list[] = "'$value'"; } $sql_value_list = implode(',', $sql_value_list); // Build the query $insert = "INSERT INTO $table ($sql_key_list) VALUES ($sql_value_list)"; mysql_query($insert) or die(mysql_error()); }