email address already registered.

Dear Jason/Ray, Just as I thought the marathon was ending, more questions are raised. I have two here.

QUESTION 1
I've asked this question a while back, but I'm wondering has there been an update on setting it up since then.

I originally ask how the system could cope with a senario where a user registers with an email address, and the tries to register again with the same email. The email column in the mysql table is set to unique.

In the past I had to create a recordset - call it rsEmail and filter this recordset - 'email = form variable email', and then prepare a server behaviour where $totalRows_rsEmail was a number between -1 and 0.

This prevented the user from registering the email twice and avoided duplicate addresses. The problem for the user was that nothing seemed to happen on the page, when he entered a duplicate email - it just stayed on the same page with no message.

Then I recently spoke to a friend and he mentioned Ajax as a way of checking, but Jason said you didn't really cover that area.

I was studying the WebAssist Validation tools, and then I discovered a thing called 'Unique Database Value'. I've used it, along with a 'show if' message - 'email already exists' - and it seemed to work.

My question is, is this a secure or reliable way of checking for duplicate entries. What is the best way to check for duplicates and prevent their entry and give a message at the same time to the user?

QUESTION 2
As a follow on from the above, is there a way of dealing with the following situation. In my Registerion Form, the user (parent/team manager) registers, and he can add members to his family or team - first name, last name, email, phome, DoB, etc. The first name and last name are required, but the email and phone are not. However, the email is a unique field in the database table.

Because the email is not required, the parent/manager, does not have to complete the email field (the child may not have an email address). However, if another parent or team manager, registers another child who has no email address - the paremt/manager is prevent, because there is already a unique email in the system, even though it's a blank one.

Any solution to this senario?

1) Yes, the unique value validation was introduced to offer a way to check for duplicate values and should be used in place of creating the recordset and using numeric validation.

2) to allow blank values, you will need to change the server variable code that is generated. when you select the form element to be used for the unique validation, the server variable code that is generated will look like:

((isset($_POST["textfield"]))?$_POST["textfield"]:"")

you can edit that slightly to check for a blank value, if it is blank pass a fictitious value that will never exist like "123456789":

(((isset($_POST["textfield"]) && $_POST["textfield"] != ""))?$_POST["textfield"]:"123456789")

Jason, I'm not certain what I need to amend, because there is extra code added. To see what I mean, I have pasted the insert section from my register page (it's a test version). I have also attached the page:


<?php
// WA DataAssist Insert
if (($_SERVER["REQUEST_METHOD"] == "POST") && (isset($_SERVER["HTTP_REFERER"]) && strpos(urldecode($_SERVER["HTTP_REFERER"]), urldecode($_SERVER["SERVER_NAME"].$_SERVER["PHP_SELF"])) > 0) && isset($_POST)) // Trigger
{
$WA_connection = $CommSimpleRegister;
$WA_table = "test_table";
$WA_sessionName = "test_table_ID";
$WA_redirectURL = "login.php";
if (function_exists("rel2abs")) $WA_redirectURL = $WA_redirectURL?rel2abs($WA_redirectURL,dirname(__FILE__)):"";
$WA_keepQueryString = false;
$WA_fieldNamesStr = "f_name|l_name|email|phone|password";
$WA_fieldValuesStr = "".((isset($_POST["f_name"]))?$_POST["f_name"]:"") ."" . $WA_AB_Split . "".((isset($_POST["l_name"]))?$_POST["l_name"]:"") ."" . $WA_AB_Split . "".((isset($_POST["email"]))?$_POST["email"]:"") ."" . $WA_AB_Split . "".((isset($_POST["phone"]))?$_POST["phone"]:"") ."" . $WA_AB_Split . "".WA_MD5Encryption(((isset($_POST["password"]))?$_POST["password"]:"")) ."";
$WA_columnTypesStr = "',none,''|',none,''|',none,''|none,none,NULL|',none,''";
$WA_fieldNames = explode("|", $WA_fieldNamesStr);
$WA_fieldValues = explode($WA_AB_Split, $WA_fieldValuesStr);
$WA_columns = explode("|", $WA_columnTypesStr);
$WA_connectionDB = $database_CommSimpleRegister;
mysql_select_db($WA_connectionDB, $WA_connection);
@session_start();
$insertParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1);
$WA_Sql = "INSERT INTO `" . $WA_table . "` (" . $insertParamsObj->WA_tableValues . ") VALUES (" . $insertParamsObj->WA_dbValues . ")";

