0

I have written a function to get all the users details from a 2 table. This is the code

//get all the diff each users
    $db->query("SELECT DISTINCT (user_fb_id )FROM users_points WHERE users_points.match_id >= '$matchId'");
    $distinctUsers = $db->resultsArray;

        foreach($distinctUsers as $key=>$user){

            $db->query("SELECT SUM(points) AS totalPoints FROM users_points WHERE user_fb_id = '{$user['user_fb_id']}' AND match_id >= '$matchId' ORDER BY totalPoints DESC");

            $result[$key]['user_fb_id'] = $user['user_fb_id'];
            $totalPoint = $db->resultsArray;
            $result[$key]['totalPoints'] = $totalPoint[0]['totalPoints'];
            $db->query("SELECT user_firstName, user_lastName FROM user WHERE user_fb_id = '{$user['user_fb_id']}'");

            $userDetail = $db->resultsArray;
            $result[$key]['user_name'] = $userDetail[0]['user_firstName'].' '.$userDetail[0]['user_lastName'];

        }

    return $result;

using this function i can get users details. but not in an order. It should be sorted DESC.

How can to write single query for this function, instead of having 3 quires.?

or else,

how to sort the array 'totalPoints'?

Suraj Malinga
  • 101
  • 1
  • 15
  • There are several reference questions on SO, including [this one about all basic ways to sort arrays and data in PHP](http://stackoverflow.com/questions/17364127/reference-all-basic-ways-to-sort-arrays-and-data-in-php#17364128). – larsAnders Mar 20 '14 at 19:30

1 Answers1

1

Here's a basic join query to combine all three into one.

SELECT DISTINCT u.user_fb_id, SUM(u.points) as total_points, r.user_firstname, r.user_lastname 
FROM user_points u, user r 
WHERE u.match_id >= 2300 AND r.user_id = u.user_fb_id 
ORDER BY total_points DESC;

This may not be the most optimized query, but it's oodles faster than looping with three. Definitely play around with it.

phpisuber01
  • 7,585
  • 3
  • 22
  • 26