Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    32

    Unanswered: Preventing duplicate records?

    This is really more of a design and/or php question than a MySQL question, but it influences the number of records created in the MySQL database.

    Currently, I have a start page that passes user input to a processing page's insert query. The processing page also displays the submitted data. Unfortunately, if the user refreshes the browser while the processing page is displayed, a new record is created with an auto-incremented ID number.

    To get around this problem, I have tried using sessions. I pass an
    AddRequest value of "Yes" from the start page to the processing page, register the variable, and then include the following lines:
    If ($AddRequest=="Yes") {
    $AddRequest="No";
    . . . process data here . . .
    }

    Unfortunately, the first time the user refreshes the processing page, I still get a duplicate record (although I am not sure how). If the user tries to refresh the page again, I do not get a third copy.

    Is there a way to prevent the addition of a duplicate record (with an auto-incremented ID number) to MySQL as a result of the user's refreshing the browser?

    Is it possible for the data to be passed from the start page to a processing page and then to a display page without the user's having to click 2 submit buttons (one to process and one to display results)?

    Thanks!

    Timm

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you use an auto_increment as the primary key, it helps to remember that this is only a surrogate key

    the "real" primary key is whatever column or columns would determine a distinct row in the absence of the surrogate

    for example, if the table contains LastName and FirstName, this might be an adequate primary key

    on the other hand, there are a lot of John Smiths, so perhaps LastName and Fristname and BirthDate would be a better choice

    note that surrogate keys have an immediate advantage in that they are compact -- you would not want to create another table which had many rows per person and try to relate that table back to the person table with a foreign key consisting of those three colulmns

    so while using a surrogate key is okay, you should never lose track of the fact that it's really just standing in for the "real" primary key

    okay, having said that, remember that one of the main attributes of a primary key is that it must be unique

    so there's the answer to your problem -- find out what other column(s) in the table should have a unique constraint declared on them

    then when the user accidentally hits the submit button, the database will prevent the "duplicate" entry

    note that with only the primary key constraint on the auto_increment, the database cannot help you, and i'm a firm believer in letting the database do what it's good at

    note, you will probably want to intercept the database "unique constraint violated" error message and instead display something more realistic to the user, like "you seem to have already entered that data"

    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •