MySQL query not returning expected result

[HDFilmMaker2112]

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.

[cpd]

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.

[HDFilmMaker2112]

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?

[cpd]

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?

[HDFilmMaker2112]

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.

[cpd]

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.