Thanks for your lengthy responses everyone. I ended up using sequences which are available with PEAR MDB2 and DB, so i get the currentID + 1 while inserting, but as you say another entry could have gone in during the process, but this happens during the insert stage. I might have to revert the table to auto inc and use concat instead on the current primary id ? The prefix's used on the tickets are the issue areas its coming from therefore enum is not possible, enum would also limit it to that and would have to be updated all the time :)
I had a similar issue with another project where i would have to create the issue number using the nextId sequence, but during that time an extra sequence nextID may have been placed before ther first insert and two entries could be inserting the same number :|, its a flaw i need to fix. See the problem with this one is, a ticket number needs to be issued before the actual insert because documents get stored within directories of timestamp/issue number. so i need to create the directory from the issue number, but after they have scanned documents (using a twain activex :) ), and ready to submit the form all kinds of problems might arise :| I may have to leave the creation of directories and moving documents until the insert stage and store the documents in a temp directory which reflects the user logged in and possible timestamp.
On 21/04/2005, at 8:06 AM, Harald Fuchs wrote:
It sounds like you are generating primary keys based on some letters + an
incrementing value. That is a very user-friendly method but does not lend
itself well to MySQL. What you CAN do with mysql is to split your primary
key into two columns, one text the other an auto_increment-ed numeric.
Then, when you insert the new row of data you can use LAST_INSERT_ID() to
get the numeric value assigned to the new row.
Although this trick does what Dan wanted, I would not recommend using it. First of all, it's extremely unportable - even within MySQL (it works only with the MyISAM backend). Secondly, it's the same kind of mistake you do when you insist to assign consecutive numbers to your rows. This just won't work in the long run - you'll get gaps in the sequence, either by deletions or by a rollback of a transaction. You just have to live with the gaps.
Applied to Dan's problem:
CREATE TABLE IncidentData ( IncidentType varchar(8) NOT NULL default '', TypeSerial int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (TypeSerial) );
INSERT INTO IncidentData (IncidentType) VALUES ('request'); INSERT INTO IncidentData (IncidentType) VALUES ('request'); INSERT INTO IncidentData (IncidentType) VALUES ('warning'); INSERT INTO IncidentData (IncidentType) VALUES ('fault'); INSERT INTO IncidentData (IncidentType) VALUES ('request');
SELECT CONCAT(UCASE(IncidentType),'-',LPAD(TypeSerial,8,'0')) as Serial
, IncidentType
, TypeSerial
FROM IncidentData;
This returns:
+------------------+--------------+------------+ | Serial | IncidentType | TypeSerial | +------------------+--------------+------------+ | REQUEST-00000001 | request | 1 | | REQUEST-00000002 | request | 2 | | WARNING-00000003 | warning | 3 | | FAULT-00000004 | fault | 4 | | REQUEST-00000005 | request | 5 | +------------------+--------------+------------+
As you can see, there's a gap between REQUEST-00000002 and REQUEST-00000005,
but what's wrong with that?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
