PHP没有从MySQL Query向数组写入所有键 - PHP not writing all keys to array from MySQL Query

我有一段奇怪的时间，从PHP中的MySQL查询中，将所有字段名作为键写入数组。所有的值都是从查询到数组的，但不是所有的键。我正在用JSON对响应进行编码，并将其发送到Android客户端。

以下是包含在JSON Array中的一个JSON Objects的原始输出。正如你所看到的，许多键只是数字。数字的顺序也与原始查询不一致。

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;
}

查询结果来自这个块，其中包含用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);

有人知道为什么钥匙出了问题，而且没有全部写出来吗？或者，是否有其他人喜欢将MySQL结果转换为JSON数组的其他方法？

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

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注入的攻击。永远不要将变量的内容直接放在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数据对象）。PDO从PHP 5.1开始就受到支持，并允许与数据库进行安全交互。它需要更多的代码，但显著提高了PHP应用程序的可读性和安全性。