current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
 
1. It's a notice, not an error 2. What shows var_dump($speaking2) –  u_mulder Aug 31 '13 at 20:40
 
I updated my question with more information. –  user1029296 Aug 31 '13 at 20:44
 
off topic, but please note that the mysql_xx() functions are deprecated and their use is discouraged. You should consider switching to a better supported database API such as the PDO library. –  Spudley Aug 31 '13 at 20:45
 
echo your query's text and see what's wrong in it. Try to perform this query in phpmyadmin, for example. –  u_mulder Aug 31 '13 at 20:47
 
I am not sure how to echo a query. Newbie here. –  user1029296 Aug 31 '13 at 20:49
show 1 more comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
 
Thanks a lot for your answer. I added the back brackets. Now the error is: "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 's health, extended access to higher education, the concerns of our Nation's vete' at line 1" which is a piece of the text that is part of the variable "speaking." Are you sure that the fact that "speaking" is an array is not a problem in the MySQL query? –  user1029296 Aug 31 '13 at 21:00
 
@user1029296. If you're getting a MySQL error within what should be content, that's further evidence that you're not escaping stuff correctly. You should be doing that. (mysql_real_escape_string or (better) mysqli_real_escape_string is your friend here.) –  TRiG Aug 31 '13 at 21:02
1  
The reason for this next error is because you haven't followed my advice about escaping the input variables. The text contains a quote character, and must be escaped. –  Spudley Aug 31 '13 at 21:03
 
@TRiG - the choice of escape function is dictated by the choice of DB function he's using for the query, etc. You can't mix and match. (well, I guess you can, but it's not a good idea). Ideally he should switch to using a better database API, but I get the feeling that it may be a struggle to get him to switch. –  Spudley Aug 31 '13 at 21:05
 
Yes. Agreed. Sorry, I wasn't clear about that. I was just trying to reiterate your advice to move away from the mysql_* functions to the mysqli_* functions, but my writing wasn't as clear as it should have been. @user1029296. mysqli_* is, generally speaking, better, and the mysql_* functions are depricated and will soon be removed. –  TRiG Aug 31 '13 at 21:08
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.