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?

where did you run this query?? I mean the command which execute query e.g mysqli_query($sqlt,$connection) — habib ul haq
– habib ul haq, Commented Apr 29, 2015 at 7:34
I removed them only to enter this question, but I put them in the real code — K0pp0
– K0pp0, Commented Apr 29, 2015 at 8:30

mins · Accepted Answer · 2015-04-29 08:04:51Z

5

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.

edited Apr 29, 2015 at 8:04

mins

7,76613 gold badges71 silver badges87 bronze badges

answered Apr 29, 2015 at 7:38

Pyton

1,3199 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shadab Over a year ago

I think we can insert as much as we want. w3schools.com/php/php_mysql_insert_multiple.asp

Pyton Over a year ago

Yes, but it depends on insert method. PDO doesn't support multi query statements.

shadab Over a year ago

stackoverflow.com/questions/6346674/…

Collectives™ on Stack Overflow

Insert JSON data in MySQL database with PHP

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related