jmcc Posted September 7, 2009 Share Posted September 7, 2009 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; Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted September 8, 2009 Share Posted September 8, 2009 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. Quote Link to comment Share on other sites More sharing options...
jmcc Posted September 8, 2009 Author Share Posted September 8, 2009 How would I go about doing option 2? Quote Link to comment Share on other sites More sharing options...
Danny620 Posted September 8, 2009 Share Posted September 8, 2009 // 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)); Quote Link to comment Share on other sites More sharing options...
jmcc Posted September 8, 2009 Author Share Posted September 8, 2009 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? Quote Link to comment Share on other sites More sharing options...
Danny620 Posted September 8, 2009 Share Posted September 8, 2009 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 Quote Link to comment Share on other sites More sharing options...
jmcc Posted September 8, 2009 Author Share Posted September 8, 2009 Thanks for the help. I'm gonna leave the post open for the night. Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted September 8, 2009 Share Posted September 8, 2009 @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) Quote Link to comment Share on other sites More sharing options...
jmcc Posted September 10, 2009 Author Share Posted September 10, 2009 Hi Thank you that was quite easy. Jay Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.