airoid3000 — Wed, 22 Dec 2004 00:16:31 +0000

Thank you for your detailed help!

Greg K — Tue, 21 Dec 2004 20:51:00 +0000

timjpriebe is correct; when using COUNT() you will always get a row returned, becasue even if nothing matched, you will get a count of 0, which is returned as a row. This is why you still got a mysql_num_rows() of 1.

You can do one of the two methods.

$sql = "SELECT COUNT(*) AS numrows FROM table WHERE id=$id AND field='checked')";
$result = $db->query($sql);
$row = mysql_fetch_array($result);
$rowsMatching = $row['numrows'];

$sql = "SELECT id AS numrows FROM table WHERE id=$id AND field='checked')";
$result = $db->query($sql);
$rowsMatching = mysql_num_rows($result);

Either method, $rowsMatching will be set to the number of matching records.

Things to note:

The first method always returns just one piece fo data from the database to php, one record, with one field (the count).

The second method will return multiple rows to php, depending on how many match. Notice that I reduced how much info is returned by doing SELECT id instead of SELECT * which returns all data.

The first method is the best method of the two for reducing overhead between SQL server and PHP.

-Greg

airoid3000 — Tue, 21 Dec 2004 19:04:03 +0000

I'm not trying to display what was found so thats not a problem. So there is no way that I can make it return either 0 for no it wasn't found or 1 for yes it was found (because there is no possibility for there being more than one found)? If no, then is there another way I can pull off this affect?

timjpriebe — Tue, 21 Dec 2004 13:20:52 +0000

Simple. Your SQL query already returns the count. So it could be 0, 1, or any number.

Then, $field is assigned the number of records returned. Well, even if there were 0 records that were checked, the SQL query returned one record with the value 0. Count always returns one "record", and that record is actually just an int representing the number of records.

You want to use EITHER count (*) OR mysql_num_rows(), NOT both.

You can do a normal query and mysql_num_rows() will tell you the number of records. Or you can do count (*) and the value that is returned will be the number of records. No actual records will be returned at all using count (*).

Make sense?

Busy — Tue, 21 Dec 2004 10:46:03 +0000

you can also do Select count(*) as count from table ... or even count(id) as count

try put songle quotes around $id and is there a space between where and (id ... ?

airoid3000 — Tue, 21 Dec 2004 05:40:37 +0000

On the same subject, I have another problem.

I count table 'table' where id=$id and field='checked'.

	$sql = "select Count(*) from table where(id=$id AND field='checked')";
	$result = $db->query($sql);	
	$field = mysql_num_rows($result);

And for some reason, $field ALWAYS equals 1 (even when the field value is not 'checked')

Why would this happen?

airoid3000 — Tue, 21 Dec 2004 04:12:57 +0000

Abhishek Reddy.....you're awesome.

CptAwesome — Tue, 21 Dec 2004 04:05:30 +0000

http://dev.mysql.com/doc/mysql/en/Counting_rows.html

Abhishek Reddy — Tue, 21 Dec 2004 04:00:41 +0000

You can use mysql_num_rows() to count the number of rows returned by a query.