endouken Posted March 22, 2011 Share Posted March 22, 2011 Hi there everyone, I have the below code (needs to be sanitized for SQL injection - i know ). Submitting to a MYSQL database. I wish to check on form submit if the users email address already exists, and if so display a simple error message (even just a windows error message) stating "the email address you have entered already exists". I don't really know where to start with this, or what the code should look like, so any help and direction would be massively appreciated. All i do know, is the email column is set to unique, and when i attempt to submit using an email i know exists the code appears to run successfully without spitting out any errors (i.e. the web url changes to the below php code) but the table doesn't update (which is correct). I just don't know how to then return the user to the form (preferably with all their info still entered) when this happens along with a nice error message... Kind regards, Tom. <? $localhost="00.000.000.00"; $username="###"; $password="###"; $database="mfirst"; $firstname=$_POST['firstname']; $surname=$_POST['surname']; $dob="{$_POST['dobyear']}-{$_POST['dobmonth']}-{$_POST['dobday']}"; if (isset ($_POST['permissionnewsletter']) || (!empty ($_POST['permissionnewsletter']))) { $permissionnewsletter = "Yes"; } else { $permissionnewsletter = "No"; } $email=$_POST['email']; $userpassword=$_POST['userpassword']; $telephone=$_POST['telephone']; mysql_connect($localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $insertintocustomerdetail = "INSERT INTO customerdetail VALUES (NULL,'$firstname','$surname','$dob','$permissionnewsletter','$email','$userpassword','$telephone',now())"; mysql_query($insertintocustomerdetail); $addressline1=$_POST['addressline1']; $addressline2=$_POST['addressline2']; $cityortown=$_POST['cityortown']; $county=$_POST['county']; $postcode=$_POST['postcode']=strtoupper(@$_REQUEST['postcode']); $insertintoaddresstable = "INSERT INTO addresstable VALUES (NULL,LAST_INSERT_ID(),'$addressline1','$addressline2','$cityortown','$county','$postcode',now())"; mysql_query($insertintoaddresstable); mysql_close(); ?> Quote Link to comment Share on other sites More sharing options...
Bl4ckMaj1k Posted March 22, 2011 Share Posted March 22, 2011 I get what you are asking but honestly I can't seem to figure out how I would add this to your code. Just know that this is how you would do it... First you would make a variable called whatever (here I called it $sql_email_check). What this does is checks to see if the variable you typed in for email addess (usually something like $email = $_POST['email'] has a match in the table. That line would look like this: $sql_email_check = mysql_query("SELECT email FROM myMembers WHERE email='$emailCHecker'"); From here you have to track that variable in a way that can return a value for checking purposes. I usually do that like this: $email_check = mysql_num_rows($sql_email_check); Its ok for these 2 lines to be one after the other. Then somewhere in the code you need to set an condition stating if there was a match found then don't move forward. I usually do that like this: if ($email_check > 0){ $errorMsg = "<b>ERROR:</b><br />Your Email address is already in use. Please try again<br />"; exit(); This will check each time an email is entered into your system. If all is good and there are no matches, then the variable $email_check will not be greater than 0 thus completely skipping over this condition. If its greater than zero, your user will be forced out of the system and receive this error message. I would also suggest adding an echo of your $errorMsg variable to your if condition. Within the message you should give the user a link which returns him/her to the last page they were on. I hope this helps!! Bl4ck Maj1k Quote Link to comment Share on other sites More sharing options...
endouken Posted March 22, 2011 Author Share Posted March 22, 2011 Thanks for the suggestion. I've done some more looking at this, and i'm thinking something along the lines of the below code might work, but i don't know how to tweak it or how to add it to my code above...any thoughts from the community? Thanks, Tom. PHP code: if (isset($_POST['Submit'])){ $email = mysql_real_escape_string($_POST['email']); $results = mysql_query("select id from users where user_email='$email' "); $row = mysql_num_rows($results); if ($row > 0 ) { //if $row is greater than 0, (means the email exists) echo "Error: email already exists"; } else { if ($row == 0 ) { // $row is equal to 0, (==), this means it didnt find results (email) //echo "Email does not exists, so lets add the email to the database"; echo "email has been added to the database"; } } Quote Link to comment Share on other sites More sharing options...
possien Posted March 23, 2011 Share Posted March 23, 2011 First query your email list... $query = "SELECT * FROM table_whatever WHERE email = '$email'; $data = mysqli_query($dbc, $query); if (mysqli_num_rows($data) == 1) { ....tell user the email exists try again..... else ... allow code... Quote Link to comment Share on other sites More sharing options...
endouken Posted March 23, 2011 Author Share Posted March 23, 2011 I've almost managed to get this working... - Can anyone help me understand why i am getting the error message "Query was empty". I understand what it means, but i have no knowledge on why/how to correct the code. - I then need to redirect back to the form rather than this error page if unsuccessful, or continue to stage 2/next page if successful. Any help would be (as always) be massively appreciated. code so far: <? $localhost="00.000.000.00"; $username="###"; $password="###"; $database="mfirst"; mysql_connect($localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); mysql_query($dupemail); $dupemail = "SELECT Email FROM customerdetail WHERE Email = '".$email."'"; $result = mysql_query($dupemail); if (!$result) { die(mysql_errno()." : ".mysql_error()); } if (@mysql_query($sql) && mysql_num_rows($result) == 0) { echo("<p>Thank you for joining the eClub!</p>"); } else { echo("<p><b>Error:</b> " . mysql_error() . "</p>" . "<p>Please <a href=\"mailto:webmaster@ourcompany.com\">contact us with this error</a> if you continue to have problems.</p>"); } $firstname=$_POST['firstname']; $surname=$_POST['surname']; $dob="{$_POST['dobyear']}-{$_POST['dobmonth']}-{$_POST['dobday']}"; if (isset ($_POST['permissionnewsletter']) || (!empty ($_POST['permissionnewsletter']))) { $permissionnewsletter = "Yes"; } else { $permissionnewsletter = "No"; } $email=$_POST['email']; $userpassword=$_POST['userpassword']; $telephone=$_POST['telephone']; mysql_connect($localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $insertintocustomerdetail = "INSERT INTO customerdetail VALUES (NULL,'$firstname','$surname','$dob','$permissionnewsletter','$email','$userpassword','$telephone',now())"; mysql_query($insertintocustomerdetail); $addressline1=$_POST['addressline1']; $addressline2=$_POST['addressline2']; $cityortown=$_POST['cityortown']; $county=$_POST['county']; $postcode=$_POST['postcode']=strtoupper(@$_REQUEST['postcode']); $insertintoaddresstable = "INSERT INTO addresstable VALUES (NULL,LAST_INSERT_ID(),'$addressline1','$addressline2','$cityortown','$county','$postcode',now())"; mysql_query($insertintoaddresstable); mysql_close(); ?> Quote Link to comment Share on other sites More sharing options...
Bl4ckMaj1k Posted March 23, 2011 Share Posted March 23, 2011 Well I have an idea of why your query may be returning an error message. mysql_query($dupemail); $dupemail = "SELECT Email FROM customerdetail WHERE Email = '".$email."'"; Shouldn't that be flip-flopped?? When your variable $dupemail is being read at the top, its a null variable. There is no data passed into it until the line beneath. Just try the following: $dupemail = "SELECT Email FROM customerdetail WHERE Email = '".$email."'"; mysql_query($dupemail); Hope this helps. It was the first issue I noticed. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 23, 2011 Author Share Posted March 23, 2011 Hey thanks buddy. Your suggestion gave me an idea, all i needed to do was the below code - now inserts when no duplicate exists and displays an error message when a duplicate does exist. $emailCheck = mysql_query("SELECT email FROM customerdetail WHERE email = '".$email."'"); if (mysql_num_rows($emailCheck) > 0) { die("EMAIL Already exists"); } Only question remaining, for anyone that can help, is how do i change the format of this error message to redirect back to the page with the form on and display a windows error message or something similar? The code that needs to be changed is below, but where can i find info on different 'die functions' (or whatever the correct terminology i should be using is!). die("EMAIL Already exists"); Thanks! Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 23, 2011 Share Posted March 23, 2011 if (mysql_num_rows($emailCheck) > 0) { /* header redirect here PROVIDED NOTHING was output to browser prior to here */ exit(); Quote Link to comment Share on other sites More sharing options...
endouken Posted March 23, 2011 Author Share Posted March 23, 2011 if (mysql_num_rows($emailCheck) > 0) { /* header redirect here PROVIDED NOTHING was output to browser prior to here */ exit(); Thanks. It almost worked. In a nut shell, what needs to happen from the users point of view is (a) to be automatically redirected back to the form along with (b) an error message (in whatever format) to allow them to try and reenter their email address again in to the form, rather than (what I've got currently happening) outputting a php error message on to an otherwise blank webpage. © Obviously if the email address is unique, they are not directed back to the form (stage 1), but to another page (stage 2). Unfortunately, the code you kindly offered does not meet (a) and (b) criteria above, and i'm not sure if it would exclude a second header ('location'); for criteria ©. This is a common website function, so i'm sure someone here has the knowledge/experience of making it work? Thanks to everyone so far, i'm sure we'll figure this out together! Tom. Quote Link to comment Share on other sites More sharing options...
endouken Posted March 23, 2011 Author Share Posted March 23, 2011 Ok, almost got this working....here's the code that displays a nice java pop up window alerting the email address already exists: $emailCheck = mysql_query("SELECT email FROM customerdetail WHERE email = '".$email."'"); if (mysql_num_rows($emailCheck) > 0) { echo '<script type="text/javascript">alert("Email Address Already Exists ' . $to . '");</script>'; } How do i terminate the script from running any more...i.e. stop inserting any values and don't navigate to www.mysite.com/insert.php so close i can taste it Quote Link to comment Share on other sites More sharing options...
Bl4ckMaj1k Posted March 23, 2011 Share Posted March 23, 2011 exit (); ?? Quote Link to comment Share on other sites More sharing options...
endouken Posted March 23, 2011 Author Share Posted March 23, 2011 exit (); ?? Heya, Yer - one of the first things i tried was: exit('<script type="text/javascript">alert("Email Address Already Exists - Please Try A Different One");</script>'); But for some reason the webpage then still goes to www.mysite.com/insert.php - which is a completely blank page due to it being server side/php - if it just stayed on the page without navigating away after displaying that error message i'd be home and dry Quote Link to comment Share on other sites More sharing options...
endouken Posted March 24, 2011 Author Share Posted March 24, 2011 anyone? :'( Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 24, 2011 Share Posted March 24, 2011 psuedo 1. start session on both the form page and the process pages 2. on form page check to see if session variable, lets call it bademail,is set use a conditional statement (ie IF) to display error message at of form 3. on process page IF bad email (A) set session variable bademail and redirect via header back to form The above will work Quote Link to comment Share on other sites More sharing options...
Bl4ckMaj1k Posted March 24, 2011 Share Posted March 24, 2011 I don't believe he has a true issue with process. He understands how this has to work. He also has a process in mind. He just needs to see the syntax in his own code to make it work. Saying this in a pseudo fashion, however correct and straight forward, unfortunately won't quite do the trick. If this was my code, I would write it out from scratch. Starting with the highest level of course and working my way down. If you code one step at a time, instead of implementing a function mid-stream, you may find it easier to work with. I truly hope you figure this thing out eventually. I am almost certain if you take a step away from it, code something similar with a lot less chaos from scratch with your objective in mind, you will fully understand how to implement that same thing into this script. Good luck mate!!, Bl4ck Maj1k Quote Link to comment Share on other sites More sharing options...
endouken Posted March 24, 2011 Author Share Posted March 24, 2011 Thanks Bl4ckMaj1k. Even without calling a java alert box, the key question is why, after performing the check and confirming the email is a duplicate does the script not end...why does it continue to process the rest of the code and ignore the exit(); command? :'( $emailCheck = mysql_query("SELECT email FROM customerdetail WHERE email = '".$email."'"); if (mysql_num_rows($emailCheck) > 0) { exit('<script type="text/javascript">alert("Email Address Already Exists - Please Try A Different One");</script>'); } Quote Link to comment Share on other sites More sharing options...
endouken Posted March 24, 2011 Author Share Posted March 24, 2011 SOLVED. Special thanks to Bl4ckMaj1k for support and just being there to bounce ideas with me - it helps and is sure better than sitting staring at a computer screen talking to myself drinking bad tasting coffee Problem wasn't calling java within php per-se, it was not ending/opening php tags before/after the java. The order of my code was not an issue like litebearer suggested - but thanks for the suggestion. <?php //Rest of my code bla bla bla // Right before the insert statement for customerdetail (which includes their email): $emailCheck = mysql_query("SELECT email FROM customerdetail WHERE email = '".$email."'"); if (mysql_num_rows($emailCheck) > 0) { ?><script type="text/javascript"> alert("The email address <?php echo $_POST['email']; ?> is already registered - Please Try A Different One."); history.back(); </script><?php } /* must include this '}' */ // Insert statement below and rest of code ?> Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 24, 2011 Share Posted March 24, 2011 Glad you got it working (What happens if user has javascripting disabled?) Quote Link to comment Share on other sites More sharing options...
endouken Posted March 24, 2011 Author Share Posted March 24, 2011 Glad you got it working (What happens if user has javascripting disabled?) I tried in IE with java off and it and it worked fine. I think it still works because the java is being called server side, so regardless of client java on/off it still calls the alert box. Does that sound right? Quote Link to comment Share on other sites More sharing options...
litebearer Posted March 24, 2011 Share Posted March 24, 2011 Not really, java script is totally client side Quote Link to comment Share on other sites More sharing options...
endouken Posted March 24, 2011 Author Share Posted March 24, 2011 Not really, java script is totally client side Then i'm not sure why it works, but it does. A lot like my car.... I'm sure there's a reason this works, but maybe neither of us know why...i definitely have java off in my IE browser tho. Quote Link to comment Share on other sites More sharing options...
Bl4ckMaj1k Posted March 24, 2011 Share Posted March 24, 2011 Awesome brother!!! Glad you figured this out. You just gave me a great idea by the way for my own project. I love Java!!!!!!!! :-) Bl4ck Maj1k 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.