generating counter code for database entry

Question

I am using PHP/MySQL, to generate a counter code for the each entry while opening the entry screen.

Here is the code I am using

$qresult = $db->query("SELECT count(*) FROM ap_aurora_projects");
$row = $qresult->fetch_row(); 
$slno = $row[0] + 1;

$today = date("ymd");

$code = $today . "-" . $slno;

This code is generating correctly and showing to user before he creates the record. The issue is when multiple users open same time the code is duplicating in their screens.

What is the method to avoid the duplicates.

I tried to create a separate table to store the latest number and incremented for each request, but the issue is, if user cancel the entry, that number is missing.

EDIT

The code is working properly when one user generate the code and insert the record. But the issue is if multiple users opens simultaneously it generates same number to them in their screen. If all of them try to save the record it is getting duplicated. Please note that, I generate this number and show in the entry screen initially not while saving the record.

Answer 1

I suppose the code you show in the question is simplified and you can't use AUTO_INCREMENT column as suggested by another answer.

You will need to lock the table for reading until it's updated. Do the MySQL queries in the following order:

LOCK TABLES ap_aurora_projects READ;
SELECT count(*) FROM ap_aurora_projects WHERE ...;
//Update here ...
UNLOCK TABLES;

More about locking whole tables can be found here: http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

If you are using InnoDB storage engine, you can read more about advanced (row) locking and transactions here: http://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-model.html