Adding a "Duplicate Entry" Error Message

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

jawinn
Forum Newbie
Posts: 13
Joined: Tue Oct 03, 2006 6:32 am

Adding a "Duplicate Entry" Error Message

Post by jawinn »

I have a web form that dumps form data into a DB. One of the fields is for email address. I would really like the form to kick back an error message if an email address is used that is already in the DB. My DB only has one table. What would the code be for that?

Thanks in advance,
J
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post by Mordred »

Go read a database tutorial or a db book. What you need is very basic, so even the simplest of tutorials would cover it. If something doesn't work, paste your code and then we'll help :)
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

You could try setting the e-mail as the primary key? May cause a few problems and I haven't tried it before. Just a hunch.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Code: Select all

SELECT name FROM table WHERE name = 'name'
Then with the php, basically just need to check that the query returned results... if so, echo "Name already taken";

I use Pear's HTMLQuickForms and MVC, but mine looks like this:

Code: Select all

if($form->isSubmitted()){
			$Event = new Event;
			// This is where you do any model-specific validation necessary (such as checking if name exists already)
			if($Event->nameExists()){
				$form->setElementError('name', 'Name already exists');
			}
			if($form->validate()){
				$Event->loadFromArray($form->getSubmitValues());
				if($Event->save()){
					echo "Event Saved!";
				}
				else{
					$this->view->messages[] = "Couldn't save event. Contact the administrator.";
				}
			}
		}
jawinn
Forum Newbie
Posts: 13
Joined: Tue Oct 03, 2006 6:32 am

Post by jawinn »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Here is what I'm using now:

Code: Select all

<?php
$handle = mysql_connect('server.com', 'test1002','password');
mysql_select_db('test1002') or die('Cannot select database');

if(isset($_POST['submit'])) {
        $form = array();
        if (ini_get('magic_quotes_gpc')) {
                $_POST = array_map('stripslashes', $_POST); 
        }
        foreach( array('fname', 'lname', 'email', 'city', 'state', 'answer') as $key) {
                $form[$key] = mysql_real_escape_string($_POST[$key], $handle);
        }

        $query = "INSERT INTO
                        errors
                        (fname,lname,email,city,state,answer)
                VALUES
                        ('{$form['fname']}', '{$form['lname']}', '{$form['email']}',
                         '{$form['city']}','{$form['state']}', '{$form['answer']}')";
        	$result = mysql_query($query, $handle);
	if ( false===$result ) {
        		echo 'error: ', mysql_error();
}
	
}
header ( 'Location: /thankyou.php' );
exit ();
?>
Again I would really like to prevent entries that have duplicate email addresses. I made the email column in MySQL unique. This fixes my problem but the user gets a cryptic error message. I'd like to do something like a header change if the email has already been used. Any thoughts?

Thanks in advance,
J


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

how about trying what i said? :?
jawinn
Forum Newbie
Posts: 13
Joined: Tue Oct 03, 2006 6:32 am

Post by jawinn »

The Ninja Space Goat wrote:how about trying what i said? :?
I want to I just don't know where it put it in my code. Apologies but I literally just finished reading my first PHP book.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

jawinn wrote:
The Ninja Space Goat wrote:how about trying what i said? :?
I want to I just don't know where it put it in my code. Apologies but I literally just finished reading my first PHP book.
OK that's understandable... well the example I provided was just to give you an idea of what you need to do... it's not a cut & paste snippet.

Are you technically able to make an SQL query and put the results into an array?
jawinn
Forum Newbie
Posts: 13
Joined: Tue Oct 03, 2006 6:32 am

Post by jawinn »

The Ninja Space Goat wrote: I want to I just don't know where it put it in my code. Apologies but I literally just finished reading my first PHP book.
OK that's understandable... well the example I provided was just to give you an idea of what you need to do... it's not a cut & paste snippet.

Are you technically able to make an SQL query and put the results into an array?[/quote]

:oops: No.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

alright, than this is a little above your head. Maybe try a php/mysql tutorial. Once you have a bit of code, post it here, and we'll help you get it right.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

I've thought of a very dirty way to do it if it helps using mysql_numrows.

Code: Select all

$query = mysql_query("SELECT * FROM aPlace WHERE email='$email'");
$numRows = mysql_numrows($query);

if ($numRows > 0) {
  echo "Think twice"; 
}

if ($numRows < 0) {
  echo "Yeeeehaw"; 
}
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

That second "if" is redundant... and you don't need to select all rows from the database... just select the id or something.

Code: Select all

$query = mysql_query("SELECT id FROM aPlace WHERE email='$email'");
$numRows = mysql_numrows($query);
if ($numRows > 0) {
  echo "Think twice"; 
}
else {
  echo "Yeeeehaw"; 
}
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

I wouldn't use the select/insert combo as you have to lock the table the whole time or you run into a race condition.
If there is a unique index for the email field mysql will not insert two records with identical values for the field.
The error number then returned by mysql_errno() is
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html wrote: Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)

Message: Duplicate entry '%s' for key %d
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

volka... in my case, I would need to know whether the name is available BEFORE the insert...
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

why?
And even if so, when you actually insert the data the check/insert operation has to be atomic. a select followed by an insert is not.
Post Reply