check if user exists before feeding DB

[jmcc]

Hi

 

I have a simple registration form that feeds the db.

 

How can I check if the username(email) already exists in the user table before inserting it again.

 

I am currently look if there are records in the recordset, how do I check for a specific email address. The email address entered in my form?

 

Here is what I have so far:

 

// Error Function

function Trigger_CheckEmail(&$tNG) {

$myThrowError = new tNG_ThrowError($tNG);

$myThrowError->setErrorMsg("Your email already exists on our system. You can use the same email and passowrd to login as a Seller, Buyer or Agent");

$myThrowError->setField("email");

$myThrowError->setFieldErrorMsg("Your email already exists on our system. You can use the same email and passowrd to login as a Seller, Buyer or Agent");

return $myThrowError->Execute();

 

 

// Create recordset

mysql_select_db($database_prop, $prop);

$query_users = "SELECT email FROM mymembers";

$users = mysql_query($query_users, $prop) or die(mysql_error());

$row_users = mysql_fetch_assoc($users);

$totalRows_users = mysql_num_rows($users);

 

 

 

// Register triggers

$ins_mymembers->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Insert1");

$ins_mymembers->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);

$ins_mymembers->registerTrigger("END", "Trigger_Default_Redirect", 99, "login_register_buy_agent.php?email={email}&pass={ password}&type={type}");

$ins_mymembers->registerConditionalTrigger("{POST.password} != {POST.re_password}", "BEFORE", "Trigger_CheckPasswords", 50);

$ins_mymembers->registerConditionalTrigger("$totalRows_users > 0", "BEFORE", "Trigger_CheckEmail", 50);

$ins_mymembers->registerTrigger("AFTER", "Trigger_SendEmail", 9;

[ToonMariner]

2 methods.

 

1 query the database for the username - if its not taken do the insert.

 

2 (preferred method) set the username field to be unique try the insert if the query fails check the error - it should say filed must be unique.

 

the benefit of the latter is only one query is run.

[jmcc]

How would I go about doing option 2?

 

 

[Danny620]

// Make sure the email address is available:
	$q = "SELECT username FROM test WHERE username='$un'";
	$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

	if (mysqli_num_rows($r) == 0) { // Available.
	}else{ die("username already taken"); }

	// Make sure the email address is available:
	$q = "SELECT id FROM test WHERE email='$e'";
	$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

	if (mysqli_num_rows($r) == 0) { // Available.

		// Add the user to the database:
		$q = "INSERT INTO test (email, password, username, lastupdate) VALUES ('$e', SHA1('$p'), '$un', '$time_now')";
		$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

[jmcc]

Can I do it on the same page without posting the form data?

 

 

can I run a SQL query from a field in my form?

[Danny620]

like so

 

1. enters the info like email

2. checks the db to see if email exists

3. if not continue with the code else die and throw error message

[jmcc]

Thanks for the help. I'm gonna leave the post open for the night.

 

 

[ToonMariner]

@danny - the method does not require 2 queries to complete the task. The database can report back on any error all by itself if you have the correct structure.

 

set the filed in the database so it must be unique.

 

ALTER TABLE  `mymembers` ADD UNIQUE (`email`)

 

 

in your code...

 

$query_users = "INSERT INTO `mymembers` (`email`, `password`) VALUES ('email', SHA1('password')";

$users = mysql_query($query_users) or die(mysql_error());

 

if you attempt to insert somet value already there it will kill the script.

 

If you want to catch that error then have a look at this page which tells you what that function returns and what you can do with it. (you could just echo out the error and remove the die so that the user could enter a different email address)

[jmcc]

Hi

 

Thank you that was quite easy.

 

Jay