Page 1 of 1

Counting items in a database

Posted: Wed Jan 15, 2003 5:33 pm
by evilmonkey
Hi everyone.

I want to count the amout of items in my MySQL DB where a certain column has a value of 'yes'. I understand I need to use a while() command. How would I use it?

Thanks.

Posted: Wed Jan 15, 2003 6:21 pm
by skehoe
Check out this page : http://www.mysql.com/doc/en/Counting_rows.html

Hope that helps.

~Scott

Posted: Wed Jan 15, 2003 7:11 pm
by evilmonkey
That's great, except that's sql language. I need php. Can someone tell me how to make that into PHP.

Thanks.

Posted: Wed Jan 15, 2003 10:18 pm
by fractalvibes
Try something like:

Select Count(*) As MyCOunt
from Mytable
where Somefield = 'yes'

Let the DB do the work for you and return 1 row containing that one
field, MyCount.

Doing that with PHP or any other language would be a waste and extremely
inefficient!

Phil J.

Posted: Wed Jan 15, 2003 10:57 pm
by volka
only to make it look more like php (but the magic is still sql, you have to query mysql in sql anyway ;) )

Code: Select all

$query = 'SELECT Count(*) FROM Mytable';
$result = mysql_query($query, $dbConn) or die($query. ': '.mysql_error());
$numRows = array_pop(mysql_fetch_row($result));
if you want to query the rows anyway you might do so with

Code: Select all

$query = 'SELECT * FROM Mytable';
$result = mysql_query($query, $dbConn) or die($query. ': '.mysql_error());
$numRows = mysql_num_rows($result));
...
while($row = mysql_fetch_array($result))
{
	echo ...
}

Posted: Thu Jan 16, 2003 9:24 am
by fractalvibes
Avoid Select * from Mytable.

If you have a gazillion rows, you'll bring back every one of them - very inefficient.

At very least,Select * from Mytable where someColumn = 'yes'
to limit the recordset coming back.

Phil J.

Posted: Thu Jan 16, 2003 9:28 am
by laserlight
I think volka intended that example for counting all the rows in the table, in which such a condition wouldnt apply.

Posted: Thu Jan 16, 2003 11:57 am
by evilmonkey
Thanks, I'll try that when I get home. I think I like volka's way the best, except I will put select*from mytable where something='yes' because that's what I need.

Thanks everyone, I'll post if I have more questions. :D

Posted: Thu Jan 16, 2003 12:38 pm
by volka
I think volka intended that example for counting all the rows in the table, in which such a condition wouldnt apply.
for SELECT count(...) the use of * it doesn't matter (in fact it might be even a bit faster, negligible) but for SELECT * FROM ... it does matter even if you're only going to use mysql_num_rows(). At least since mysql_query() uses mysql_store_result by default to transfer the results instead of mysql_use_result
Naming the fields you want to select is almost always a good idea.

But yes, it was only an example and I didn't take too much care :D