PhP - MySQL query error?

Question

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,

Email Address:*

if ($emailvalidationcheck == "NOTOK"){
$flag = "NOTOK";
print ' Please enter a valid Email Address. ';
}
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 ' This email address is already in use. ';
}
else if($count==0){
$emailclass = "basictext";
$flag="OK";
}}

?>

Answer 1

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!!

Answer 2

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) . "'";

Answer 3

Make it greater convenient on your self. Use the underscore ('_') to chop up words as adversarial to a hyphen ('-'). occasion: $table = 'menu_pizza'; then you certainly do not might desire to apply the get away series "-". you will observe the opposite lessen is used to flee a definite character.