Counting rows in mysql database

[Anon]

What can I use to return the number of rows in a table in my mysql database. Thankyou!

[lawnmowerman]

You can use the MySQL built-in function count() like this:

SELECT count(*) as numrows FROM table;

This will return a field named "numrows" and fill it with the number of rows.

I believe it would be better to replace the * (which selects all fields) with a specific field, say, your primary key field to speed up the select.

I hope this helps 🙂

-Rich

[jc94062]

<?php
$db = mysql_connect("host", "username", "password");
mysql_select_db("dbname",$db);
$number = mysql_query("SELECT * from table",$db);
$foo = mysql_num_rows($number);
echo $foo;
?>

Displays the number of rows.

[lawnmowerman]

This is a good way to obtain the number of rows if you intend to use all of the data which you have obtained from the table.

But, if you only want to find the number of rows in a table, this is an incredible waste of resources. The database has to return all the rows, all of that data needs to be transmitted over the connection between the SQL server and the host running the script,
and the script must allocate enough memory to hold the data. At this point, you then query the number of rows in the returned data using mysql_num_rows().

It would be much more efficient for the SQL server, the network, and the php script to simply ask the SQL server how many rows are in the table in the first place using it's built-in count() function.

Again, this is, of course, if you're not intending to use all of that data which you asked for 🙂 If you ARE going to use all that data, this is a fine way to do it 🙂

-Rich