PHP usort before passing to json_encode

Question

I have 2 mysql queries, one to get all the my friends and the other to get the details and a sum of the points for each of my friends.

$get_my_friends_query = mysqli_query($GLOBALS['connect'], "SELECT friend_user_id FROM tbl_friend_link WHERE tbl_friend_link.your_user_id = 1");

while($get_my_friends_result = mysqli_fetch_assoc($get_my_friends_query))
{
   $get_friend_user_details_query = mysqli_query($GLOBALS['connect'], "SELECT tbl_registered_user.user_id, tbl_registered_user.first_name, tbl_registered_user.last_name,  tbl_registered_user.profile_name, SUM(tbl_point.amount) FROM tbl_registered_user JOIN tbl_point ON tbl_registered_user.user_id = tbl_point.user_id WHERE tbl_registered_user.status = 1 AND tbl_registered_user.user_id = '" . $get_my_friends_result['friend_user_id'] ."' GROUP BY tbl_registered_user.user_id ORDER BY SUM(tbl_point.amount)");
   $get_friend_user_details_result = mysqli_fetch_assoc($get_friend_user_details_query);

   $result = array(
      "done" => "true",
      "name" => $get_friend_user_details_result['first_name'] . " " . $get_friend_user_details_result['last_name'],
      "profile_name" => $get_friend_user_details_result['profile_name'],
      "userID" => $get_friend_user_details_result['user_id'],
      "points" => $get_friend_user_details_result['SUM(tbl_point.amount)'],
      "number_friends" => mysqli_num_rows($get_my_friends_query),
      "error_message" => ""
   );
   $row_array[] = $result;
}

echo json_encode($row_array);

I am using the first query because I would like a result to be display regardless of whether they have any points or not. I would like to sort the results based on the SUM of the points however that information is being retrieved in the second query however I could not find a suitable explanation on how to use usort on the $row_array.

possible duplicate of [How do I sort a multidimensional array in php](http://stackoverflow.com/questions/96759/how-do-i-sort-a-multidimensional-array-in-php) — Jon, Jul 11 '13 at 10:13
The PHP manual page for [`usort`](http://php.net/manual/en/function.usort.php) provides examples as well as explanation. Is there a problem with them? — Jon, Jul 11 '13 at 10:14
Not at all I just don't know how to apply to usort on $get_friend_user_details_result['SUM(tbl_point.amount)'] — puks1978, Jul 11 '13 at 10:20
If I were you, I wouldn't use a query inside a while loop, instead just use the second query and add another join to get the results for the first query. This is because you only have one call to your database rather than several, otherwise you could experience performance issues. — Rwd, Jul 11 '13 at 10:25
"*I am using the first query because I would like a result to be display regardless of whether they have any points or not.*" - consider [outer joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) instead. — eggyal, Jul 11 '13 at 10:26
I did try a nested select query however it only produced the results where friend_user_id existed in tbl_points where I would like to display all friends but then show their points. — puks1978, Jul 11 '13 at 10:26
Thanks for sharing the join example, this clears it up a lot. — puks1978, Jul 11 '13 at 10:28
Nested queries and joins are not the same. If you want to, you can still have 2 queries, however, I would strongly suggest using another join in your second query rather than multiple calls within a loop!!! :) — Rwd, Jul 11 '13 at 10:29

score 1 · Accepted Answer · answered Jul 11 '13 at 10:33

1

I got it to work with the following:

function sorter($key){
    return function ($a, $b) use ($key) {
        return $a[$key] - $b[$key];
    };
}
usort($row_array, sorter('points'));
var_dump($row_array);

answered Jul 11 '13 at 10:33

Expedito

7,493
5
27
40

score 0 · Answer 2 · answered Jul 11 '13 at 10:34

0

usort($row_array, function($a, $b)
{
 return ($a['points']<$b['points']) ? 1 : -1;
});

answered Jul 11 '13 at 10:34

Rwd

27,979
5
47
62

score 0 · Answer 3 · answered Jul 11 '13 at 11:09

To "display regardless of whether they have any points or not" you should look into outer joins. Example from Sams Teach Yourself MySQL in 10 Minutes:

mysql>  SELECT c.name, o.order_date
    ->  FROM customers c
    ->  LEFT OUTER JOIN orders o
    ->  ON o.customer_code = c.customer_code
    ->  AND o.order_date BETWEEN '2006-02-01' AND '2006-02-28';
+-------------------------+------------+
| name                    | order_date |
+-------------------------+------------+
| Presidents Incorporated | NULL       |
| Science Corporation     | 2006-02-02 |
| Science Corporation     | 2006-02-05 |  
| Musicians of America    | 2006-02-01 |
| Musicians of America    | 2006-02-02 |
+-------------------------+------------+
5 rows in set (0.00 sec)

In addition I'd use the AS keyword to name the columns what I want directly in the query, instead of having to do what you want there with all the get_friend_user_details_result.

To get exactly what you want in a single query improves speed quite a lot and in many cases also clean up your code quite a bit.

PHP usort before passing to json_encode

3 Answers3