Ordering results by total vote count

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 />";
    /开发者_C百科/DISPLAYS BY TOTAL VOTES YET THE SOLUTION IS HORRID
}

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

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`

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