Avoiding duplicate database entries - UK Web Hosting | Dedicated Server Windows and Linux VPS Forum

**Pangloss** · #1 (**permalink**) 16-04-2008, 21:32

I have a MySQL 5 membership database which is populated via a form in the conventional way. I now wish to refuse any form submitted which contains a duplicate of an existing record and inform the user, preferably by way of another html page, that he is already in the database.

I have tried making the email address field a unique field and the result of this is a rather insignificant message on a blank page reading: "Duplicate entry 'member1@gmail.com' for key 2". If this was the thing to do I would like to make the message more informative and generally prettier.

Any suggestions guys?

**DavidAllen** · #2 (**permalink**) 16-04-2008, 22:25

When the user submits the form - first do a call to the database

PHP Code:


			
$sql=select * from TABLE_NAME where email="'.$_POST[email].'";
$Result=mysql_query($sql, $link_id);

Then see if any records are returned (use mysql_num_rows($Result) )- if none then you know the email is unique and you can go ahead and accept it. If a record is returned from the query, then you know that the email address has already been used and display the appropriate error message to the user

**Pangloss** · #3 (**permalink**) 17-04-2008, 15:55

David - thanks very much. I can see that this is the perfect way to do it but I am a real beginner and need a bit more help!! First off I am working in PHP so that the MySQL stuff looks rather different in PHP (to me anyway).

I have given it a go but without success even when I tried the PHP equivalent (well I thought it might be right!)

Would it be too much to ask if you could give it to me as the equivalent in PHP?? And (just one more request) could you tell me how to send the reader to an html page if there is a duplicate email address, so that I can display a home-made error message?

**DavidAllen** · #4 (**permalink**) 17-04-2008, 17:33

Pangloss -err... that was php !! Appologies if I was too brief in the explanation
Here is the full stuff you need

I connect to the database in a seperate function as below

PHP Code:


			
function db_connect() {



$server = "localhost";        // The mysql host

$database = "your_database_name";    // The Database name

$username = "your_user_name";            // database uid

$password = "your_password";                // database password - don't expect to memorise it!





    $link_id = @mysql_connect($server, $username, $password) or die("FATAL ERROR: Could not contact the database server!");

    @mysql_select_db($database) or die("FATAL ERORR: There was a problem with the database!");

    return $link_id;

}

then when I want to use a database connection all i do is

PHP Code:


			
                $link_id = db_connect();

then as in my original reply I just use that $link_id to run the query and test the result

PHP Code:


			
$sql=select * from TABLE_NAME where email="'.$_POST[email].'";

$Result=mysql_query($sql, $link_id);  

if (mysql_num_rows($Result)>0) {

     //display the html page with the error message

     header("Location:ErrorPage.html")

} else {

    //unique email address

    //so insert into the database

    $sql='Insert into TABLE_NAME set email="'.$_POST[email].'", otherfield="'.$_POST[otherfield].'"; //add whatever fields you need to enter

  $Result=mysql_query($sql, $link_id);

  header("Location:SuccessPage.html")

}

As for your other query - how to display html pages - you can see above with the 'location' statement how to redirect to a custom page. Or else you can combine both html and php in one page like the simple one below

PHP Code:


			
<html><head>

<meta http-equiv="Content-Language" content="en-gb"> 

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<title>My Page Title</title>

<link rel="shortcut icon" href="images/MISC32.ICO">

</head>

<body>



<div>

<?php 

for ($i=0;$i<10;$i++) {

   echo $i.'<br>';

}

?>

</div>

</body>

</html>

so you see the html is entered normally and the php is between <?php....?> tags

save the file as a .php file and it will display a page with a div with the numbers 1 to 9 in it

Hope all this makes sense

David

**Pangloss** · #5 (**permalink**) 18-04-2008, 11:40

Having serious difficulties getting this thing to work!! One of the troubles may be that I wrote the original form using Dreamweaver 8 whose (automatic) code looks a bit different to yours - this is why I did not recognise it as PHP (many apologies)! The whole form has worked very reliably so far if it was not for these duplicate entries.

This page starts with :

<?php require_once('Connections/databasename_conn.php'); ?>

So I put in your db_connect function directly below this line.

This is what your code came out like when I put in the names:

<?php
$link_id = db_connect();
$sql=SELECT * from members3 where emailaddress="'.$_POST[emailaddress].'";
$Result=mysql_query($sql, $link_id);
if (mysql_num_rows($Result)>0) {
//display the html page with the error message
header("Location:thankyou.htm")
} else {
//unique email address
//so insert into the database
$sql='Insert into members3 set emailaddress="'.$_POST[emailaddress]'"; //add whatever fields you need to enter
$Result=mysql_query($sql, $link_id);
header(Location:"thankyou2.htm")
}
?>

The line in bold has consistently produced the error message:
Parse error: syntax error, unexpected T_STRING in C:\wamp\www\vu3aEUK2\application_form2.php on line 567 when run on local server whatever I do to try to correct it

On the remote server it objects to the previous line 566

I am quite at a loss!!

**DavidAllen** · #6 (**permalink**) 18-04-2008, 11:52

sorry my typo mistake
problem is this line - corrected below

PHP Code:


			
$sql='SELECT * from members3 where emailaddress="'.$_POST[emailaddress].'"';

forgot the ' before the SELECT and at the end

**DavidAllen** · #7 (**permalink**) 18-04-2008, 11:56

Also the Connections/databasename_conn.php that you had already probably does something similar to the db_connect() function that I posted. I didn't mention it - but I have that function in a seperate file that is included in the same way. It means that you only have those connction details in one place - not in every php file you write.

**Pangloss** · #8 (**permalink**) 19-04-2008, 11:32

David - in spite of your generous and patient help I am still having trouble with this! Because there are so many fields in my form and because it has been such an epic adventure getting the thing up and running at all I am reluctant to start all over again. One thing I have not discovered is where to put your code so that it executes at the right time. Otherwise I have made some of it work. I do however get an error message about the use of 'header' as it points out that it has been used before on line 104.

Please do not feel obliged to enter this morass of difficulties, you have been a lot of help so far and I am very grateful. I do wonder however whether it is just possible to intercept the error message (from MySQL?) because I have designated emailaddress as unique which says :

"Duplicate entry 'paulbaron1@gmail.com' for key 2"

that being the email address I used when trying to make a duplicate entry. key 2 is what MySQL is calling my emailaddress field. If only I could make this into a better looking message this is all I need!

**DavidAllen** · #9 (**permalink**) 19-04-2008, 12:09

hi pangloss
it's not wildly easy to catch that error and make it look nice. if you want to pm me with your file i'll have a look at it to determine where to put the uniqueness check