Help Needed - mysql query with while loop

[Unicorn13]

Hi All,

I'm new to php and mysql and I have been trying to work this one out for a bit, if anyone can guide me it would be appreciated.

I need a function to generate and check order#'s to make sure of no dupes in the orders_id row of the orders table of a mysql db.

The thing is I am not sure where to start, I have found individual snippets but not sure how to merge them.

Outline of function:

1. Generate a random order# (based on dmyxxxx(xxxx being 0000 to 9999))

2. Check orders_id of orders table for duplicate

3 if dupe exists generate another order_id

4. do this a maximum of 10 times

5. if still dupe after 10 queries exit gracefulluy with trapable error (Bank payment module before starting)

6. if no dupe exists and return the order_id and a true flag so order can be inserted into the db.

Thank you in advance
Andy

[devinemke]

is the order number the primary key field in the table? if so any particular reason that you don't simply use auto_increment?

[Unicorn13]

order_id is the primary keyfield in table, oscommerce is set as auto increment, but we are going to be using the order_id as the Sales order ref when exporting from the shop to accounts package.

My boss wants the first part of the ordernumber as the date (dmy), as a second line for tracking the date of the orders after export, as we don't always have time to sync the two and also it would mean we keep the format we have always used.

It is also a check, against actual order date, we have found that most people either delete or 'Cannot Find' their shop receipts and on one occasion there was a fault with a product after our warrany period has expired they tried to claim it was purchased after the actual date of buying.

Andy

[Unicorn13]

Forgot to mention the main reason!

Oscommerce assigns the order number and inserts the data for the order after it has returned from the payment module.

Our e-payment processor requires an order number passing to it before the module starts, this is because the customer is taken to the HSBC secure web site to complete the transaction and then is returned to our site , this is why we cannot use the auto increment.

Andy