counting results where....

[bagus]

I have a sql that I execute...

$query="select name, number from table";
$result=mysql_query($query) or die("Error");

     I want to get a count of the results where number=0 without iterating thru the rows in a while statement. 

Can it be done?

[devinemke]

$result = mysql_query('SELECT COUNT(*) FROM table WHERE number = 0') or exit(mysql_error());

[bagus]

I should have specified, that I don't want to use a where statement in the sql.

I want to count the results where number=0 after the sql is executed without using a while loop.

[devinemke]

bagus wrote:

I should have specified, that I don't want to use a where statement in the sql.

I want to count the results where number=0 after the sql is executed without using a while loop.

why don't you want to use a while loop? the query will return a result resource. you have to loop thru it in order to test each row for a specific condition. you can either have PHP do this using a loop or have mySQL do it using the COUNT() function. either way each row must be looked at to test for the conditional. using the COUNT() query could be faster if proper indexing has been done on the table, but if you want all of the rows (even ones that don't meet the condition) then you should use a PHP loop.

[bagus]

devinemke wrote:

why don't you want to use a while loop?

Because I want to print the total before printing details of each individual record.

[devinemke]

bagus wrote:

Because I want to print the total before printing details of each individual record.

ok then, so what is the problem? get the total first, then print the details.

[bagus]

like I can use mysql_numrows() to count the total number of records returned by my select statement, but how can I get a conditional inside to get a number of records where in this case number=0.

//this is fine
echo ("total records returned: " . mysql_num_rows($result) . "<br>");
// but I want something like

     echo ("total number of records where number=0: " . mysql_num_rows(...where the value of the second field is 0...) .  "<br>");

[bagus]

I guess I could do it with two separate while statements... among other ways.

[bagus]

but really, isn't there a better way to do this with only one line of php?

[devinemke]

bagus wrote:

but really, isn't there a better way to do this with only one line of php?

no