php/mysql question, counting number of * results if something = something

[mkx]

Let's say I have a mysql table called john with several duplicate 'status' columns and I run the query:

$res2 = mysql_query("SELECT * FROM john WHERE status='1'");
$row2 = mysql_fetch_assoc($res2);


How do I declare how many results there are with this IP? For example if the table had 11 entries with the status=1, how do I declare $total_status = 11 (11 will be changed to the code I need to calculate this)

Hope this isn't to confusing, if so let me know and I will post a better example. I basically want to declare how many columns there are with the status of 1.

[quantum-x]

SELECT COUNT(*) FROM `john` WHERE `status` = 1

It's always a good idea to backtick fields. Also, don't put INT fields in quotes - it will slow your query down by up to 60%.

Note, this method of counting rows has fundamental restrictions. If you have 100 items, and use LIMIT - count will only return the number of LIMIT'd rows.

If you want to always have a count of ALL rows that matched the query:
SELECT SQL_CALC_FOUND_ROWS * FROM `john` WHERE `status` = 1

Pulling the resulting count out will require another query, but your DB abstractor should take care of it

[mkx]

thx, ill try that out

[mkx]

so what would i call it?

$statusonecount = ???;

[quantum-x]

Please read the PHP / mysql manual.

<?php
$sql = "SELECT COUNT(*) as `count` FROM `john` WHERE `status` = 1";
$result = mysql_query($sql);

list($count) = mysql_fetch_row($result);

echo $count;
?>

[mkx]

thx, i love u