Finding total number of rows in a table

We can get the number of rows or records present in a table by using mysql_num_rows() function. This function is to be used along with mysql select query. We can add condition by using mysql where clause to the select query and get the conditional rows. This function is widely used in different php scripts and you can see the use of this function in PHP paging tutorial in our php tutorial section.

Please note that this can't be used along with update, delete, insert commands, for this mysql_affected_rows is to be used. You can use count command also to read the number of records. Here is the sample code for mysql_num_rows()
Related Tutorial
Counting row by PDO
SQL Count

$query = mysql_query("SELECT * FROM student");
$number=mysql_num_rows($query);
echo "Total records in Student table= ". $number;

Download sql dump of this student table

Number of User Comments : 13


Google+

More on PHP MySQL functions
Tim

05-03-2009

The result offered in the main article requires transferring all of the data from the table across the wire to PHP. A much less intensive approach to getting the number of records in a table is: $query = mysql_query("SELECT COUNT(*) FROM student"); list($number) = mysql_fetch_row($query); echo "Total records in Student table=$number";
Marcus

08-04-2009

Also, to be less intensive all you need to do is select a single column with the least amount of information such as the primary key column: $query = mysql_query("SELECT key FROM student"); $number = mysql_num_row($query); echo "Total records in Student table=".strval($number)
JSC

06-05-2009

Looks like the ultimate efficient count would be "SELECT COUNT(key) FROM student" :)
MSQL

11-05-2009

prefer COUNT(key) AS Total
arnab

07-07-2009

very useful site.
konrad

07-09-2009

To Marcus: Little mistake - should be: mysql_num_rows not mysql_num_row
webmaster

17-04-2010

I used Tim's example. Very helpful. I took me forever to find a decent example.
Shreeram N Venkatesh

04-08-2010

how could we see the table contents from a database using mysql in the dos prompt?
lovely

21-03-2011

how to use the fetch_array in select statement?
DingDong

16-06-2011

Also, COUNT(key) is not the ultimate efficient count. That would be COUNT(*). The first count will require a table scan, but the second will not since it is taken from an already existing cache on the number of rows.
Akhlas Ahmad

17-01-2014

Thanks. Such useful site. Bookmarked it.
npinelo

14-03-2015

When I use $query = mysql_query("SELECT * FROM books WERE ves=es"); I get "mysql_num_rows() is not valid MySQL result resource", how can I fix this?
smo

15-03-2015

Change the query part like this
"Select * FROM books WHERE ves='es' "

Post Comment This is for short comments only. Use the forum for more discussions.






OPINION POLL

What is the most important factor of a web site

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer