Json_encode改变了我的查询顺序

Question

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.

Answer 1

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