By user2882684


2014-03-07 14:33:02 8 Comments

I need help checking if a row exists. I am getting "email no longer exists [email protected]".

Is there a better way to check if row exists with mysqli?

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

    $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";
    }   
}

3 comments

@Funk Forty Niner 2014-03-07 15:02:32

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) > 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) > 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->prepare($query)){

        $stmt->bind_param("s", $email);

        if($stmt->execute()){
            $stmt->store_result();

            $email_check= "";         
            $stmt->bind_result($email_check);
            $stmt->fetch();

            if ($stmt->num_rows == 1){

            echo "That Email already exists.";
            exit;

            }
        }
    }

Or a PDO method with a prepared statement:

<?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->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
     exit( $e->getMessage() );
}

// assuming a named submit button
if(isset($_POST['submit']))
    {

        try {
            $stmt = $conn->prepare('SELECT `email` FROM `tblUser` WHERE email = ?');
            $stmt->bindParam(1, $_POST['email']); 
            $stmt->execute();
            while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            }
        }
        catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
        }

    if($stmt->rowCount() > 0){
        echo "The record exists!";
    } else {
        echo "The record is non-existant.";
    }


    }
?>
  • 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: <input type = "text" name = "var"> - $_POST['var'] match. $_POST['Var'] no match.

  • POST arrays are case-sensitive.

Consult:

Error checking references:

Please note that MySQL APIs do not intermix, in case you may be visiting this Q&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:

If you are using the mysql_ API and have no choice to work with it, then consult the following Q&A on Stack:

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:

@S.Lukas 2015-10-20 06:47:01

Am I right in thinking the first 2 examples are NOT injection safe, and the latter 2 are? Thanks

@Funk Forty Niner 2015-10-20 11:02:35

@S.Lukas That is correct.

@Brad 2017-07-14 18:15:24

The methods here that are concatenating data into the query are broken and should not be used. Always use parameterized queries of some kind, or escaping at a minimum.

@Funk Forty Niner 2017-07-14 18:15:57

can the downvoter care to share why they downvoted my answer?

@Funk Forty Niner 2017-07-14 18:17:46

@Brad are you stuck up on that and your other comment. I'm guessing that's your downvote and you're looking for stuff to downvote in my answers. Why wasn't the other answers below downvoted? I should flag your behaviour for moderation.

@Funk Forty Niner 2017-07-14 18:19:03

@Brad "Always use parameterized queries of some kind, or escaping at a minimum" - Err.. isn't that in my answer? what are you looking for anyway?

@Brad 2017-07-14 18:32:29

Flag away... your code concatenating up top is broken and that's just reality. Your code below that is obviously fine. If you edit your post and remove broken code, I'd be happy to undo the downvote. And, thanks for calling attention to the other broken answers... I'll get to those later today.

@Funk Forty Niner 2017-07-20 14:04:09

@Brad "your code concatenating up top is broken" - you're going to have to be specific here, because I don't see where anything is "broken", you're just trolling me if anything. There is nothing wrong with my answer or any of the syntax used, please don't insult me. Edit: If you're talking about email='".$email."' that is valid syntax where you may think it should have been written as email='$email', I don't know.

@Funk Forty Niner 2017-07-20 14:30:42

@Brad FYI: I did update my answer but it wasn't in regards to what you may have been talking about in regards to concatenation for '".$var."' and '$var' if that's what you were talking about (and made a side note about that in the edit), I don't know since you never mentioned specifics. The update I did make, was in regards to my using mysqli_query() twice, to which that was fixed/edited. That is pretty much it. If that doesn't satisfy your concern, then you will need to type this all out for me, because I don't know what you were talking about exactly.

@Funk Forty Niner 2017-11-21 14:09:40

someone just downvoted my answer here and I'm sure it has to do with this one that I closed just now stackoverflow.com/q/47414744/1415724 - not very nice and not very classy if you ask me.

@robhoomph 2016-01-11 19:32:06

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

@Jason 2016-08-04 18:13:35

This answer should be selected!

@Brad 2017-07-14 18:33:54

Never concatenate data directly into a query... it create ambiguous queries which can lead to errors and security issues. Use prepared/parameterized queries to avoid this issue entirely. At a minimum, proper escaping must be used.

@Emilio Gort 2014-03-07 14:37:41

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

@user2882684 2014-03-07 14:43:53

See my update, I still get the fail message

@Brian Powell 2016-04-12 16:26:13

I'm so making a band called Pseudocodigo.

@Brad 2017-07-14 18:34:04

Never concatenate data directly into a query... it create ambiguous queries which can lead to errors and security issues. Use prepared/parameterized queries to avoid this issue entirely. At a minimum, proper escaping must be used.

@Emilio Gort 2017-07-14 18:56:30

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

@Brad 2017-07-14 18:57:43

@EmilioGort That depends on how you execute that stored procedure!

@Emilio Gort 2017-07-14 19:01:01

@Brad just a question: Is it MYSQL prepare to handle a parameter like @Email='[email protected]');DROP TABLE USERS; ' ??

Related Questions

Sponsored Content

36 Answered Questions

[SOLVED] How do I check if a string contains a specific word?

41 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

29 Answered Questions

[SOLVED] How to output MySQL query results in CSV format?

  • 2008-12-10 15:59:51
  • MCS
  • 1016164 View
  • 1018 Score
  • 29 Answer
  • Tags:   mysql csv quotes

10 Answered Questions

[SOLVED] Insert into a MySQL table or update if exists

15 Answered Questions

[SOLVED] How to get a list of MySQL user accounts

  • 2009-07-16 03:23:53
  • burntsugar
  • 1392602 View
  • 1277 Score
  • 15 Answer
  • Tags:   mysql mysql5

35 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

21 Answered Questions

[SOLVED] How do I connect to a MySQL Database in Python?

  • 2008-12-16 21:49:09
  • Marc Lincoln
  • 1085629 View
  • 1041 Score
  • 21 Answer
  • Tags:   python mysql

22 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

10 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

9 Answered Questions

[SOLVED] How to 'insert if not exists' in MySQL?

Sponsored Content