How do I get count(*) in MySQL

[Ross Nicholson]

When I access MySQL using
SELECT Count(*)
FROM TABLE
WHERE CONDITION

How do I get the count result from the query.
I've $row["count"] but it doesn't contain anything.

Also, I'm presuming that a count query is a lot quicker than a query that gets actual records, would I be right????

[JoelFord]

Open the connection, then:

mysql_select_db("yourdatabase",$db);
$result = mysql_query("select count(*) as ttl from yourtable",$db);
if ($myrow = mysql_fetch_array($result)) {
echo "<table border=0>\n";
do {
printf("<tr><td>Total Entrants: %s</td></tr>\n", $myrow["ttl"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>\n<P>";
} else {
echo "Sorry, no data was found!";
}

Or, if you want totals by a particular field, connect, then do this:

mysql_select_db("yourdatabase",$db);
$result = mysql_query("SELECT fieldyouwant, COUNT(*) as ttlbs FROM yourtable GROUP BY fieldyouwant",$db);
if ($myrow = mysql_fetch_array($result)) {
echo "Totals by Fieldyouwant:\n";
echo "<table border=0 cellpadding=2 cellspacing=1 width=70>";
do {
printf("<tr><td width=30 ALIGN=center>%s</td><td width=40 ALIGN=right>%s</td></tr>\n", $myrow["fieldyouwant"], $myrow["ttlbs"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "Sorry, no data was found!";
}

------------------
"Whatever can be said can be said clearly." -- Ludwig Wittgenstein

[Vinay]

I havent read JoelFord's response.. but, Ross, a quick fix

SELECT count(*) AS count FROM table WHERE ...

then you can do $row['count']

--------------------
SitePoint Moderator
( my ONLY host ) ( geekarea.org )

[Kevin Yank]

Yup, you're both right. :-)

For the record, this is covered in Part 9 ("Advanced SQL") of my series on SitePoint.com.


------------------
-Kevin Yank.
http://www.SitePoint.com/
Helping Small Business Grow Online!