php - creating primary key based on date

Question

I am trying to create a unique ID for each record based on the date the record is created. Example, if the record is created today, I want the key to be 20101130XX where XX would be a sequential list of numbers such as 01, 02, 03 etc... in case more than one person creates a record today.

If 3 people created records yesterday their unique IDs would be

2011032700 2011032701 2011032702

and then midnight comes and someone creates a new record it would be

2011032800

The purpose of this is to give each record a unique ID that will be used to reference the ticket.

We are already using the date format, but currently users are having to manually enter the id and that can create errors such as 2012032700 when it should have been 2011032700 so instead of putting 2011 they put 2012

I am just not sure how to create a trigger to generate this type of unique ID and would appreciate any assistance

Answer 1

From dev.mysql.com: example-auto-increment.html

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

So, make 2 columns on the table, one dateEntered and one (auto_incremented) id, like this:

CREATE TABLE yourTable (
    dateEntered DATE NOT NULL,
    id INT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (dateEntered, id)
) ENGINE=MyISAM;

If you don't use ISAM but InnoDB, I think you'll have to write your own trigger that implements this behaviour.