How to check if a row exists in MySQL i e check if an email exists in MySQL

Question

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 publisher example com   This is the code I m currently using   if  count   POST          email    dbl- gt 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   amp  amp  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

User · Answer

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.

User · Answer

The following are tried  tested and proven methods to check if a row exists     Some of which I use myself  or have used in the past    Edit  I made an previous error in my syntax where I used mysqli query   twice  Please consult the revision s    I e    if   mysqli query  con  query   which should have simply read as if    query     I apologize for overlooking that mistake    Side note  Both     var    and   var  do the same thing  You can use either one  both are valid syntax   Here are the two edited queries    query   mysqli query  con   SELECT   FROM emails WHERE email     email            if    query                die  Error      mysqli error  con           if mysqli num rows  query   gt  0        echo  email already exists     else          do something      and in your case    query   mysqli query  dbl   SELECT   FROM  tblUser  WHERE email     email            if    query                die  Error      mysqli error  dbl           if mysqli num rows  query   gt  0        echo  email already exists     else          do something      You can also use mysqli  with a prepared statement method    query    SELECT  email  FROM  tblUser  WHERE email      if   stmt    dbl- gt prepare  query              stmt- gt bind param  s    email            if  stmt- gt execute                  stmt- gt store result                  email check                            stmt- gt bind result  email check                stmt- gt fetch                 if   stmt- gt num rows    1                echo  That Email already exists                exit                                   Or a PDO method with a prepared statement    lt  php  email     POST  email      mysql hostname    xxx    mysql username    xxx    mysql password    xxx    mysql dbname    xxx    try    conn  new PDO  mysql host  mysql hostname dbname  mysql dbname    mysql username   mysql password          conn- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION     catch  PDOException  e         exit   e- gt getMessage            assuming a named submit button if isset   POST  submit                    try                stmt    conn- gt prepare  SELECT  email  FROM  tblUser  WHERE email                     stmt- gt bindParam 1    POST  email                   stmt- gt execute                while  row    stmt- gt fetch PDO  FETCH ASSOC                                      catch PDOException  e                echo  ERROR       e- gt getMessage                   if  stmt- gt rowCount    gt  0           echo  The record exists          else           echo  The record is non-existant                    gt     Prepared statements are best to be used to help protect against an SQL injection      N B    When dealing with forms and POST arrays as used outlined above  make sure that the POST arrays contain values  that a POST method is used for the form and matching named attributes for the inputs    FYI  Forms default to a GET method if not explicity instructed    Note   lt input type    text  name    var  gt  -   POST  var   match    POST  Var   no match     POST arrays are case-sensitive    Consult     http   php net manual en tutorial forms php   Error checking references    http   php net manual en function error-reporting php http   php net manual en mysqli error php http   php net manual en pdo error-handling php   Please note that MySQL APIs do not intermix  in case you may be visiting this Q amp A and you re using mysql  to connect with  and querying with      You must use the same one from connecting to querying    Consult the following about this    Can I mix MySQL APIs in PHP    If you are using the mysql  API and have no choice to work with it  then consult the following Q amp A on Stack    MySql php  check if Row exists   The mysql   functions are deprecated and will be removed from future PHP releases     It s time to step into the 21st century      You can also add a UNIQUE constraint to  a  row s    References    http   dev mysql com doc refman 5 7 en constraint-primary-key html http   dev mysql com doc refman 5 7 en alter-table html How to check if a value already exists to avoid duplicates  How add unique key to existing table  with non uniques rows

User · Answer

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   gt  0           UPDATE  Base in your edit just change   if is resource  query   amp  amp  mysqli num rows  query   gt  0             query   mysqli fetch assoc  query           echo  email     email exists       query  email       n     By  if is resource  result   amp  amp  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

User · Answer

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- gt query  sql           if  result- gt num rows  gt  0                usernameErr     username already taken     takes em back to form          else      go on to INSERT new record

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

PDO

MySQLi

Minor remarks

Examples related to php

Examples related to mysql

Examples related to mysqli

Examples related to pdo