PhP - MySQL query error?

Hi,

Here is a snippet of code from a form I have. I am trying to validate an email address field, the validation all goes through just fine, as far as the regex expression I am using is concerned and if an unmatching or invalid email address is entered, the statment "Please enter a valid email address" is printed back.

However I am trying to test wether an email address is already in use in the database, and I think my MySQL query syntax is correct, at least I can't think of another way to put it. Can anyone set me straight on this?

Thanks,


<tr>
<td><div class="<?php print $emailclass; ?>">Email Address:*</div></td>
<td><input type="text" name="Email1" value="<?php print $email; ?>" size="30"/></td>
</tr>
<?php

if ($emailvalidationcheck == "NOTOK"){
$flag = "NOTOK";
print '<tr><td align="center" colspan="2" class="errorsubtext"> Please enter a valid Email Address. </td></tr>';
}
else if ($emailvalidationcheck == "OK"){
$emailquery = "SELECT * FROM $tbl_name WHERE customers_email_address='$email'";
$result = mysql_query($emailquery);
$count = mysql_num_rows($result);
if($count==1){
$emailclass = "errortext";
$flag="NOTOK";
print '<tr><td align="center" colspan="2" class="errorsubtext"> This email address is already in use. </td></tr>';
}
else if($count==0){
$emailclass = "basictext";
$flag="OK";
}}

?>

Anwered 2010-03-08 02:47:41 by just "JR"

mysql_num_rows is NOT a reliable call for what you are doing...
Re-write this way: (and beware of the syntax!)
$sql = "select count(*) from `".$tbl_name."` where `customer_email_address` = '" . $email . "'";
$list = mysql_query($sql);
$lst = mysql_fetch_array($list);
$count = $lst[0]; // The count you want!
mysql_free_result($list);
Query expanded to show details:
"select count ( * ) from ` " . $tbl_name . " ` where `customer_email_address` = ' " . $email . " ' " ;
Remember that Php does NOT parse variables enclosed in SINGLE quotes!!

Anwered 2010-03-08 02:19:34 by Jim

If there are multuple insertions in the database, the number is not going to be 1. if the field is UNIQUE, or you have applied a UNIQUE INDEX to that field, you should only get 1 max.

in either case, if you ever have to move databases, I suggest you change your code to if (count >= 1)

where is your mysql_connect and mysql_select_db?

you can work with a *single* database connection if you specify a $db_link in your query and elsewhere, gotten from mysql_connection.

also, people often forget to mysql_free_result($result) after they are finished with the query results. it frees up precious server memory, and it stops your server from messing up, especially if you are reusing variables!

also, you really should be using $emailquery = "SELECT * FROM $tbl_name WHERE customers_email_address='" . mysql_escape_string($email) . "'";