the code to edit is not in the insert behavior, it is in the unique value validation code:

change:

$WAFV_Errors .= WAValidateUnique("CommSimpleRegister",$CommSimpleRegister,$database_CommSimpleRegister,"test_table","email","',none,''","0","email","',none,''","".(((isset($_POST["email"]))?$_POST["email"]:""))  ."",true,3);

to:

$WAFV_Errors .= WAValidateUnique("CommSimpleRegister",$CommSimpleRegister,$database_CommSimpleRegister,"test_table","email","',none,''","0","email","',none,''","".(((isset($_POST["email"]) && $_POST["email"] != ""))?$_POST["email"]:"123456789")  ."",true,3);

Jason,

I've inserted the new code and it hasn't worked for me on this occasion - see what I did below:

I entered a new player and left his email blank.

I then entered another player and left his email blank - I then get the following message in my browser - Duplicate entry '' for key 'email'.

Below is my updated validation code:

<?php
if (($_SERVER["REQUEST_METHOD"] == "POST") && (isset($_SERVER["HTTP_REFERER"]) && strpos(urldecode($_SERVER["HTTP_REFERER"]), urldecode($_SERVER["SERVER_NAME"].$_SERVER["PHP_SELF"])) > 0) && isset($_POST)) {
$WAFV_Redirect = "";
$_SESSION['WAVT_register_184_Errors'] = "";
if ($WAFV_Redirect == "") {
$WAFV_Redirect = $_SERVER["PHP_SELF"];
}
$WAFV_Errors = "";
$WAFV_Errors .= WAValidateRQ(((isset($_POST["f_name"]))?$_POST["f_name"]:"") . "",false,1);
$WAFV_Errors .= WAValidateEM(((isset($_POST["email"]))?$_POST["email"]:"") . "",false,2);
$WAFV_Errors .= WAValidateUnique("CommSimpleRegister",$CommSimpleRegister,$database_CommSimpleRegister,"test_table","email","',none,''","0","email","',none,''","".(((isset($_POST["email"]) && $_POST["email"] != ""))?$_POST["email"]:"123456789") ."",true,3);

if ($WAFV_Errors != "") {
PostResult($WAFV_Redirect,$WAFV_Errors,"register_184");
}
}
?>

this means that you have set the email column in the database to not allow duplicate entries,

it is trying to add a second blank value, but since you have set the column to not allow duplicate entries, it wont allow the second blank value.

Okay Jason, Great, I got it to work, but only after removing 'unique' from the email field in the database table. The table continues to refuse duplicate emails (which is what I want), and will now allow me to enter blank emails (which is also what I want). I'm not clear on the logic behind the adjusted code you gave me, but it works and I think an explanation can be left for another day.

So just to recap, am I right in saying that you don't have to make the field in the mysql table 'unique' in order to prevent duplicate entries as long as you use the Web Assist server validation called 'unique database value'?

And would I also be correct in saying that, you, would make the field unique, if you 'required' a unique email from your users or is there any point at all in making a field unique in a mysql database?

Originally Said By: thehalpeen319342

  So just to recap, am I right in saying that you don't have to make the field in the mysql table 'unique' in order to prevent duplicate entries as long as you use the Web Assist server validation called 'unique database value'?  

Yes, that is correct. Since you want to allow blanks, those would create duplicates. So use the unique value validation to force unique email addresses, but allow for duplicate spaces.

Originally Said By: thehalpeen319342

  And would I also be correct in saying that, you, would make the field unique, if you 'required' a unique email from your users or is there any point at all in making a field unique in a mysql database?  

If you where going to force an email address to be required, you could make it unique in the database.

Great - one saga complete. Almost there - I hope.

I must say once again, that the support from WebAssist is fantastic and as I work through the product, it is very helpful.