php - 依次更新mysql中的json结果

Question

我实际上想以相同的顺序更新 mysql 的输出。我有一个数据库，其中已经存在一些数据，并且 sendondate 很常见。我尝试更新，但它只更新最后一个输出，而不更新所有序列。请帮我解决这个问题..

<?php
    $data = '[{"message":"Hello+Test+Message","sender":"test","billcredit":"0.00","messageStatus":"DND","sendondate":"2015-04-23 12:27:00","provider":"aaaa"},{"message":"Hello+Test+Message","sender":"test","billcredit":"0.00","messageStatus":"DELIVERD","sendondate":"2015-04-23 12:27:00","provider":"aaaa"}]';


       // $objs = json_decode($data);
    $con=mysqli_connect("localhost","root","Password*9","sms9");
        $objs = json_decode($data,true);
        foreach ($objs as $obj){
            $repor = $obj['messageStatus'];
           // echo $repor . '<br />';
            $sen= $obj['sendondate'];
            //echo $sen;

            $repor=array();
            while($row = mysql_fetch_array($repor))
        {
            $sql2=  "Update detail SET Delivery='$repor' WHERE Datetime='$sen'";
    if(mysqli_query($con, $sql2)){
            echo "up";
            }
        }

        }   

    ?>

Answer 1

只需删除 $repor=array() 和 while 条件(我看不到，它的目的是什么):

<?php

$data = '[
   {"message":"Hello+Test+Message","sender":"test","billcredit":"0.00","messageStatus":"DND","sendondate":"2015-04-23 12:27:00","provider":"aaaa"},
   {"message":"Hello+Test+Message","sender":"test","billcredit":"0.00","messageStatus":"DELIVERD","sendondate":"2015-04-23 12:27:00","provider":"aaaa"}
]';

// $objs = json_decode($data);
$con = mysqli_connect("localhost","root","Password*9","sms9");
$objs = json_decode($data,true);

foreach ($objs as $obj) {
    $repor = $obj['messageStatus'];
    // echo $repor . '<br />';
    $sen = $obj['sendondate'];
    //echo $sen;

    $sql2 = "Update detail SET Delivery='$repor' WHERE Datetime='$sen'";
    if (mysqli_query($con, $sql2)) {
       echo "up";
    }
}

根据您的评论进行更新:

在sql中，如果你想修改特定的行，你必须使用适当的where条件来匹配它们。通常，基于非唯一列(例如日期时间)选择行并不是一个好主意。因此，如果您可以更改 db 和 json 数据，通常的方法是定义某种 id(通常是整数):

$data = '[
   {"id": 123, "message":"Hello+Test+Message","sender":"test","billcredit":"0.00","messageStatus":"DND","sendondate":"2015-04-23 12:27:00","provider":"aaaa"},
   {"id": 666, "message":"Hello+Test+Message","sender":"test","billcredit":"0.00","messageStatus":"DELIVERD","sendondate":"2015-04-23 12:27:00","provider":"aaaa"}
]';
...
    $id = $obj['id'];
    ...
    $sql2 = "Update detail SET Delivery='$repor' WHERE id=$id";

或者，如果您无法添加额外的列，则必须添加已定义的列之一以区分具有相同日期时间的行，例如发送者(发送者值必须不同，记录才能区分它们，即json 示例数据中的情况并非如此，但这只是用法示例):

   ...
   $sender = $obj['sender'];
   ...
   $sql2 = "Update detail SET Delivery='$repor' WHERE Datetime='$sen' AND sender='$sender'";

额外安全提示:一般来说，你应该正确escape发送到数据库之前的数据，以防止 sql injection 。或者您可以使用prepared statements与 binded parameters .