php json mysql数据库中,使用PHP在MySQL数据库中插入JSON数据

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.