Foolproof Sequential Order Number?

phplearner

I'm trying to figure out a foolproof sequential order number generation, but would like some guidance. Highly unlikely 2 people might click an order page at precisely the same 'microsecond', but I need to be aware of any flaws in either of the following two approaches:

Have a MySQL Table with one entry, which gets read from the initial .php script with "1" immediately added to it and re-inserted into the table column/row to reflect an updated count.
Use a 'flat file' with just one entry for the same purpose. I dunno if .php has a 'seek' type function like Perl to go to the front of the file to overwrite/update the number but probably so.

Which of the two approaches might be faster? If there is some other way or perhaps a function to already do this I'd sure appreciate knowing.

Thanks a lot.

Sxooter

Use an autoincrement field. In most databases, this will do what you want.

That and a unique key on the field ensure you will never have a dupe. I think MySQL requires the field to be pk to work.

phplearner

Hi again, Sxooter:

Yes, I thought about this but was trying to keep from growing a large table. But 'better safe than sorry' is perhaps best.

RE: 'pk' ... what is this? Oh... Primary Key

Thanks again.😃

phplearner

Gheezz... I thought this would be easy.

I set up a table to simply retrieve the auto-incremented ID numbers in sequence. But my default value of 1 doesn't display...the table is empty. Strange.

--SEQUENTIAL ORDER NUMBER Table
CREATE TABLE ordernum ( 
 ID int NOT NULL UNIQUE auto_increment primary key default '1'
);

How would you suggest I read & insert to this so that the numbers auto-increment?

It just dawned on me there could still be a potential problem, since the sequential number is going to be used to tag onto a date/time slimmed down string like:

20030908-142212-00000001
Date - Time - Seq Order Num

So if I read the last ID number and then do something to 'trigger' the autoincrement, what if another end-user was accessing at the same time at read the same ID number before the autoincrement took place?

Yikes.

tomhath

You're trying way too hard. Inserting a row with a unique ID is so common every modern DBMS has the capability built in.
The id will be generated and inserted for you every time you insert a row into the table, you don't have to keep track of anything. And your table is empty because you haven't inserted any rows. Read the manual and try the example they give: http://www.phpbuilder.com/manual/function.mysql-insert-id.php

By the way, the example you posted using flat files virtually guarantees you would have duplicates. It's a long explanation why; but never assume two lines of code will be executed one right after the next, the operating system can and will stop running your script and start running another whenever it wants.

phplearner

tomhath:

Tnx for the info. My understanding was that including a 'default' value at time of table creation established an initial record. Guess I wasn't getting that right.

I checked out the example you mentioned...thanks...and for clarifying the potential problems with a flat file scenario.

It still seems like there is a potential for error if multiple users insert at the same time. I was considering LAST_INSERT_ID() as well.

If an order number was generated at the time all the other data gets inserted to the DB this problem wouldn't exist, however the fact I need to combine date, time & sequential order number in a specific format, I am not seeing any other way.

That is, unless I can somehow format yymmdd-hhmmss-0000000n into one DB column/field (n=1 and incrementing upward upon each order entry).

Hmmm...actually, this would be the most error-free method if it can even be done.

Thanks again for the info.

phplearner

Well, I found this works for part of it:

-- SEQUENTIAL ORDER NUMBER Table
CREATE table seqordnum (
  ID int unsigned not null auto_increment, PRIMARY KEY (id))
  auto_increment = 00000001;

--Start it off with an INSERT
INSERT INTO seqordnum (ID) VALUES(NULL);

However, there must be a trick to having the number 'zero fill' because it only shows as '1' in the table column.

I guess I could INSERT all the data into all the table columns/fields, then before final processing:

Read out the system date & time & reformat
Read out the incremented ID number for the record
Combine #1 and #2 above
INSERT the 'custom' order number back into the DB in a separate column/field.

Either that, or run an if-else ditty on the ID number & zero fill - then combine with the date/time in the format I need and INSERT that.

Certainly either of the above would eliminate errors.

phplearner

Hot Diggity Dog...look what I found: ZEROFILL

-- SEQUENTIAL ORDER NUMBER Table
CREATE table seqordnum (
  ID int unsigned ZEROFILL not null auto_increment, PRIMARY KEY (id))
  auto_increment = 00000001;

--Start it off with an INSERT
INSERT INTO seqordnum (ID) VALUES(NULL);

Yields 1st record as: 0000000001

😃

phplearner

OK...in case anyone else needs to do this, I think I have a workable approach now.

Start off with Table creation and initial number

-- SEQUENTIAL ORDER NUMBER Table
CREATE table orderdata (
  ID int(6) unsigned zerofill not null auto_increment, PRIMARY KEY (id),
  orderupdated TIMESTAMP,
  ordercreated TIMESTAMP,
  ordernumber varchar(30)
)
  auto_increment = 00000001;

--Start it off with an INSERT
INSERT INTO orderdata (ID,orderupdated,ordercreated,ordernumber) 

VALUES(NULL,NULL,NULL,NULL);

;

I'll concatenate the order number with only one "-" and keep date & time together:

SELECT CONCAT(ordercreated,"-",ID) AS ordernumber FROM orderdata;

Finally, I'll update the Table column 'ordernumber' before moving on down the line:

UPDATE orderdata SET ordernumber = '20030909000722-000001' WHERE ID = 1  #test record

The above are all manual via MYSQLCC on the PC, so will need to put everything into PHP/MySQL stuff.

Now, ZZZZZZZZZZzzzzzzzzzzzzzzzzzzzz...

tomhath

What you have should work, but make sure you use mysql_last_insertid to get the row you want to update, don't try to INSERT then SELECT the last row in the table or you'll have the same problem as the flat file approach. Last_insertid guarantees you get the id that your process inserted.

phplearner

Tnx for the heads up, however I will be using an UPDATE based upon the actual TIMESTAMP. Just discovered I need to trim the Date/Time down 'cuz the overall order number can not exceed 20 characters...and need the "-" in between.

So, on to read up on preg_match (I think that's it).