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?

If you check that json string with jsonlink.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
– RiggsFolly, Commented Aug 7, 2015 at 16:17

Don't Panic · Accepted Answer · 2015-08-07 18:28:43Z

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 7, 2015 at 18:28

answered Aug 7, 2015 at 16:01

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Don't Panic Over a year ago

@CodeGodie agreed, just added that.

CodeGodie Over a year ago

Nice.. also what do you think about his JSON format? is it correct?

Don't Panic Over a year ago

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.

ghowe Over a year ago

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.

Don't Panic Over a year ago

@ghowe I realized I had misunderstood what was going on with the JSON. I updated the answer.

RiggsFolly · Accepted Answer · 2015-08-07 16:22:40Z

0

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.

answered Aug 7, 2015 at 16:22

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Collectives™ on Stack Overflow

Inserting multiple blank rows into MySQL from JSON array

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related