CODEWITHSUNDEEP
phpmysqljson
K0pp0
K0pp0

Reputation: 295

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

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?

Upvotes: 2

Views: 5385

Answers (2)

bloodyKnuckles
bloodyKnuckles

Reputation: 12079

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.

Upvotes: 0

Kailash Yadav
Kailash Yadav

Reputation: 1930

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());
        }
    }
}
?>

Upvotes: 1

Related Questions