Check how many record in the MySQL database matches the query

Posted by Shek on June 30, 2008 under PHP | Be the First to Comment | Total View: 3,662 views

Editor’s note: In some cases, you might like to check how many records in the MySQL database which matches the query you entered. Case such as a login system where you want to check if there is a record matches the user name and the password inputted , here is an example

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:

1
2
3
4
5
6
7
8

<?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.

Not what you want? Try a Search

Tags: , , , , ,

Add A Comment

CAPTCHA Image
*