Json_encode改变了我的查询顺序

问题描述：

I have a mysql query that orders by a column. It works fine if I just run the php. After I use json_encode and send it to the client, the order is changed to the primary key. Why does it do this and is there a solution?

Query looks like:

try{
    $dbh = new PDO('mysql:host=localhost;dbname=Batik', 'root', 'root');
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
    $_SESSION['table'] = $_SESSION['category'] = $table = $_GET['button'];
    $count = $dbh->query("SELECT * FROM $table ORDER BY `order` ASC");
    $count->setFetchMode(PDO::FETCH_ASSOC);
    $myarray = array();
    while ($row = $count->fetch()) {
        $id = array_shift($row);
        $myarray[$id] = $row;
    }
    print json_encode($myarray);
}  catch (PDOException $exception) {
    echo "There was an error uploading your information, please contact James for assistance. "; 
    error_log($exception->getMessage());
 };

So, the output I want and get in plain php is like this: (ID = primary_key)

  Order:     ID:      Location:
  1             4         foto1.jpg
  2             5         foto3.jpg
  3             3         foto2.jpg
  4             2          foto4.jpg
  5             1          foto5.jpg

After I json_encode the array and output to client, I get this: (ID = primary_key)

  Order:     ID:       Location:
  5             1          foto5.jpg
  4             2          foto4.jpg
  3             3          foto2.jpg
  1             4          foto1.jpg
  2             5          foto3.jpg

I hope this makes sense.

我有一个按列排序的mysql查询。如果我只运行php，它工作正常。在我使用json_encode并将其发送到客户端后，订单将更改为主键。为什么这样做并且有解决方案？ p>

查询看起来像： p>

  try {
 $ dbh = new PDO（  'mysql：host = localhost; dbname = Batik'，'root'，'root'）; 
 $ dbh-＆gt; setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_WARNING）; 
 $ _SESSION ['table']  = $ _SESSION ['category'] = $ table = $ _GET ['button']; 
 $ count = $ dbh-＆gt; query（“SELECT * FROM $ table ORDER BY`order`ASC”）; 
 $  count-＆gt; setFetchMode（PDO :: FETCH_ASSOC）; 
 $ myarray = array（）; 
 while（$ row = $ count-＆gt; fetch（））{
 $ id = array_shift（$ row）; \  n $ myarray [$ id] = $ row; 
} 
 print json_encode（$ myarray）; 
} catch（PDOException $ exception）{
 echo“上传您的信息时出错，请联系James寻求帮助 “;  
 error_log（$ exception-＆gt; getMessage（））; 
}; 
  code>  pre> 
 
 因此，我希望并在纯PHP中获取的输出是这样的：  （ID = primary_key） p> 
 
 
 顺序：ID：位置：
 1 4 foto1.jpg 
 2 5 foto3.jpg 
 3 3 foto2.jpg 
 4 2  foto4.jpg 
 5 1 foto5.jpg 
  code>  pre> 
 
 在我对数组进行json_encode并输出到客户端之后，我得到了这个：（ID = primary_key） p>  
 
 
 顺序：ID：位置：
 5 1 foto5.jpg 
 4 2 foto4.jpg 
 3 3 foto2.jpg 
 1 4 foto1.jpg 
 2 5 foto3。  jpg 
  code>  pre> 
 
 我希望这是有道理的。 p> 
  div>

答

Short answer is : don't use

$id = array_shift($row);
$myarray[$id] = $row;

to build your array. Build a real 0-based numerically indexed array instead with this syntax :

$myarray[] = $row;

and the array will be built with the items in the order they are looped over, although with a slightly different record structure (no meaningful keys).

As an alternative, to preserve your current structure, you could order the array in php instead of SQL with the *sort family of functions (usort in particular), like so (assuming your "order" field is numeric, see http://www.php.net/manual/en/function.usort.php for an example with a string-type field) :

while ($row = $count->fetch()) {
    $myarray[] = $row;
}
usort($myarray,function ($a, $b){return ($a > $b) ? 1 : -1;});

答

the client orders the array atuomatically if you use a literal object with numbers. So if you leave out the id you shuould get the right content.

replace

while ($row = $count->fetch()) {
    $id = array_shift($row);
    $myarray[$id] = $row;
}

//doing so you create this
json array=[
 0=undefined,
 1={order:5,id:1;location1},
 2={order:4,id:2;location2},
 3={order:3,id:3;location3},
 4={order:2,id:4;location4},
]
//error

with

while ($row = $count->fetch()) {
    $myarray[] = $row;
}

basicaly you transform your literal object into a simple array.wich can lead to several errors if you start to delete some images.

then you probably just need the id and the location

SELECT id,location FROM ... ORDER BY order ASC

and you have

[[1,location1],[2,location2]]

[{id:1,location:"location1"},{id:2,location:"location2"}]
  //0 wich is order 1        //1 wich is order 2

The sort order created by your mysql query is the json array index.

Json_encode改变了我的查询顺序

相关推荐