Jump to content

MySQL query not returning expected result


Recommended Posts

The below code is always placing a value of "1" into the $error array. I echoed out the $sanitized_email variable and displays the inputted email address fine.

 

I checked the database via PHPMyAdmin and the email address is not in the DB.

 

$check_email_DB = mysqliCOE('db_name');
$sanitized_email=mysqli_sanitize($check_email_DB, $register_email);
$result = $check_email_DB->query("SELECT COUNT(email_address) FROM user WHERE email_address='$sanitized_email'");
$number_rows = $result->fetch_assoc();
if($number_rows>=0){
$error[18]=1;
}
else{
$error[18]=0;
}
$check_email_DB->close();

 

I even ran the query manually in PHPMyAdmin and it returned zero results.

Link to comment
Share on other sites

Its because your using a COUNT function. You will always get a result set because if no rows are found matching your query, it will return a count of 0. This will in turn cause num_rows to return 1

 

I also noted you have a >= 0 in your if statement. So you want it to error if there are no rows as well as if there are rows? That doesn't really make sense.

Link to comment
Share on other sites

Its because your using a COUNT function. You will always get a result set because if no rows are found matching your query, it will return a count of 0. This will in turn cause num_rows to return 1

 

I also noted you have a >= 0 in your if statement. So you want it to error if there are no rows as well as if there are rows? That doesn't really make sense.

 

I thought it would literally return a number either 0 or the number of rows that has that email address. Guess I thought wrong. So what should I be doing here? Maybe I'm after $number_rows[0]? To get the first value entry in the array?

Link to comment
Share on other sites

If you want to test if an email address is in the database, remove the count function.

 

Also why does fetch_assoc return a number? Surely that should return a parsed row?

 

I'm not looking to return an email address. I'm looking to return the number of rows that has that email address in it. To see if it's equal to 0 or to 1 or more. I've always counted the amount of rows to check for a value.

 

Okay, if I don't count; what do I compare the result to?

 

Basically I need to check if the user entered email address is in the database, if not continue with registration; if it is, prevent registration, and send them back to the form with a error message.

Link to comment
Share on other sites

Yes I understand and everything I've said already leads you to what you want to do.

 

You have two methods

 

$stmt = "SELECT `email` FROM `table` WHERE `email` = '{$email}'"l
$res =  mysql_qyery($stmt);
if(mysql_num_rows($res) > 0){
     // Email exists
} else {
    // Email doesn't exist
}

 

Or

$stmt = "SELET COUNT(`email`) AS `count` FROM `table` WHERE `email` = '{$email}'";
$res = mysql_query($stmt);
$array = mysql_fetch_assoc($res);
if($array['count'] == 0){
    // Email doesn't exist
} else {
    // Email exists
}

 

If you use a COUNT function you will ALWAYS get a result set (provided the SQL statement is valid) therefore, num_rows will not determine if an email exists in the database or not.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.