generating counter code for database entry

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开发者_JAVA百科 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.

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

Here's what you need to do:

First, create a table:

create table uniqueId ( id bigint(20) unsigned not null auto_increment primary key );

Second, use this code (I don't know which MySQL code you use, so I will use the common one):

$link = mysql_connect('localhost', 'user', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');
mysql_query("INSERT IGNORE INTO uniqueId VALUES (NULL);");
printf("Last inserted record has id %f\n", date("ymd") . "-" . mysql_insert_id());

By inserting and not selecting, you are sure you will not have duplicate.

Creating your own autoincrementing identifier is difficult and error-prone as you've discovered. You should think about using the MySQL AUTO_INCREMENT feature and the corresponding LAST_INSERT_ID() function which will return the id of the most recently inserted record in that session.

Key excerpt from that link:

These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

You will have to change your approach however. Instead of showing an ID in the screen before the record is created, you'll need to wait until the insert of the record. OR if you REALLY need to show this ID then insert a blank record to create and return the ID and then edit that record from user-entered data rather than performing an insert.

One worrisome idea is that you're needing to show the internal primary key to the user of the system. The key is not natural (such government-issued identifiers) and it is generated by the system. This smells a bit of bad design. Typically primary key values are never shown to the user and some other surrogate identifier is used.