Insert multiple rows and documents from JSON files to MySQL database with PHP

Question

I wrote this PHP code to extract values from a JSON file and insert them into a MySQL database.

<?php
//connect to mysql db
$con = mysqli_connect("localhost","root","","db_tweets") or die('Could not connect: ' . mysql_error());

//read the json file contents
$jsondata = file_get_contents('prova.json');

//convert json object to php associative array
$data = json_decode($jsondata, true);

foreach ($data as $u => $z){
    foreach ($z as $n => $line){
        //get the tweet details
        $text = $line['text'];
        $id_tweet = $line['id_str'];
        $date = $line['created_at'];
        $id_user = $line['user']['id_str'];
        $screen_name = $line['user']['screen_name'];
        $name = $line['user']['name'];

        $sqlu = "INSERT INTO user(id_user, screen_name, name)
            VALUES ('".$id_user."', '".$screen_name."', '".$name."')";
    }
}
if(!mysqli_query($con, $sqlu))
{
    die('Error : ' . mysql_error());
}
?>

In so doing it insert the values always in the first line of my table, overwriting the previous value. So it remains only the last.

How can I:

1) insert all values in multiple lines?

2) to parse multiple JSON files?

you can take in multiple files from javascript, and using AJAX send them to a backend php process to parse them and add them to the database. http://php.net/manual/en/function.json-decode.php On the client end you should be controlling which file is being sent, and confirming it has been received / processed using AJAX. — Beau Bouchard, Apr 29 '15 at 15:48
Those 2 loops are you enemies, since you are evaluating only the last assignment of $sqlu — Masiorama, Apr 29 '15 at 15:50

Kailash Yadav · Answer 1 · 2015-04-29T15:58:09.027

Try this. You are just executing the last query cause you mysqli_query() is outside loop.

Method 1:

<?php
//connect to mysql db
$con = mysqli_connect("localhost","root","","db_tweets") or die('Could not connect: ' . mysql_error());

//read the json file contents
$jsondata = file_get_contents('prova.json');

//convert json object to php associative array
$data = json_decode($jsondata, true);

foreach ($data as $u => $z){
    foreach ($z as $n => $line){
        //get the tweet details
        $text = $line['text'];
        $id_tweet = $line['id_str'];
        $date = $line['created_at'];
        $id_user = $line['user']['id_str'];
        $screen_name = $line['user']['screen_name'];
        $name = $line['user']['name'];

        $sqlu = "INSERT INTO user(id_user, screen_name, name)
            VALUES ('".$id_user."', '".$screen_name."', '".$name."')";
       if(!mysqli_query($con, $sqlu))
       {
           die('Error : ' . mysql_error());
       }
    }
}
?>

Method 2:

<?php

//connect to mysql db
$con = mysqli_connect("localhost","root","","db_tweets") or die('Could not connect: ' . mysql_error());

//read the json file contents
$jsondata = file_get_contents('prova.json');

//convert json object to php associative array
$data = json_decode($jsondata, true);

$values = "";
foreach ($data as $u => $z){
    foreach ($z as $n => $line){
        //get the tweet details
        $text = $line['text'];
        $id_tweet = $line['id_str'];
        $date = $line['created_at'];
        $id_user = $line['user']['id_str'];
        $screen_name = $line['user']['screen_name'];
        $name = $line['user']['name'];

        $values .= "('".$id_user."', '".$screen_name."', '".$name."'),";
    }
}

if(!empty($values)) {
    $values = substr($values, 0, -1);
    $sqlu = "INSERT INTO user(id_user, screen_name, name) VALUES {$values}";
    if(!mysqli_query($con, $sqlu))
    {
        die('Error : ' . mysql_error());
    }
}
?>

Answer for multiple files:

<?php

//connect to mysql db
$con = mysqli_connect("localhost","root","","db_tweets") or die('Could not connect: ' . mysql_error());

$files = array("prova.json", "file2.json");

foreach ($files as $file) {
//read the json file contents
    $jsondata = file_get_contents($file);

//convert json object to php associative array
    $data = json_decode($jsondata, true);

    $values = "";
    foreach ($data as $u => $z) {
        foreach ($z as $n => $line) {
            //get the tweet details
            $text = $line['text'];
            $id_tweet = $line['id_str'];
            $date = $line['created_at'];
            $id_user = $line['user']['id_str'];
            $screen_name = $line['user']['screen_name'];
            $name = $line['user']['name'];

            $values .= "('" . $id_user . "', '" . $screen_name . "', '" . $name . "'),";
        }
    }

    if (!empty($values)) {
        $values = substr($values, 0, -1);
        $sqlu = "INSERT INTO user(id_user, screen_name, name) VALUES {$values}";
        if (!mysqli_query($con, $sqlu)) {
            die('Error : ' . mysql_error());
        }
    }
}
?>

Thanks for your answer. With "Method 1" works. But for multiple files, gives me error, doesn't read the second file. — K0pp0, Apr 29 '15 at 16:49
does your file exists in the same path and has same data structure ? It would be great if you can paste some content of files and file names — Kailash Yadav, Apr 29 '15 at 16:51

bloodyKnuckles · Accepted Answer · 2015-04-29T17:54:15.723

With every loop you're overwriting the last $sqlu value before ever passing that variable to the mysqli_query function after the loops. So once the loops are completed you're left with the last assigned value to $sqlu, and that's the only one that gets executed.

Instead, execute your query inside the loop and...

Use PHP mysqli_ functions mysqli_prepare, mysqli_stmt_bind_param, and mysqli_stmt_execute to simplify and secure your query:

//connect to mysql db
$con = mysqli_connect("localhost","root","","db_tweets") or die('Could not connect: ' . mysql_error());

// prepare your insert query
$stmt = mysqli_prepare($con, 'INSERT INTO user(id_user, screen_name, name) VALUES (?, ?, ?)');

// bind the upcoming variable names to the query statement
mysqli_stmt_bind_param($stmt, 'iss', $id_user, $screen_name, $name);

// loop over JSON files
$jsonfiles = array('prova.json', 'provb.json', 'provc.json');
foreach ( $jsonfiles as $jsonfile ) {

    //read the json file contents
    $jsondata = file_get_contents($jsonfile);   

    //convert json object to php associative array
    $data = json_decode($jsondata, true);

    foreach ($data as $u => $z){
        foreach ($z as $n => $line){
            //get the tweet details
            $id_user = $line['user']['id_str'];
            $screen_name = $line['user']['screen_name'];
            $name = $line['user']['name'];

            // execute this insertion
            mysqli_stmt_execute($stmt);
        }
    }
}

So, this not only uses fewer database resources by preparing your query once, and has cleaner code, but also properly escapes your insertion values to protect against sql injection.

Added an array $jsonfiles containing any number of JSON filenames, and used a foreach construct to loop over the JSON files.

Thanks! It works! But, for multiple files? Can you help me for this? — K0pp0, Apr 29 '15 at 17:23
How to set this dynamic : $jsonfiles = array('prova.json', 'provb.json', 'provc.json'); ie) the script needs to pick all the json files coming to a folder location every few seconds and save to DB — Sushivam, Mar 16 '17 at 09:57
@SachinS Try this: http://stackoverflow.com/a/12664572/2743458 In this case: `$jsonfiles = glob('/path/to/dir/*.json');` — bloodyKnuckles, Mar 16 '17 at 16:02

Insert multiple rows and documents from JSON files to MySQL database with PHP

2 Answers2