Sometimes, you might like to check how many records in the MySQL database matches the query you entered. This case happen often in a login system where you want to check if there is a record matches the user name and the password with what the user typed in, here is an example I have created to show you how to do so.

I have created a table in the MySQL database called “test” and with a field called “name” with 3 records “john”, “peter” and “peter”. I put 2 “peter” into the table intentionally because i want to show that when a query is called to check the number of row containing “peter”, it will show up “2″ on the screen.

I have created a php file with the following codes:

First we have to connect to the database:

mysql_connect("Host Name", "User Name", "User Password") or die("Connection Failed");
mysql_select_db("DataBase Name")or die("Connection Failed");

Then we create a query to select all records with “peter” in it from the table “test”:

$query = "SELECT * FROM test where name = 'peter'";

We then run the query and store the result in a variable:

$result = mysql_query($query);

Now we use the mysql_num_rows() function to check how many rows in the MySQL database matches the query and the result is stored in a PHP variable $count:

$count=mysql_num_rows($result);

At last we print out the result onto the screen to see if the whole thing works correctly:

echo $count;

Altogether we have:

<?php
mysql_connect("Host Name", "User Name", "User Password") or die("Connection Failed");
mysql_select_db("DataBase Name")or die("Connection Failed");
$query = "SELECT * FROM test where name = 'peter'";
$result = mysql_query($query);
$count=mysql_num_rows($result);
echo $count;
?>

We get “2″ in this case because there are 2 records in the MySQL database with “peter” in the “name” field.