Count(*)
I've never used "select Count(*)" and I don't know a lot about it. I want to count how many rows in table 'table' have an id of '$id' and a fieldname of 'checked'. Here is what I have.
$sql = "select Count(*) from table where(id=$id AND fieldname='checked')";
How do I display the result (as a number of course)?
Abhishek Reddy posted this at 04:00 — 21st December 2004.
He has: 3,348 posts
Joined: Jul 2001
You can use mysql_num_rows() to count the number of rows returned by a query.
CptAwesome posted this at 04:05 — 21st December 2004.
He has: 370 posts
Joined: Dec 2004
http://dev.mysql.com/doc/mysql/en/Counting_rows.html
airoid3000 posted this at 04:12 — 21st December 2004.
They have: 71 posts
Joined: Mar 2004
Abhishek Reddy.....you're awesome.
airoid3000 posted this at 05:40 — 21st December 2004.
They have: 71 posts
Joined: Mar 2004
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?
Busy posted this at 10:46 — 21st December 2004.
He has: 6,151 posts
Joined: May 2001
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 ... ?
timjpriebe posted this at 13:20 — 21st December 2004.
He has: 2,667 posts
Joined: Dec 2004
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?
Tim
http://www.tandswebdesign.com
airoid3000 posted this at 19:04 — 21st December 2004.
They have: 71 posts
Joined: Mar 2004
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?
Greg K posted this at 20:51 — 21st December 2004.
He has: 2,145 posts
Joined: Nov 2003
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 posted this at 00:16 — 22nd December 2004.
They have: 71 posts
Joined: Mar 2004
Thank you for your detailed help!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.