4

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:

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)

Notice how the order/inclusion of the keys is subject to change.

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:

$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? Thank you!

UPDATE 11/27:

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."

Here's my code as it stands now:

$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 - WORKING!

It's working. Here's what we've got:

$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;
Improve this question
2
  • 2
    nothing wrong with the approach, but you're taking arbitary text from some joe-random site and throwing it at your database, hoping something will stick. what if some of those fields don't exist in $table? What if there's an sql injectionproblem? if you're at all worried about doing this properly, you'll go with a different more secure approach. Commented Nov 20, 2012 at 18:40
  • hi Marc. all possible fields exist in the table, since it's our API and I know what they could all be. it's just that they're not always returned from a query. when I tried this approach, it did not record all the info on the same row before advancing to the next place in the array. Commented Nov 20, 2012 at 18:53

3 Answers 3

Reset to default
5

I actually have just this function sitting around because I use it all the time. What this does: pool all the associative pairs in your array for insertion into the table, and puts them in as a single insert. To be clear: this isn't what you're doing above, as it looks like you're trying to insert each value in its own insert, which would probably give you way 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 says above, there is a risk that your target table won't have a column that your results show. But we are sanitizing the insertion (with mysqli_real_escape_string) so we shouldn't have issues with injections vulnerabilities.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

This looks very cool, David. I will give it a try and post back my results! Thank you!
Hi David, please see my edits. I'm trying t work it out using your function, but running into a strange error. Thanks!
Sorry for the delay: you can pull both the key and value in a foreach command, which is what I'm doing above. (The => delineates that relationship as KEY => VALUE.) The part of your solution that's confusing me is $colName = key($arr_value); which I doubt is moving the pointer through the array (you'd need some next()s for that) and so you're just getting the first key many times. But rather than deal with that needless complexity it's better to get the keys natively with foreach, as my solution is.
David, thank you for the follow up. I've almost got this figured out! It was tricky because I needed to go one level deeper into the array than your example (I'm new and slow, heh). At this point, I'm stuck on the following error: "Database Connection Error (1110) Column 'title' specified twice on query." When I dump the data, everything looks good, but it's not allowing me to write it to the db. I will update my update with my current code. Thank you!
Sorry. I didn't look closely enough at your data structure, my code was built on handing a single associative array, but yours is an array of associative arrays. That also explains how you're getting the duplicated fields: you need to end the outer foreach right after the query. As it is you're encoding all your associative arrays together into one monster insane query and then trying to write it to the database, which as you say isn't working. I'd also add, right before you terminate the outer foreach an unset($cols, $vals), otherwise you'll have the problem again after the first query.
|
1

Here's one-liner:

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

1 Comment

Of course you would need to prepare the array first and remember to clean both - keys and values of the array before saving the data into the database.
0

I noticed an answer being posted above, but since I already wrote this, thought I'd post it. Note: I hope you trust where you get your data from, otherwise this opens up all sorts of problems. I've also added comments throughout the code, hopefully helps to understand how it's being 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());
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.