I tried to parse a JSON file using PHP.
Then I would put the extracted data in a MySQL database.
This is my JSON file
{"u":[
{"text":"Chef salad is calling my name, I\u0027m so hungry!",
"id_str":"28965131362770944",
"created_at":"dom gen 23 24:00:00 +0000 2011",
"user":
{"id_str":"27144739",
"screen_name":"LovelyThang80",
"name":"One of A Kind"}},
{....} //Other same code
}
And this is my PHP code
//connect to mysql db
//read the json file contents
//convert json object to php associative array
$data = json_decode($jsondata, true);
//prepare variables for insert
$sqlu = "INSERT INTO user(id_user, screen_name, name)
VALUES ";
$sqlt = "INSERT INTO tweet(id_user, date, id_tweet, text)
VALUES ";
//I analyze the whole array and assign the values to variables
foreach ($data as $u => $z){
foreach ($z as $n => $line){
foreach ($line as $key => $value) {
switch ($key){
case 'text':
$text = $value;
break;
case 'id_str':
$id_tweet = $value;
break;
case 'created_at':
$date = $value;
break;
case 'user':
foreach ($value as $k => $v) {
switch($k){
case 'id_str':
$id_user = $v;
break;
case 'screen_name':
$screen_name = $v;
break;
case 'name':
$name = $v;
break;
}
}
//insert value into table
$sqlu .= "('".$id_user."', '".$screen_name."', '".$name."')";
break;
}
}
//insert value into table
$sqlt .= "('".$id_user."', '".$date."', '".$id_tweet."', '".$text."')";
}
}
?>
But in the table doesn't enter anything!
And if I try:
echo $sqlu;
Output:
INSERT INTO user(id_user, screen_name, name)
VALUES ('27144739', 'LovelyThang80', 'One of A Kind')
INSERT INTO user(id_user, screen_name, name)
VALUES ('27144739', 'LovelyThang80', 'One of A Kind')('21533938', 'ShereKhan77', 'Loki Camina Cielos ')
INSERT INTO user(id_user, screen_name, name)
VALUES ('27144739', 'LovelyThang80', 'One of A Kind')('21533938', 'ShereKhan77', 'Loki Camina Cielos ')('45378162', 'Cosmic_dog', 'Pablo')
And the same for
echo $sqlt;
Why?
解决方案
You forgot to add , between values:
INSERT INTO user(id_user, screen_name, name)
VALUES ('27144739', 'LovelyThang80', 'One of A Kind'), ('21533938', 'ShereKhan77', 'Loki Camina Cielos ')
And you can insert only one INSERT statement at once.