JSON to MySQL Table

Question

I have a JSON file with data output from another program and i am trying to import this data into a MySQL table with the following two issues.

1. Data never gets put in the table and no errors returned, when i echo out the output and enter it manually into the database it works fine.

2. The data changes from the JSON values when output into the array needed for the sql import string (Values).

JSON:

{
"version" : 90200,
"protocolversion" : 70002,
"blocks" : 322877,
"timeoffset" : -1,
"connections" : 49,
"proxy" : "",
"difficulty" : 34661425923.97693634,
"testnet" : false,
"relayfee" : 0.00001000,
"errors" : ""
}

PHP:

<?php
$data = file_get_contents("data.json");
$array = json_decode($data, true);
$keys = array_keys($array);
$rows = array();
foreach($keys as $key) {
    $value = $array[$key];
    $rows[] = "'" . $value . "'";
}
echo $rows;
$values = implode(",", $rows);

$hostname = 'localhost';
$username = 'username';
$password = 'password';
$database = 'database';
try
{
    $dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

    if($dbh)
      echo 'Connected to database'; // echo a message saying we have connected

    $datetime = date("Y-m-d H:i:s");
    $count = $dbh->exec("INSERT INTO getinfo VALUES ('','" . $datetime . "'," . $values . "");
    echo "Going into DB: ('','" . $datetime . "'," . $values . ")";
    echo $count;// echo the number of affected rows
    $dbh = null;// close the database connection
}
catch(PDOException $e)
{
    print_r($e);
}
?>

Info ECHOED out and sent as VALUES:

('','2014-09-28 16:11:33','90200','70002','322880','-1','49','','34661425923.977','','1.0E-5','')

The first two values are manual entries i created for ID and DATE/TIME

As you can see the output is not the same as input however these values work with a manual INSERT query but not through this script also (hence the two problems).

Answer 1

Just to make sure I didn't miss anything, these are the differences I notice:

| Name       | Actual Imploded | Expected             |
| blocks     | 322880          | 322877               |
| difficulty | 34661425923.977 | 34661425923.97693634 |
| relayfee   | 1.0E-5          | 0.00001000           |
| testnet    | ''              | false                |

To preserve the precisions of your numeric data, you can try set the JSON decode bitmask option to JSON_BIGINT_AS_STRING like this:

json_decode($data, true, 512, JSON_BIGINT_AS_STRING);

As for the value of testnet, try printing $values using var_dump() instead of print_r() (if that's what you have been using). print_r() doesn't show type; false will be shown as an empty string.

Finally, not exactly sure why your SQL insertion doesn't work, but try to change the default error mode of PDO to PDO::ERRMODE_EXCEPTION so that any database errors will be thrown as an exception, to ensure you really didn't miss any database errors.

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

By the way, you should consider using prepare statements.

UPDATE

To deal with earlier version of PHP where JSON_BIGINT_AS_STRING is not supported, try of one of the following:

1. Use preg_replace to add quotes to integer: preg_replace('/("\w+"):(\d+(\.\d+)?)/', \\1:"\\2"', $data)
2. Increase PHP precision by either adding ini_set('precision', 20); to your PHP app or update your php.ini by changing precision = 20.
3. Use ServiceJSON like this:

$json = new ServicesJSON();
$var = $json->decode($data);