Avoiding duplicate database entries

  • Filter
  • Time
  • Show
Clear All
new posts

    Avoiding duplicate database entries

    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 '[email protected]' 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?

    When the user submits the form - first do a call to the database
    PHP Code:
    $sql=select from TABLE_NAME where email="'.$_POST[email].'";
    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
    David Allen - www.serina.co.uk


      Perfect answer, imperfect realisation

      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?


        That was php

        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 Server 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!");

        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].'";
        if (
        mysql_num_rows($Result)>0) {
        //display the html page with the error message
        } 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);

        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:
        <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">

        for ($i=0;$i<10;$i++) {
        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 Allen - www.serina.co.uk


          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:

          $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
          } 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);

          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!!


            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
            Last edited by DavidAllen; 18-04-2008, 11:57.
            David Allen - www.serina.co.uk


              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.
              David Allen - www.serina.co.uk


                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 '[email protected]' 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!


                  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
                  David Allen - www.serina.co.uk


                    Originally posted by DavidAllen View Post
                    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
                    I haven't yet worked out the intricacies of this forum and, aparently a PM is not available to me as I have not made enough postings....

                    However I have only just received notification of your last message which said you would take a look at the page in question. This is very kind of you and the problem is still unsolved! So could I take you up on it now? (The problem was to specifically point out to people that they (well, their email address) were/was already in the database).

                    I am not sure how I can get the page to you even though it says one can attach to a forum reply.

                    Regards - P


                      Reply from Pangloss

                      Thanks, enablingbiz, for the recommendation - sounds like a really good tool. Having visited their web site hosting it looks like you might have to be hosted by them. My problem really is that I have produced a nice looking form which dumps the results into a Mysql database but the validation part of the form is really not so good and, in particular, needs a method of stopping people making a repeat visit and using it again.


                        Pangloss I've sent you a PM with my email address
                        David Allen - www.serina.co.uk