CODEWITHSUNDEEP
phpmysql
Jacob
Jacob

Reputation: 2071

Order after executed?

Im trying to do a PHP script where it order users based on a point system. Problem is that the points is calculated after I execute the users. Like this:

$getScore = $db->prepare("SELECT * FROM `users`");
$getScore->execute();
$results = $getScore->fetchAll();

foreach($results as $row):

$likes = $get->likes("user", $row["id"]);
$subscribers = $get->subscribers("user", $row["id"]);

$score = $get->score($likes, $subscribers, $row["date"]);

    echo $row["username"];

endforeach;

Without having another execution how do I order the results? I want the user with highest value on $score to be shown first. I've tried alot of things but without succession.

Thanks in advance!

Upvotes: 0

Views: 59

Answers (2)

Corstian Boerman
Corstian Boerman

Reputation: 848

First, sorry for suggesting another execution flow. Next, this is my solution:

$getScore = $db->prepare("SELECT * FROM `users`;");
$getScore->execute();
$results = $getScore->fetchAll();

// Result is something like [rownum][colomname]

array_walk_recursive($results, function($item, $key) {
    $id = $item["id"];
    $date = $item["date"];

    $arr = array();

    $arr['likes'] = $get->likes("user", $id);
    $arr['subscribers'] = $get->subscribers("user", $id);
    $arr["score"] = $get->score($arr["likes"], 
                                $arr["subscribers"],
                                $date);

    $item = array_merge($item, $arr);
});

In this code you get the results from your database. Next you calculate things like the score and you add this to the array.

To sort this array on the score you can use the PHP multisort function (http://php.net/manual/en/function.array-multisort.php):

foreach ($results as $key => $row) {
    $score[$key] = $row["score"];
}

array_multisort($score, SORT_DESC, $results);

When you're looping your loop to print the data for example you can do this:

foreach($results as $row) {
    $likes = $row["likes"];
    $subscribers = $row["subscribers"];
    $score = $row["score"];

    echo("your stuff over here");
}

Good luck!

Upvotes: 1

Attila
Attila

Reputation: 3406

You can implement this different ways.

If you have already loaded all the data from the databse you can order them using PHP. Custom sorting can be done using usort.

PHP.net - usort documentation

Maybe you could use something like this:

$users = array();

// Load all the users and calculate scores
foreach($results as $row):
   $users[$row["username"]] = $get->score($likes, $subscribers, $row["date"]);
endforeach; 

// Sort users based on score
sort($users);

// Output users, sorted by score
foreach($score as $key => $row):
      echo $key, " score:", $score;
endforeach;

If you would like to use a more complex data structure, all you have to do is implement the special comparision to ensure ordering by score with usort.

If you store the points in the database, then you could use a special query including an ORDER BY clause, this way returning the users already ordered.

I think using the DBMS to order the users would result in a more scalable solution. If you have really high number of users, it is not going to perform well to sort all the users each page load.

Upvotes: 1

Related Questions