PHP 没有将 MySQL 查询中的所有键写入数组

Question

我在将所有字段名称作为 PHP 中 MySQL 查询的数组的键写入时遇到了奇怪的情况。所有值都从查询到数组，但不是所有键。我将响应编码为 JSON 并将其发送到 Android 客户端。

以下是其中一个 JSON 对象 的原始输出，包含在 JSON 数组 中。正如您所看到的，许多键只是数字。数字的顺序也不符合原始查询。

08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):   {
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "STATE": "1",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "RECEIVER_ID": "29",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "LAST_MODIFIED": "2013-08-17     06:15:01",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "CREATED": "2013-08-07     15:51:25",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "UNIQUE_ID": "cef3fc71-073e-11e3-8ffd-0800200c9a66",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "3": "2",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "2": "29",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "1": "918fa7f5-073c-11e3-8ffd-    0800200c9a66",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "0": "2",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "7": "2013-08-07 15:51:25",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "6": "2013-08-17 06:15:01",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "DATA_RECORD_UUID": "918fa7f5-073c-11e3-8ffd-0800200c9a66",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "5": "cef3fc71-073e-11e3-8ffd-0800200c9a66",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "4": "1",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "ID": "2",
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):    "IS_TEST": "2"
08-18 05:41:49.130: I/GETJSON=ACCESSOR(JSON)(20407):   },

我一直在使用此函数创建一个记录数组:

    public function getRowArray($mysql_query_result){
    $result=array();
    while($row=mysql_fetch_array($mysql_query_result)){
        $result[]=$row;
    }
    return $result;
}

查询结果来自该 block ，其中变量经过mysql_real_escape_string预先转义:

$ary=mysql_query("SELECT tRelations.ID,tRelations.DATA_RECORD_UUID,tRelations.RECEIVER_ID,
    tRelations.IS_TEST,tRelations.STATE,tRelations.UNIQUE_ID,tRelations.LAST_MODIFIED,
    tRelations.CREATED FROM tRelations 
            JOIN tUserData ON (tUserData.UNIQUE_ID=tRelations.DATA_RECORD_UUID) 
            JOIN tUsers ON (tUsers.ID=tUserData.USER_ID) 
        WHERE tUsers.ID=$user_id AND tRelations.last_modified>'$last_sync' 
        ORDER BY tRelations.ID ASC;") or die(mysql_error());


    if(mysql_num_rows($ary)>0){
        $array2= $this->getRowArray($ary);

有人可能知道为什么 key 乱序并且没有全部被写入吗？或者，还有其他人喜欢将 MySQL 结果转换为 JSON 数组的其他方法吗？

Answer 1

列的顺序由选择查询的顺序决定。

SELECT tRelations.ID,tRelations.DATA_RECORD_UUID,tRelations.RECEIVER_ID,
    tRelations.IS_TEST,tRelations.STATE,tRelations.UNIQUE_ID,tRelations.LAST_MODIFIED,
    tRelations.CREATED FROM tRelations 

它显示:ID、DATA_RECORD_UUID、RECEIVER_ID、IS_TEST、STATE、UNIQUE_ID，并且它也将以这种方式添加到数组中。您可以将其重新排序为

SELECT tRelations.STATE,tRelations.RECEIVER_ID,tRelations.LAST_MODIFIED,
    tRelations.CREATED,tRelations.UNIQUE_ID,tRelations.DATA_RECORD_UUID,tRelations.ID,
    tRelations.IS_TEST FROM tRelations 

但是很难按该顺序获取其他字段。您可能必须自己填写它们，除非您可以使用 tRelation.SOME_FIELD as '2' 等别名。

public function getRowArray($mysql_query_result){
    $order=array("STATE", "RECEIVER_ID", "LAST_MODIFIED", "CREATED", "UNIQUE_ID", "3", ....);
    while($row=mysql_fetch_array($mysql_query_result)){
        $newRow = array();
        foreach($order as $value) {
            $newRow[$value] = $row[$value];
        }
        $result[]=$newRow;
    }
    return $result;
}

这实际上会逐行循环并按照您想要的列顺序对其进行排序。

附注:

您的代码非常危险并且容易受到 SQL 注入(inject)攻击。永远不要将变量的内容直接放入 SQL 查询中!

$user_id = "Somestring with spaces";
$last_sync = "2013-08-07 15:51:25";
$ary=mysql_query("SELECT tRelations.ID,tRelations.DATA_RECORD_UUID,tRelations.RECEIVER_ID,
    tRelations.IS_TEST,tRelations.STATE,tRelations.UNIQUE_ID,tRelations.LAST_MODIFIED,
    tRelations.CREATED FROM tRelations 
            JOIN tUserData ON (tUserData.UNIQUE_ID=tRelations.DATA_RECORD_UUID) 
            JOIN tUsers ON (tUsers.ID=tUserData.USER_ID) 
        WHERE tUsers.ID=$user_id AND tRelations.last_modified>'$last_sync' 
        ORDER BY tRelations.ID ASC;") or die(mysql_error());

这将导致查询

SELECT tRelations.ID,tRelations.DATA_RECORD_UUID,tRelations.RECEIVER_ID,
    tRelations.IS_TEST,tRelations.STATE,tRelations.UNIQUE_ID,tRelations.LAST_MODIFIED,
    tRelations.CREATED FROM tRelations 
            JOIN tUserData ON (tUserData.UNIQUE_ID=tRelations.DATA_RECORD_UUID) 
            JOIN tUsers ON (tUsers.ID=tUserData.USER_ID) 
        WHERE tUsers.ID=Somestring with spaces AND tRelations.last_modified>'2013-08-07 15:51:25'
        ORDER BY tRelations.ID ASC;"

你看到缺陷了吗？那是因为“$user_id”没有被转义。您应该始终使用mysqli_escape_string转义你的字符串。例如:

$ary=mysql_query("SELECT tRelations.ID,tRelations.DATA_RECORD_UUID,tRelations.RECEIVER_ID,
    tRelations.IS_TEST,tRelations.STATE,tRelations.UNIQUE_ID,tRelations.LAST_MODIFIED,
    tRelations.CREATED FROM tRelations 
            JOIN tUserData ON (tUserData.UNIQUE_ID=tRelations.DATA_RECORD_UUID) 
            JOIN tUsers ON (tUsers.ID=tUserData.USER_ID) 
        WHERE tUsers.ID=".mysqli_escape_string($user_id)." AND tRelations.last_modified>'".mysqli_escape_string($last_sync)."' 
        ORDER BY tRelations.ID ASC;") or die(mysql_error());

或者更好:与数据库交互时使用 PDO(PHP 数据对象)。自 PHP 5.1 起支持 PDO，并允许与数据库进行安全交互。它需要更多的代码，但显着提高了 PHP 应用程序的可读性和安全性。