Using PHP to order a MYSQL query result

Question

I have a basic MySQL query:

$getFeed = "SELECT posts.postID, posts.postTitle, .....
FROM posts
LEFT JOIN users ON posts.userID = users.id
LEFT JOIN postScore ON posts.postID = postScore.postID          
GROUP BY posts.postID

$feedResult = mysql_query($getFeed) or die; 
while($row1 = mysql_fetch_array($feedResult)){
    $postOwner = $row1["userID"];
    $postID = $row1["postID"];
    etc...
}

So now here is where I am stuck. I want to order the feed using PHP by some sort of combination of votes, timestamp, etc. Basically create a very simple score algorithm. I would need to use some of the variables above to to do the math, then display the results in order. How can I do that with PHP ?

Other question, should I do this on the fly with PHP or save the algorithm "score" in the DB then just order by that column?

EDIT: Lets say I store the score the DB, however this "score" would be based off an algorithm that is time sensitive (meaning the score would change as time passes). Would it be appropriate to create a backend script that ran at an interval to update all the scores in the DB?

The best way would probably be saving the score in the DB and then just order by that column. — Kokos
– Kokos, Commented Jun 23, 2011 at 17:16
Do you just want to order the posts by their postScore, or are you going to do something more dynamic/advanced? — hughes
– hughes, Commented Jun 23, 2011 at 17:19
I wanted to do something more advanced using time, similar to how HN does it. news.ycombinator.com/item?id=1781013 — Dave Chenell
– Dave Chenell, Commented Jun 23, 2011 at 17:20

NullRef · Accepted Answer · 2011-06-23 17:17:10Z

3

It is going to be much more efficient if you can rank them in MySQL before pulling them. Sorting moderate to large datasets in PHP is resource intense.

answered Jun 23, 2011 at 17:17

NullRef

3,7381 gold badge22 silver badges18 bronze badges

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

Comments

Gidon Wise · Accepted Answer · 2011-06-23 17:20:42Z

1

You are typically better off ordering it in the DB. For a few reasons 1. the result set might be too large for a php process. 2. LIMIT and pagination are faster in a DB.

But, if you do want to do it in PHP, then

here is the pattern. first get the records into an array and then sort them with one of the php sort functions.

while($row1 = mysql_fetch_array($feedResult)){
  $rows[] = $row1;
}
usort($rows, 'mycmp');

function mycmp($a, $b)
{
    if ($a['score'] == $b['score']) {
        return 0;
    }
    return ($a['score'] < $b['score']) ? -1 : 1;
}

answered Jun 23, 2011 at 17:20

Gidon Wise

1,9161 gold badge11 silver badges11 bronze badges

Comments

tskuzzy · Accepted Answer · 2011-06-23 17:20:39Z

0

If you want to do this on the fly, look into usort

Whether or not you want to store this "score" in the database depends on your application's requirements. It's basically a tradeoff between storage space and speed. If you store it in the database, you lose storage space but if you compute it on the fly, you lose speed.

Though in this particular case, this small storage space overhead isn't something to be worried about and the speedup more than justifies it.

Short answer: save the score beforehand in the database :)

answered Jun 23, 2011 at 17:20

tskuzzy

36.6k15 gold badges78 silver badges145 bronze badges

Collectives™ on Stack Overflow

Using PHP to order a MYSQL query result

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related