CODEWITHSUNDEEP
phpsqlsql-order-by
Seth-77
Seth-77

Reputation: 254

ORDER BY $variable set AFTER query?

I have this code:

<?php 
$data = mysql_query("SELECT id FROM board") or die(mysql_error()); 
while($info = mysql_fetch_assoc( $data )) 
{ 
    Print " ".$info['id']." ";
    myOtherQuery($info['id']);
}

function myOtherQuery($id) {
    $result = mysql_query("SELECT COUNT(is_following_board_id) FROM follow WHERE is_following_board_id='$id'");
    $c = mysql_result($result, 0);
}
?>

It lists all ID's with a number beside it, defined as $c above in the second query.

For simplicity I have remove the HTML of the code but it aligns in a table.

I'm trying to ORDER BY $c, but don't know how to do it. Since it is defined AFTER the select query: $data = mysql_query("SELECT id FROM board")

It errors if I add: $data = mysql_query("SELECT id FROM board ORDER BY '$c'")

Is there anything I can add to the bottom of the code to make this order by work?

Upvotes: 0

Views: 116

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You want to do this in one query, by aggregating the results:

select is_following_board_id, sum(is_following_board_id) as cnt
from follow
group by is_following_board_id
order by cnt desc;

Your approach was to fetch the result and then fetch the count. Rather inefficient, because SQL is designed for this type of query.

Upvotes: 1

Related Questions