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.

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
