php - multi_query() 有错误

Question

我需要一些帮助来找出我在随附代码中的错误。当我使用 if ($conn->query($sql) === TRUE) { 方法运行两个查询中的任何一个时，每个查询都可以正常工作。但是当我尝试将它们与 if ($conn->multi_query($sql) === TRUE) { 方法组合时。没有上传记录。我在这里做错了什么。

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

$device = $row['device'];
$type = $row['data_type'];
$zone = $row['zone'];
$sample = $row['sample'];
$count = $row['count'];
$time = $row['date_time'];
$epoch = $row['epoch_stamp'];

$sql = "INSERT INTO data(device, type, zone, sample, count, date_time, epoch_stamp) VALUES('$device', '$type', '$zone', '$sample', '$count', '$time', '$epoch');";
$sql . = "UPDATE data SET date_time = FROM_UNIXTIME(epoch_stamp);";
if ($conn->multi_query($sql) === TRUE) {
//if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
}
}
$conn->close();
?>

...是的，我知道这段代码不安全，但对于我的测试目的来说没问题。

Answer 1

本质上，下面的代码在我们进入循环之前是相同的，在该循环中我们构建了一个要执行的查询数组，并在我们离开循环后在最后执行一次 multi_query() 。为简洁起见，我删除了一些在开头呼应信息的评论和声明。我希望这看起来不错并且有效....

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

    $conn = new mysqli($servername, $username, $password, $dbname);
    if( $conn->connect_error ) die("Connection failed: " . $conn->connect_error);
    $payload_dump = $_POST['payload'];

    $payload_array = json_decode($payload_dump,true);

    if( is_array( $payload_array ) ){

        $queries=array();

        foreach( $payload_array as $row ){
            //get the data_payload details
            $device = $row['device'];
            $type = $row['data_type'];
            $zone = $row['zone'];
            $sample = $row['sample'];
            $count = $row['count'];
            $time = $row['date_time'];
            $epoch = $row['epoch_stamp'];

            /*note: we do not need to add the semi-colon here as it gets added later when we implode the array */
            $queries[]="INSERT INTO `data` ( `device`, `type`, `zone`, `sample`, `count`, `date_time`, `epoch_stamp` ) VALUES ('$device', '$type', '$zone', '$sample', '$count', '$time', '$epoch')";
        }
        /*
            Previously the below query was being execute on every iteration
            ~ because $epoch is now the last one encountered in the array, 
            the value that is updated in ALL records is as it would have been
            previously.
        */
        $queries[]="UPDATE `data` SET `date_time` = from_unixtime( $epoch );";

        $sql=implode( ';', $queries );
        if ( $conn->multi_query( $sql ) === TRUE ) {
            echo "New records created and updated successfully";
        } else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
    }
    $conn->close();

?>