Ordering results by total vote count

Question

I'm building a voting system. There are two tables - one for votes, other for items being voted on. In this example, the items are threads.

First, I get the items.

Second, I get the votes for the items & count them.

Third, I'd like to display the items in an order based on the total counted votes.

$q = $db_conn->prepare('SELECT id, title FROM thread');
$q->execute();
$q->bind_result($threadid, $title);
$q->store_result();
while($q->fetch()){

    $q2 = $db_conn->prepare('SELECT value FROM vote WHERE item_id = ? AND item_type_id = 1');
    $q2->bind_param('i', $threadid);
    $q2->execute();
    $q2->bind_result($value);
    $totvalue = 0;
    while($q2->fetch()){
        $totvalue += $value;
    }?>

    <span style='color:grey;'>[<?php echo $totvalue; ?>]</span>

    <form class='thread' method='POST'>
        <input type='image' name='vote' value='up' src='media/img/uparrow.png' />
        <input type='image' name='vote' value='down' src='media/img/downarrow.png' />
        <input type='hidden' name='threadid' value='<?php echo $threadid; ?>' />
    </form>

    <?php echo $title . "<br />";
    //DISPLAYS BY ID
}

The only way to do it that I've found is to put the results in an array and sort it that way. But it makes no sense to put the whole table in an array when the site is to have hundereds of items.

$threads[] = array('threadid' => $threadid, 'title' => $title, 'totvalue' => $totvalue);

foreach ($threads as $key => $row) {
    $tid[$key]  = $row['threadid'];
    $title[$key] = $row['title'];
    $tval[$key] = $row['totvalue'];
} array_multisort($tval, SORT_DESC, $tid, SORT_DESC, $tval, SORT_DESC, $threads);

foreach ($threads as $t) { ?>

    <span style='color:grey;'>[<?php  echo $t['totvalue']; ?>]</span>

    <form class='thread' method='POST'>
        <input type='image' name='vote' value='up' src='media/img/uparrow.png' />
        <input type='image' name='vote' value='down' src='media/img/downarrow.png' />
        <input type='hidden' name='threadid' value='<?php echo $t['threadid']; ?>' />
    </form>

    <?php echo $t['title'] . "<br />";
    //DISPLAYS BY TOTAL VOTES YET THE SOLUTION IS HORRID
}

Is there a way to do it with MySQL? Or any other optimal solution?

David Nguyen · Answer 1 · 2011-05-31T17:20:24.637

2

Well for one thing you should join your query into one and save a query, you should read up on mysql documentation/tutorials as it can save you a lot of work on the back end.

SELECT thread.id, thread.title, count(vote.value) as votes 
FROM thread JOIN vote ON thread.id = vote.item_id 
WHERE vote.item_type_id = 1
GROUP BY thread.id
ORDER BY votes DESC`

edited May 31 '11 at 17:20

answered May 31 '11 at 17:08

David Nguyen

8,368
2
33
49

D'Arcy Rittich · Accepted Answer · 2011-05-31T17:11:30.170

1

This assumes there is one row per vote in the vote table:

select t.id, t.title, c.VoteCount
from thread t
inner join (
    select item_id, count(*) as VoteCount
    from vote
    where item_type_id = 1
    group by item_id
) c on t.id = c.item_id
order by c.VoteCount desc

If not, you can do this:

select t.id, t.title, v.Value as VoteCount
from thread t
inner join vote v on t.id = v.item_id
where v.item_type_id = 1
order by v.Value desc

edited May 31 '11 at 17:11

answered May 31 '11 at 17:06

D'Arcy Rittich

167,292
40
290
283

In my case it was sum(value) instead of count(*), but this worked like a charm. Thank you very much! – thekodols May 31 '11 at 17:46
@thekodols ah, didn't think of that possibility. – D'Arcy Rittich May 31 '11 at 18:05

Ordering results by total vote count

2 Answers2