从JSON数组向MySQL插入多个空行

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?

Answer 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'];