PHP script to Sort a mySQL table output in JSON

Question

Very new to PHP and JSON, I'm using the following to display a list of rows:

$db = new PDO('mysql:host=localhost;dbname=rugbysuperleague','xxx','xxx');
$stmt = $db->query("SELECT * FROM `table`");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt->execute();
echo json_encode($data);

It returns all the rows in the table OK, But how do I return the rows sorted based on aAscending values of a specific column, if tie, sort by next column. etc.

I'm trying to list in correct order a Sports Team Standings table. So that the team with most points is in 1st position, if there is a tie, sort by point difference etc.

order by in your sql query? json is just a string. it has no "order". you'd have to build your JS data structure in whatever order you want if you want the json to be ordered, and there's no guarantee that what you stuff into a JSON string will be in the same order it was in the original JS structure. — Marc B
– Marc B, Commented Apr 28, 2014 at 20:54

AbraCadaver · Accepted Answer · 2014-04-28 21:13:11Z

1

Use ORDER BY and list the the columns in order of how you want it ordered:

SELECT * FROM `table` ORDER BY `points` DESC, `point_diff` ASC

You also don't need execute() and probably not the fetchAll().

edited Apr 28, 2014 at 21:13

answered Apr 28, 2014 at 21:00

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP script to Sort a mySQL table output in JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related