How to Find the Number of Rows in a MySQL Table Using PHP



PHP


In this article, using PHP, we will show you how you can find the number of rows which a MySQL table is comprised of.

This can be useful for not just finding the total number of rows in the entire table but can also be used to find the number of rows where a certain condition is specified, for example, finding the number of total rows where a person's last name is 'Smith' for a database of names, for instance.

This is also especially useful when we're looking up the availability of a user name when a new registrant is signing up to join something. We can check if there are any rows in the table where that user name exists. If it does exist, then we know it is unavailable. If it doesn't, then we know that the user name can be used.

To find the number of total rows of a MySQL table, we first connect to the database and then we include the following code:



The first line of code selects all the rows of all the columns from the table table_name and stores it in the variable $result. Then we use the PHP function mysql_num_rows() to find out how many rows are in the table. We store this result in the $count variable, which we then display with the echo function.

Below is a MySQL table representing different fruits:

Banana
$0.79
Mango
$1.99
Cantaloupe
$3.99
Honeydew
$4.99


To find the number of rows of this table, we will now call the PHP function which is the same as that shown above:

The number of rows in this table is 4

Since the above table has 4 rows, the mysql_num_rows function returns the value of 4.

How to Find the Number of Rows with a Specific Criterion

Besides finding the total number of rows in an entire table, we can also find the number of rows where a specific criterion is met. For example, find the number of rows containing the word 'Cantaloupe' is an example of a criterion we are looking for.

The general format to find the number of rows meeting a certain criterion is:



To find the number of rows in the table above which contains the word 'Cantaloupe' is:



Actual PHP Code

The number of rows in this table is 1

Because there is only 1 row which contains the word 'Cantaloupe', the mysql_num_rows function returns the value of 1.




Related Resources

How to Select a Specific Row of a MySQL Table Using PHP

How to Delete a Row of a MySQL Table Using PHP

How to Delete All Rows of a MySQL Table Using PHP




HTML Comment Box is loading comments...