Counting items in a database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Counting items in a database

Post 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.
User avatar
skehoe
Forum Commoner
Posts: 59
Joined: Sun Dec 22, 2002 5:57 am
Location: Denver

Post by skehoe »

Check out this page : http://www.mysql.com/doc/en/Counting_rows.html

Hope that helps.

~Scott
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

That's great, except that's sql language. I need php. Can someone tell me how to make that into PHP.

Thanks.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ...
}
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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.
laserlight
Forum Commoner
Posts: 28
Joined: Wed Jan 01, 2003 6:41 am

Post by laserlight »

I think volka intended that example for counting all the rows in the table, in which such a condition wouldnt apply.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
Post Reply