Imploding in PHP an array from Json

Question

I have a JSON that looks like this:

{
"num_found": 407343,
"results": [
    {
        "speaker_state": "LA",
        "speaking": [
            "ABC",
            "DEF",
            "GHI"
        ],
        "speaker_party": "D",
    },

And so on. I am trying to parse this data into a MySQL database. I adapted the following php code:

<?php

$hostname_ndb = "localhost";
$database_ndb = "senate";
$username_ndb = "root";
$password_ndb = "root";
$ndb = mysql_pconnect($hostname_ndb, $username_ndb, $password_ndb) or trigger_error(mysql_error(),E_USER_ERROR); 
mysql_select_db($database_ndb);

$url = "http://xxx.yyy/text.json";
$json = file_get_contents($url);

$out = json_decode($json, true);

foreach($out["results"] as $results) { 
$speaker_state = $results['speaker_state']; 
$speaking2 = $results['speaking'];
$speaking = implode('', $speaking2);
$date = $results['date'];

mysql_query("INSERT INTO speeches (speaker_state, speaking, date ) VALUES('$speaker_state', '$speaking','$date')") or die (mysql_error()); 

}

?> 

There is an array within an array and it seems to be the problem. The script does not work, data is not stored in the database. The script returns the error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'speaking, date, ' at line 1.

The PHP log shows a notice:"PHP Notice: Array to string conversion in /Applications/MAMP/htdocs/json.php on line 46" in the php log.

var_dump($speaking2) returns:

array(3) { [0]=> string(713) "XXX" [1]=> string(891) "ZZZ" [2]=> string(1189)

and so on. How would you advise me to adapt this script to make it work?

Answer 1

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'speaking, date, ' at line 1.

The reason for this error is that you are using date as a field name.

date is a reserved word in SQL, and will thus cause an error if you use it as a field name or table name.

Ideally you should not use SQL reserved words in your DB table or field names, but if you do, you need to wrap the names in back-ticks so that mySQL will accept them. (in fact, it's generally recommended good practice to do this to all field names and table names; not compulsory, but good practice)

So your query should look like this:

INSERT INTO `speeches` (`speaker_state`, `speaking`, `date`) VALUES ('$speaker_state', '$speaking','$date')

I would also note that you don't seem to be escaping your input data. You should use the mysql_real_escape_string() to escape any variables that you put into your query (ie $speaker_state, $speaking and $date). Failure to do this can result in errors and in your site being vulnerable to being hacked.

(note also that the mysql_xxx() functions are deprecated; ideally you should be using a different set of database functions, in which case there would be a different method for escaping the variables, but as things stand you need to use mysql_real_escape_string())

PHP Notice: Array to string conversion in /Applications/MAMP/htdocs/json.php on line 46

This is entirely un-related to the mySQL error. It may still be a problem that may still need to be looked into, but it isn't the cause of the SQL error that you're asking about.

I can't be certain without knowing which line is line 46, but what is probably happening here is that the incoming JSON string produces an array for one of the three variables that you are putting into your SQL string. You're treating them as strings, but one of them isn't a string. This will probably cause data corruption, so you should look into it. I can't tell anything more than that without seeing the actual JSON string, but you should use print_r() or var_dump() to examine the contents of the variables.

Hope that helps.