current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

here is a little background on what I'm trying to accomplish. I have an array from a MySQL query that is being displayed. I want to sort the array based on a factor. The factor is calculated inline based on the time the article was posted & the number of votes it's received. Something like this:

// ... MySQL query here
$votes = $row['0']
$seconds = strtotime($record->news_time)+time();
$sum_total = pow($votes,2) / $seconds;

So the array thats coming in looks something like this:

Array (
    [0] => stdClass Object (
        [id] => 13
        [news_title] => Article
        [news_url] => http://website.com/article/14
        [news_root_domain] => website.com
        [news_category] => Business
        [news_submitter] => 2
        [news_time] => 2013-02-18 12:50:02
        [news_points] => 2
    )
    [1] => stdClass Object (
        [id] => 14
        [news_title] => Title
        [news_url] => http://www.website.com/article/2
        [news_root_domain] => www.website.com
        [news_category] => Technology
        [news_submitter] => 1
        [news_time] => 2012-10-02 10:03:22
        [news_points] => 8
    )
)

I want to sort the aforementioned array using the factor I mentioned above. The idea is to show the highest rated articles first on the list (using the calculated factor), instead of the default sorting method that the array comes in. It seems like usort might be my best bet, but let me know what you all think?

share|improve this question
1  
Why not sort on that factor using MySQL? Otherwise, what is $record? And where do "votes" come into play? I see no reference to that. –  Madbreaks Feb 22 '13 at 18:38
    
The factor isn't stored in MySQL. It's being dynamically generated based on time & votes. Votes are $rValue - renamed in the question so it's more clear. :) The votes are basically populated from the "news_points" value inside the array. $record is the for each statement that I'm running. –  viablepath Feb 22 '13 at 18:44
    
@viablepath: So how do you tally the news_points? is it a total of some sort, or is it jsut the value listed on the record? –  prodigitalson Feb 22 '13 at 18:55
    
It's the value in the record. I update the record everytime a vote is recorded. –  viablepath Feb 22 '13 at 19:05
    
I realize the factor isn't stored in MySQL, but you can certainly calculate it as part of your query and then sort on it. –  Madbreaks Feb 22 '13 at 21:46

1 Answer 1

up vote 1 down vote

Do it all in the query:

SELECT n.*, ( POW(?, 2) / (UNIX_TIMESTAMP(n.news_time) + UNIX_TIMESTAMP(NOW())) ) as rank 
FROM news_table n
ORDER BY rank;

Now in order to get the votes you may have to do a subquery or a join, but i cant advise on that because you dont give enough info on where the votes are coming from. You could however supply the votes to the query as well instead of selecting it all in one shot something like:

$sql = sprintf('SELECT n.*, ( POW(%d, 2) / (UNIX_TIMESTAMP(n.news_time) + UNIX_TIMESTAMP(NOW())) ) as rank FROM news_table n ORDER BY rank', $votes);

Aside from that, yes you could use usort, but that would also require you to have the entire recordset in memory to provide accurate sorting, which could be problematic at some point.

share|improve this answer
    
For some reason that is returning only 1 item from the array, with funky formatting. So to be clear, your saying I should run this query, then foreach through the items in order to display them properly? It seems like a good solution, I just need to get a functioning query. Thanks for your help! :) –  viablepath Feb 22 '13 at 19:26
    
Yes, thats what im saying. If you can post a dump of some of your data and your table definition i cant help fix the query. –  prodigitalson Feb 22 '13 at 19:33
    
Also if news_points is always the value of news_points on the record then you can just use POW(n.news_points, 2) –  prodigitalson Feb 22 '13 at 19:35
    
Array ( [0] => 8 [id] => 8 [1] => Article Title [news_title] => Article Title [2] => website.com/article/1 [news_url] => website.com/article/1 [3] => website.com [news_root_domain] => website.com [4] => [news_category] => [5] => 1 [news_submitter] => 1 [6] => 2013-02-18 10:40:41 [news_time] => 2013-02-18 10:40:41 [7] => 0 [news_points] => 0 [8] => 0 [rank] => 0 ) –  viablepath Feb 22 '13 at 19:42
    
Table info: i.imgur.com/3f5Ujos.png –  viablepath Feb 22 '13 at 19:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.