CODEWITHSUNDEEP
phpdatabasemysqli
threeFatCat
threeFatCat

Reputation: 819

How to avoid duplicate entry in the database if the data is submitted from two users at the same time?

My question is a little bit confusing, i understand that my question may sound similar to other questions here. I have a form that is numbered and increase by 1 depending what is the last value in the database. For example in the database: 

ID | ApplicationNumber
 1 |    1
 2 |    2

So if the user will generate another form it will display as: Application Number 3. And it will be save in the database as application number 3. But what if there are two users who will submit the form at the same time? Both are filling up the form numbered as: Application Number 3 and before my INSERT INTOi perform the query of looking the last value in the database so i can +1 to the value of Application Number value before submitting it to the database. I let two users submit the form at the same time and what i received in the database is like this:

 ID | ApplicationNumber
 1 |    1
 2 |    2
 3 |    3
 4 |    3

My query for getting the last value and BEFORE inserting something to the database:

$query = mysqli_query($con,"SELECT table1.ApplicationNumber FROM table1 INNER JOIN table2 ON table1.AdminID = table2.AdminID AND table2.Username = '$user' ORDER BY table1.ApplicationNumber DESC LIMIT 1");

while($row = mysqli_fetch_array($query)) {



    $aNumber = $row['ApplicationNumber'] + 1;
}

Upvotes: 0

Views: 1301

Answers (1)

Nagh
Nagh

Reputation: 1804

If I'm getting you right there are few straightforward options from the database point of view:

  1. Use autoincrement for the ApplicationNumber column in mysql
  2. Create Unique-key constraint for this column and handle this error somehow
  3. Also you can just LOCK the table before select, so you have exclusive access to the data. Beware, you should especially care for unlock and understand that other users won't be able to use this table, while lock in effect.

Upvotes: 2

Related Questions