Stop a duplicate entry being added into MYSQL database-CodePudding

A user makes a booking. They enter their name, the Pickup address and a Dropoff address and a time of pickup.

If, the user has an internet problem and their form doesn't seem to send, they press the submit button a second time, I often end up with 2 entries both the same in the database. Sometimes, Date_added column could change by amount of seconds because of when user clicks submit button.

I am trying to stop 2 entries being added to the database.

How do I check the database to see if their first submit was a successful entry into the database and stop a second row/record being added?

I was going to try using UNIQUE, but the user books trips and other times and also other users could use the same address's at times.

My columns names are: Date_added, Username, Pick_up_Address, Drop_off_Address, PU_time

How do I check the database to see if it's not a duplicate entry about to be added, that is only different by under a minute.

I don't know where to start with this or how to do it.

I tried UNIQUE in query but this would not work as other users would use the address's.

CodePudding user response：

This is not an issue from the back I believe but from the front.

Once the user hits the button, disable it, and add a "Loading functionality" and that should prevent the double save.

If the back answer with "200" (or whatever status you want) on the AJAX request, then, send the user to another page where they can see the booking data or any other kind of things like that.

If the back answers with another apart of "200", then re-enable the button.

So, you control everything from your back.

Now, if the user is not able to finish the request but the data has been saved to the database, validate that using your time. I see your PU_Time, so maybe if you have users with accounts, you can validate them using the same account PU_Time "if PU_time > now - 60 seconds" send the user to the "booking data page"

That should solve it.

CodePudding user response：

Use this Sql to insert:

INSERT INTO `table_name`(`Username`, `Pick_up_Address`, `Drop_off_Address`) 
SELECT '$userName', '$pickAddress', '$dropOffAddress' FROM `table_name`
WHERE (SELECT COUNT(*) FROM table_name WHERE Username = '$userName' AND 
DATE_SUB(CURRENT_TIME, INTERVAL 5 MINUTE) < Date_added) = 0

You can change the "5 MINUTE" to "1 MINUTE" or "1 HOUR" or "1 DAY"

CodePudding user response：

I believe I may have found an answer to my problem.

When the first database entry is INSERTed...I then set a session variable.

Then added:

if (!$_session['booked']){
  //Insert entry into database//

  $_session['booked'] = 1;
}