Insert JSON data in MySQL database with PHP

Question

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

<?php

    //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?

Answer 1

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.