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

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';

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   lt  php     Connection code    options          PDO  ATTR ERRMODE              gt   PDO  ERRMODE EXCEPTION       PDO  ATTR EMULATE PREPARES     gt  false      pdo   new  PDO  mysql host localhost port 3306 dbname test charset utf8mb4    testuser    password    options       Prepared statement  stmt    pdo- gt prepare  SELECT 1 FROM tblUser WHERE email       stmt- gt execute    POST  email       exists    stmt- gt 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   lt  php     Connection code mysqli report MYSQLI REPORT ERROR   MYSQLI REPORT STRICT    mysqli   new  mysqli  localhost    testuser    password    test     mysqli- gt set charset  utf8mb4        Prepared statement  stmt    mysqli- gt prepare  SELECT 1 FROM tblUser WHERE email       stmt- gt bind param  s     POST  email      stmt- gt execute     exists    bool   stmt- gt get result  - gt 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

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)

Examples related to php

Examples related to mysql

Examples related to mysqli

Examples related to pdo