Checking for matching record in MySQL table

Record Exist by Query We can just check MySql table to verify whether a record exist or not. Here we are not interested in collecting the actual data from table. We are interested in getting a reply of TRUE or FALSE from our command. We will try for one line command using PHP.

We need such a query while working on a member signup script. Here when member enters a userid in the signup form we need to verify with our existing table to know if the userid is already used by some other member. Some time we will have different pages with access to different groups of members ( here the members have different level of access ). So when the member visit a page we will check with our table the member has required level of privilege or not.

This conditional checking is done by using if condition in PHP. Let us see the code for checking the userid exist inside a MySQL table or not.
if(mysql_num_rows(mysql_query("SELECT userid FROM plus_signup WHERE userid = '$userid'"))){
// Code inside if block if userid is already there
}
If we are using PDO class then we can use like this
$count=$dbo->prepare("select userid from plus_signup where userid=:userid");
$count->bindParam(":userid",$userid);
$count->execute();
$no=$count->rowCount();
if($no >0 ){
$msg=$msg."User Name already exists. Choose a different User Name";
$status = "NOTOK";
}
The above sql query will return TRUE or FALSE depending on the presence of userid in MySQL table.

Short code to find number of matching record

If you have already sanitized your variable and can use directly in the query then here is a quick way to find out how many matching records present in the table.
Here is the code using PHP PDO.
require "config.php"; // Database connection string

$nume = $dbo->query("select count(id) from  plus_signup where userid='$userid'")->fetchColumn();
echo "<br>Number of records : ". $nume;
Note that in this query id field is a unique field so we are counting records using only id field. If there is no such unique field is used then we can change the query to match the total record.
$nume = $dbo->query("select count(*) from  plus_signup where userid='$userid'")->fetchColumn();
echo "<br>Number of records : ". $nume;
Using MySQLi

Read more on MySQLI

$q="select * from plus_signup where userid=?";
if($stmt=$connection->prepare($q)){
$stmt->bind_param('s',$userid);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows >0 ){
	echo " Data exit " 
	}
}else{
 echo $connection->error;
}

Selecting records SQL to Limit Records
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Omar

    19-02-2010

    Thank you, That helped Me ..
    Vinayak

    06-03-2010

    Thanks, the code solved the bug in my program.
    Danoj

    23-03-2010

    Thank you very much for the code, its very important code to all programmers.
    Elise

    02-06-2010

    Wauw!! I was searching for this code for hours!! Thank you so much!!
    laan

    08-07-2010

    you rule that was exactly what i was looking for 3 second google search and blam on with the programming
    Sashko

    13-07-2010

    Awesome! Concise and accurate.
    Jan

    09-12-2010

    Thanks, I used to use the mysql COUNT function for this but this is much handier !
    Satish Verma

    03-02-2012

    Thanks dear wow very nice code very useful this code
    Cool

    14-06-2012

    Very very usefull script ... Thank you ...
    Doug

    26-06-2012

    TOTALLY USEFUL. Exactly what was needed.
    Scott

    21-08-2012

    Much more efficient than the count steps I was attempting. Thanks for the post.
    Alex

    15-11-2012

    YES YES YES. After hours of struggling I have found the missing key mysql_num_rows($query) THANKS THANKS THANKS
    Amanda

    25-01-2013

    Ha! Thank you, had the same problem as Alex.
    sidharam anache

    24-02-2013

    very excellent answer.
    Shane

    02-10-2014

    Spent HOURS trying to find this, finally got my problem solved!

    Can't thank you enough for posting this!

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer