Inserting multiple blank rows into MySQL from JSON array

Question

The below PHP code receives a JSON array from a Python script in the format of:

{'payload': '[{"sample": 1, "count": 60, "sqlTimeStamp": "2015-08-03 15:14:05", "id": 1, "zone": 1}, {"sample": 2, "count": 60, "sqlTimeStamp": "2015-08-03 15:14:07", "id": 2, "zone": 1}, {"sample": 3, "count": 60, "sqlTimeStamp": "2015-08-03 15:14:09", "id": 3, "zone": 1}]'}

The PHP code is as follows:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "practice";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connection made...";

$payload_dump = $_POST['payload'];
//$payload_dump = '{"device":"gabriel","data_type":"data","zone":1,"sample":6,"count":0,"time_stamp":"00:00"}';
echo $payload_dump;

$payload_array = json_decode($payload_dump,true);

if(is_array($payload_array)){
foreach($payload_array as $row){
//get the data_payload details
$sample = $payload_array['sample'];
$count = $payload_array['count'];
$time = $payload_array['sqlTimeStamp'];
$id = $payload_array['id'];
$zone = $payload_array['zone'];
//$time = $payload_array['time_stamp'];

$sql = "INSERT INTO data(sample, count, sqlTimeStamp, id, zone) VALUES('$sample', '$count', '$time', '$id', '$zone')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
}
}
$conn->close();
?>

The PHP script creates the correct number of new rows in the MySQL database, but all the values are zero/null. What am I doing wrong?

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Aug 07 '15 at 15:49
If you check that json string with [jsonlink.com](http://jsonlint.com/) you will see that it is not in fact valid json. This will of course have an effect on your code. First make sure you have valid JSON then test again — RiggsFolly, Aug 07 '15 at 16:17

Don't Panic · Accepted Answer · 2015-08-07T18:28:43.830

1

Assuming the JSON is actually like this:

[{"sample": 1, "count": 60, "sqlTimeStamp": "2015-08-03 15:14:05", "id": 1, "zone": 1}, 
    {"sample": 2, "count": 60, "sqlTimeStamp": "2015-08-03 15:14:07", "id": 2, "zone": 1}, 
    {"sample": 3, "count": 60, "sqlTimeStamp": "2015-08-03 15:14:09", "id": 3, "zone": 1}]

It looks like the problem is that you are looping over $payload_array, but you are not referencing the $row variable for the current iteration of the loop. So instead of

foreach($payload_array as $row) {
    $sample = $payload_array['sample'];
    $count = $payload_array['count'];
    $time = $payload_array['sqlTimeStamp'];
    $id = $payload_array['id'];
    $zone = $payload_array['zone'];

It needs to be

foreach($payload_array as $row) {
    $sample = $row['sample'];
    $count = $row['count'];
    $time = $row['sqlTimeStamp'];
    $id = $row['id'];
    $zone = $row['zone'];

edited Aug 07 '15 at 18:28

answered Aug 07 '15 at 16:01

Don't Panic

41,125
10
61
80

@CodeGodie agreed, just added that. – Don't Panic Aug 07 '15 at 16:03
Nice.. also what do you think about his JSON format? is it correct? – CodeGodie Aug 07 '15 at 16:04
I think based on what he says is happening, it's probably a copy/paste issue. If the JSON was really in that format, it seems like nothing would be getting into the database. – Don't Panic Aug 07 '15 at 16:05
Making the changes suggested above, I get zone rows imported. The JSON string was a copy and past from the output of the python as it appears in the console. Not sure it is actually what is being received by the PHP. – ghowe Aug 07 '15 at 16:35
@ghowe I realized I had misunderstood what was going on with the JSON. I updated the answer. – Don't Panic Aug 07 '15 at 18:30

score 0 · Answer 2 · answered Aug 07 '15 at 16:22

Before going any further add this test after doing the json_decode

$payload_array = json_decode($payload_dump,true);
if ( json_last_error() !== JSON_ERROR_NONE ) {
    echo 'JSON Error : ' . json_last_error() . 
         ' ' . 
         json_last_error_msg();
    exit;
}

If the JSON data you are processing is NOT Valid this should tell you so.

Inserting multiple blank rows into MySQL from JSON array

2 Answers2