[php] How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL)

I need help checking if a row exists in the database. In my case, that row contains an email address. I am getting the result:

email no longer exists [email protected]

This is the code I'm currently using:

if (count($_POST)) {
    $email = $dbl->real_escape_string(trim(strip_tags($_POST['email'])));

    $query = "SELECT `email` FROM `tblUser` WHERE `email` = '$email'";
    $result = mysqli_query($dbl, $query);
    if (is_resource($result) && mysqli_num_rows($result) == 1) {
        $row = mysqli_fetch_assoc($result);
        echo $email . " email exists " .  $row["email"] . "\n";
    } else {
        echo "email no longer exists" . $email . "\n";
    }
}

Is there a better way to check if a row exists in MySQL (in my case, check if an email exists in MySQL)?

This question is related to php mysql mysqli pdo

The answer is


You have to execute your query and add single quote to $email in the query beacuse it's a string, and remove the is_resource($query) $query is a string, the $result will be the resource

$query = "SELECT `email` FROM `tblUser` WHERE `email` = '$email'";
$result = mysqli_query($link,$query); //$link is the connection

if(mysqli_num_rows($result) > 0 ){....}

UPDATE

Base in your edit just change:

if(is_resource($query) && mysqli_num_rows($query) > 0 ){
        $query = mysqli_fetch_assoc($query);
        echo $email . " email exists " .  $query["email"] . "\n";

By

if(is_resource($result) && mysqli_num_rows($result) == 1 ){
        $row = mysqli_fetch_assoc($result);
         echo $email . " email exists " .  $row["email"] . "\n";

and you will be fine

UPDATE 2

A better way should be have a Store Procedure that execute the following SQL passing the Email as Parameter

SELECT IF( EXISTS (
                  SELECT *
                  FROM `Table`
                  WHERE `email` = @Email)
          , 1, 0) as `Exist`

and retrieve the value in php

Pseudocodigo:

 $query = Call MYSQL_SP($EMAIL);
 $result = mysqli_query($conn,$query);
 $row = mysqli_fetch_array($result)
 $exist = ($row['Exist']==1)? 'the email exist' : 'the email doesnt exist';

After validation and before INSERT check if username already exists, using mysqli(procedural). This works:

//check if username already exists
       include 'phpscript/connect.php'; //connect to your database

       $sql = "SELECT username FROM users WHERE username = '$username'";
       $result = $conn->query($sql);

       if($result->num_rows > 0) {
           $usernameErr =  "username already taken"; //takes'em back to form
       } else { // go on to INSERT new record

There are multiple ways to check if a value exists in the database. Let me demonstrate how this can be done properly with PDO and mysqli.

PDO

PDO is the simpler option. To find out whether a value exists in the database you can use prepared statement and fetchColumn(). There is no need to fetch any data so we will only fetch 1 if the value exists.

<?php

// Connection code. 
$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new \PDO('mysql:host=localhost;port=3306;dbname=test;charset=utf8mb4', 'testuser', 'password', $options);

// Prepared statement
$stmt = $pdo->prepare('SELECT 1 FROM tblUser WHERE email=?');
$stmt->execute([$_POST['email']]);
$exists = $stmt->fetchColumn(); // either 1 or null

if ($exists) {
    echo 'Email exists in the database.';
} else {
    // email doesn't exist yet
}

For more examples see: How to check if email exists in the database?

MySQLi

As always mysqli is a little more cumbersome and more restricted, but we can follow a similar approach with prepared statement.

<?php

// Connection code
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli('localhost', 'testuser', 'password', 'test');
$mysqli->set_charset('utf8mb4');

// Prepared statement
$stmt = $mysqli->prepare('SELECT 1 FROM tblUser WHERE email=?');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();
$exists = (bool) $stmt->get_result()->fetch_row(); // Get the first row from result and cast to boolean

if ($exists) {
    echo 'Email exists in the database.';
} else {
    // email doesn't exist yet
}

Instead of casting the result row(which might not even exist) to boolean, you can also fetch COUNT(1) and read the first item from the first row using fetch_row()[0]

For more examples see: How to check whether a value exists in a database using mysqli prepared statements

Minor remarks

  • If someone suggests you to use mysqli_num_rows(), don't listen to them. This is a very bad approach and could lead to performance issues if misused.
  • Don't use real_escape_string(). This is not meant to be used as a protection against SQL injection. If you use prepared statements correctly you don't need to worry about any escaping.
  • If you want to check if a row exists in the database before you try to insert a new one, then it is better not to use this approach. It is better to create a unique key in the database and let it throw an exception if a duplicate value exists.

Examples related to php

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop?

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to mysqli

phpMyAdmin ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user 'pma'@'localhost' (using password: NO) mysqli_real_connect(): (HY000/2002): No such file or directory mysqli_connect(): (HY000/2002): No connection could be made because the target machine actively refused it Call to a member function fetch_assoc() on boolean in <path> How can I enable the MySQLi extension in PHP 7? Fatal error: Call to a member function bind_param() on boolean Warning: mysqli_select_db() expects exactly 2 parameters, 1 given in C:\ How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL) Object of class mysqli_result could not be converted to string in mysqli::query(): Couldn't fetch mysqli

Examples related to pdo

Pass PDO prepared statement to variables PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes PHP 7 RC3: How to install missing MySQL PDO PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it php artisan migrate throwing [PDO Exception] Could not find driver - Using Laravel How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL) PDOException SQLSTATE[HY000] [2002] No such file or directory How do I configure php to enable pdo and include mysqli on CentOS?