Generating a Number

Highest From Two Tables

[wfernley]

Hello. I am trying to generate an Invoice Number in PHP. I have two tables orders_pending and orders_completed.

The way I have it working now is it grabs the highest number from orders_pending and the highest from orders_completed. Then it grabs the highest of the two and adds 1. That number is then the customers Invoice Number. I am hitting problems though when it doesn't work correctly and then the two numbers from orders_pending and orders_completed are equal. Then it doesn't know what to do.

Basically I was wondering what the best way to do this function would be. Basically the site is working where the user goes offsite to pay online with a credit card. So I have it set so when they do go offsite it adds their info to a orders pending table so if they pay and close the browser after paying but before they get sent back to our site we still have their info and order. Now, if they do get sent back to our site then it deletes the record from the pending table and adds it to the orders completed table.

I set it this way so that we always have info just incase something happens and we get paid but dont have the order.

Is this a good setup? If so, how should I generate a Invoice number. The process in my mind sounds quite simple but I can't think of how I should program it. It has to grab the highest number out of the two tables and then add 1. Or if they are equal decifer a number that isn't a duplicate and higher than all the other numbers. This number also has to be only 5 digits so I can do a random 10 digit number.

Any help that can provided will be greatly appreciated.

Thanks :)

Wes

[marilyn]

Maybe I don't understand you, but why can't the order number be the same as the invoice number?

I do it like this : when an order is made, the order number comes from the auto increment field in the order table, so is unique to the order table. Say that number is 95. I then pad the number out to 5 digits and get 00095. The order number then is ON00095 and the invoice number is IN00095.

I would just have one order table with an order number auto increment field, and have a field that gives the status - pending or completed (or whatever). That way you are not dealing with multiple tables and your work is easier. If the customer never comes back the order remains 'pending'. If he does it gets set to 'completed' and you can give him his order number. Indeed, you can pass the order number to the payment system so that it appears on his credit card bill. That is what I do, more or less.

Marilyn

[wfernley]

Thanks for the reply Marilyn. I think I'm going to design just one table and have the invoice number being an auto-increment. That way there are no duplicates and it would make it a lot easier to design.

Thanks again for the reply :)

Wes

[jatar_k]

Then when you create a new row via insert you can call mysql_insert_id [ca2.php.net] to get the invoice id for the just created row if you need to display it for the customer.