Firstly, this question is pretty general and I think all languages are able to do it in its own way. Just that I'm making a PHP script that does the job, so I'll post it in the PHP forum.

Now the question.. I'm trying to make a database record of an ID number which is not supposed to be set as auto-increment in the MySQL databse, rather the user has to key in manually the ID number. THe ID is unique and its like an ID for an invoice or receipt that suppose to run in incrementing order.

For example:
Invoice ID : 1000
Invoice ID : 1001
Invoice ID : 1002
Invoice ID : 1003
... and so on..

Now let say if the user misses a number and hop to 1005, missing 1004, what is the programming code to detect that 1004 is missing?

For example:
Invoice ID : 1000
Invoice ID : 1001
Invoice ID : 1002
Invoice ID : 1003
Invoice ID : 1005
Invoice ID : 1006
... and so on..

Thanks alot for help..

What do you mean if the user misses a number? What are they actually doing when they enter this number? Are they just entering the ID in a field and pressing 'submit'? If that's the case then it doesn't matter if they miss a number; you just query the db based on the ID entered at that time.
If there's more to it than that you're gonna need to explain it more.

You see, I'm trying to make a MySQL database script with PHP for to keep a record of invoices for the sales of a company. Every of the invoices has in invoice number, which runs in incrementing order (1000, 1001, 1002). I did realise that MySQL also has an auto-increment feature that started from 0 and followed by 1, 2, 3 and so on.. However this invoice number needs to be entered manually. So in the database table, there will be the auto-increment ID column, Invoice Number column, and other columns to store the invoice information and details.

So probably after a month of database entry, the user wants to retrieve all the database information and view them as output. Since there could be hundreds of entries, the user might miss an invoice number. Like the 2nd example I gave in my previous post. So I would like to make a script that is able to detect the missing invoice number everything the entire database is being displayed, and arranged order by invoice number in accending order. This feature is especially useful because the user is able to see if they had missed a number or missed a client or some related matters since they would probably sum up the stuffs at the end of every month.

Simply, I would just like to know how do I do it to detect and display if a number has missed?

I still don't get how a number's going to be missed. The database assigns the ID number whenever a new record is entered. It's not going to skip any numbers.

Yeap.. I know what you meant.. But you don't know what I meant.. In the database table, there will be the auto-increment ID column, Invoice Number column, and other columns to store the invoice information and details..

This is how it will look like in the database table:

ID (auto-increment) | InvoiceNumber | Name | etc..
----------------------------------------------------------
1 | 1000 | Bill
2 | 1001 | Peter
3 | 1002 | Smith

Now ignore the auto-incrementing ID column, because I will not use it anyway. I will only take reference from Invoice number column to track the customers.

So I'd rephrase my question if its a little unclear: How do I check if I've missed an Invoice number, assuming the invoice number should be in incrementing order.

Notice that the following will be valid, to show what I meant by incrementing in the invoice number, not according to how the data is arranged in the database table, rather is according to how data is arranged when displayed with the SELECT SQL statement.

Valid Database Table
ID (auto-increment) | InvoiceNumber | Name | etc..
----------------------------------------------------------
1 | 1000 | Bill
2 | 1001 | Peter
3 | 1002 | Smith
4 | 1005 | Peter
5 | 1003 | Smith
6 | 1004 | Peter
7 | 1006 | Smith
8 | 1008 | John

This will be displayed out with the SELECT SQL statement, ORDER BY InvoiceNumber in Accending.

SQL Statement : SELECT * FROM Table WHERE InvoiceNumber ORDER BY ASC
InvoiceNumber | Name | etc..
----------------------------------------------------------
1000 | Bill
1001 | Peter
1002 | Smith
1003 | Smith
1004 | Peter
1005 | Peter
1006 | Smith -----|___ Invoice Number 1007 is missing
1008 | John -------|


So how to detect that the Invoice Number 1007 is missing?

This post has been edited by Inspiron: Mar 8 2006, 03:36 PM

Another potential problem to recognize at this stage of the development is the fact that unless you take advantage of the auto-increment in SQL, you run the risk of having duplicate numbers. Is this a concern? I think it should be.



Seems to me that you are having the Invoices produced manually or you would be using the auto-increment feature of SQL to provide the Invoice number on creation of the Invoice. Am I correct? Do you need the "missing Invoice number" on creation? or on entry of the Invoice data? or only once a month or so when the Customer requests this Audit of the Invoice numbers?

And the "ID" number is the database key rather than the "Invoice Number"?

I see this making a difference in the answers here. Maybe you should list your Database Schema so we could better understand this problem and assist in developing this code.

If you have your heart set on having the user enter the number then you could just check, whenever they enter a number, if it's 1 more than the previous. Otherwise couldn't you just do something like ID+999?

Why not let the db auto increment your invoice numbers too? Why does the user have to enter it? If you're worried about missing a number, then letting the db calculate what the next number is seems like the way to go.