Json data getting read but not inserting into mysql using php

Question

Im trying to insert json data using php into mysql,

I get success msg, but no records are inserted.

My json data is :

jsondata.json:

{"users": { "bert":6.44, "earnie":0.25, "bigbird":34.45 }}

My php code:

<?php
//First: read data
$fo=fopen("data.json","r");
$fr=fread($fo,filesize("data.json"));
$array=json_decode($fr,true);
//Second: create $values
$rows = array();
foreach ($array['users'] as $key => $value)
$rows[] = "('" . $key . "', '" . $value . "')";
$values = implode(",", $rows);


 //To display all values from JSON file
 echo '<pre>';print_r($array);
//Save to DB
$hostname = 'localhost';                 
$username = 'root';
$password = '';

try 
{
$dbh = new PDO("mysql:host=$hostname;dbname=nodejs", $username, $password);
echo 'Connected to database<br />';
//$count = $dbh->exec("INSERT INTO USERSAMOUNTS(USERNAME, AMOUNT) VALUES " . $values) or die(print_r($dbh->errorInfo(), true)); 
$count = $dbh->exec("INSERT INTO json(firstName) VALUES " . $values) or die(print_r($dbh->errorInfo(), true)); 
echo $count;// echo the number of affected rows
$dbh = null;// close the database connection
echo 'Success<br />';
}
catch(PDOException $e)
{
  echo $e->getMessage();
}
?>

echo "INSERT INTO USERAMOUNTS(USERNAME, AMOUNT) VALUES " . $values; and try to run it in mysql directly. — Bhaskar Jain, Mar 15 '17 at 13:22
INSERT INTO `USERSAMOUNTS` (`USERNAME`, `AMOUNT`) VALUES ('Nadya Eka','20.0'); this works and is inserting and also seems the count value is 1 echo $count; — Sushivam, Mar 15 '17 at 13:26
and try to debug the errors using `$dbh->exec("INSERT INTO USERAMOUNTS(USERNAME, AMOUNT) VALUES " . $values) or die(print_r($dbh->errorInfo(), true));` — hassan, Mar 15 '17 at 13:33
USERNAME is varchar, AMOUNT is int and for debug i get Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Array' at line 1 ) — Sushivam, Mar 15 '17 at 13:40

score 0 · Accepted Answer · answered Mar 15 '17 at 13:32

I believe the problem could be the order in which the actions are performed.

<?php

//First: read data
$fo = fopen("jsondata.json", "r");
$fr = fread($fo, filesize("jsondata.json"));
$array = json_decode($fr, true);

//Second: create $values
$rows = array();
foreach ($array['users'] as $key => $value)
    $rows[] = "('" . $key . "', '" . $value . "')";
$values = implode(",", $rows);

//Third: display
echo '<pre>';
print_r($array);

//Fourth: save to db
$hostname = 'localhost';
$username = 'root';
$password = '';

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=nodejs", $username, $password);
    echo 'Connected to database<br />'; // echo a message saying we have connected 
    $count = $dbh->exec("INSERT INTO USERAMOUNTS(USERNAME, AMOUNT) VALUES " . $values);
    echo $count; // echo the number of affected rows
    $dbh = null; // close the database connection
    echo 'Success<br />';
} catch (PDOException $e) {
    echo $e->getMessage();
}
?>

Have updated the answer , its inserting now and also how to change the column values accordingly, Column count doesn't match value count at row 1 — Sushivam, Mar 15 '17 at 14:34

score 0 · Answer 2 · answered Mar 15 '17 at 13:39

Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them for more info please check - http://php.net/manual/en/pdo.setattribute.php

$dbh = new PDO("mysql:host=$hostname;dbname=nodejs", $username, $password);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$count = $dbh->exec("INSERT INTO USERAMOUNTS(USERNAME, AMOUNT) VALUES " . $values);

Hope this help.

score 0 · Answer 3 · edited May 23 '17 at 12:09

0

The issue is with how your are trying to insert data. I'm surprised you're not getting an error.

You should use a prepared statement. See the following... https://stackoverflow.com/a/4629088/2033178

edited May 23 '17 at 12:09

Community

1
1

answered Mar 15 '17 at 13:39

Darkman

223
1
3
14

score 0 · Answer 4 · answered Mar 15 '17 at 13:45

Some things are funky here.

At first it looks like you're expecting the data to come magically from $data (unless that is passed somewhere?)

$array = json_decode($data, true);
$rows = array();                                   
foreach($array['users'] as $key => $value)                         
$rows[] = "('" . $key . "', '" . $value . "')"; 
$values = implode(",", $rows);

And then it looks like you're opening a file and parsing the JSON (but not doing the above magic with $rows[])

$fo=fopen("jsondata.json","r");
$fr=fread($fo,filesize("jsondata.json"));
$array=json_decode($fr,true);

Why not insert on the for each loop?

$fo=fopen("jsondata.json","r");
$fr=fread($fo,filesize("jsondata.json"));
$array=json_decode($fr,true);
$count = 0;
$dbh = new PDO("mysql:host=$hostname;dbname=nodejs", $username, $password);

try {
    foreach($array['users'] as $key => $value) 
    $count = $count + $dbh->exec("INSERT INTO USERAMOUNTS(USERNAME, AMOUNT) VALUES " . $key . " " . $value . ")";

 } catch ...

Json data getting read but not inserting into mysql using php

4 Answers4