Which SQL query should I use?

Question

I want to get the number of rows where the "value" field is equal to 1, and output its number and all the content_id where value_type is option in table votingapi_vote. the following is my code, but it doesn't work.

$result = db_query("SELECT content_id,count(value=1) FROM {votingapi_vote} WHERE value_type = '%s'" , "option");

while ($obj = db_fetch_object ($result)) {
  return $obj->content_id.'<br>'.'$obj->count';
}

it showed an error. when i delete the filed value in the query. it only output one result of $obj->content_id/ why? i want to output all the results of $obj->content_id

ps: now, if i want to get this result.

1,get the content_id and descending it according to the value(the count of each content_id 's value=1 substract the count of each content_id 's value=0 ,each content_id has many value=0 or value=1)

how to write the query.

Answer 1

First, that condition syntax is just wrong, you need to add that as a WHERE condition. Second, if you want the count per content id, you need to group by that.

And third, you need to collect the output in a variable instead of returning it directly.

Something like this:

$result = db_query("SELECT content_id, COUNT(*) AS count FROM {votingapi_vote} WHERE value_type = '%s'"  AND value = 1 GROUP BY content_id, "option");

$output = '';
while ($obj = db_fetch_object ($result)) {
  $output .= $obj->content_id.'<br>' . $obj->count;
}
return $output;