How To Check If Incrementing Misses A Number

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?

Edited March 8, 2006 by Inspiron (see edit history)

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.

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.

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.

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?

In my PHP application, I'll never had to touch the auto-increment ID column. Not even inserting a new data, editing it or displaying it. Everything will be based on the InvoiceNumber column. The auto-increment ID column basically contains dummy data to the project that every database needs to have as to follow a standard. I just need a script, done in PHP, to extract the all the data from the SQL InvoiceNumber column and checks if a number in the InvoiceNumber column is missing. The beginning numbers in the InvoiceNumber column can start with any number, making sure that from that number onwards, there will be no missing number until the biggest number.

For example, the starting number is 4 and the largest or end-most InvoiceNumber is 9. The PHP script has to check if the numbers 4, 5, 6, 7, 8, 9, are present in the column, and detect any numbers that are not present to notify the user. Of course in this example, the numbers are quite simple. But expect those real invoice numbers to be 4 to 6 digits in length. And if it starts from 1, it will be stored in the database as 0001 if its a 4 digit invoice number. 6 digit numbers will follow the similar format.

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?

I cannot write a PHP script that does the auto-incrementing to the invoice number automatically. Not because I don't know how, but rather it is not the way. Because the user might enter an invoice data information with a invoice number that is not followed on sequence based on the previous invoice number.

For example, invoice 0001 is written in the database. The user now has an invoice, 0002, but it's on pending such that he/she cannot store the information in the database yet. So he/she has to skip this number temporary and carry on with 0003. So whenever the user checks on the database, the script will display a notice to the user that invoice 0002 is missing.

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.

Please refer to my previous replies.

Actually, it's a pretty simple question that you guys might think it into somewhat complicated. Anyway I guess my examples in this reply shows a clearer picture of what I'm talking about.
If anyone knows, how to detect and find a missing number in the Invoice column in the database?

I don't think the others understand the application. I think they were confused when you mentioned auto-increment. The application here is that the InvoiceID field is DIFFERENT from the InvoiceNumber. This is a totally acceptable way to set up your database schema. The application here is that the user may not enter the invoice numbers in order (for various reasons such as pending order which was mentioned earlier). This means the InvoiceNumber field CANNOT be auto-incremented.

The solution to your problem is pretty simple. All you have to do is compare each invoice number to the previous one.

1. Select the invoice numbers from the table and order by ascending (small to large).

2. Use a loop structure to compare EACH invoice number

3. Use another loop (nested) to display the missing invoice numbers

Here is a some sample code for the loop/if structure to get you started. I am going to assume you know how to select the invoicenumber from the table and order it into ascending order. Also, I am going to assume that you place all the invoice numbers into the array: Results.

Note: I am not bothering to put $ in the variable names and ; after lines... takes too long and I am lazy.

For (i=0,i<Count(Results)-1,i++) {  If ((Results[i+1]-Results[i])>1) {	For (c=1,c<(Results[i+1]-Results[i]),c++) {	  echo Results[i]+c }}}

That's it. The loop checks each number by subtracting the difference and then displays the invoice numbers for each instance that it is missing

i didn't test it or anything, but the logic should work... just make sure the counters are all right

Edit: Also someone mentioned duplicate invoice numbers. This will not check for duplicates. Checking for duplicates should be done at the point where you submit the data, not when it is already in the database. Checking for duplicates is even easier than the above. Let me know if you need help on that...
Edited March 12, 2006 by no9t9 (see edit history)

I think they were confused when you mentioned auto-increment. The application here is that the InvoiceID field is DIFFERENT from the InvoiceNumber. This is a totally acceptable way to set up your database schema. The application here is that the user may not enter the invoice numbers in order (for various reasons such as pending order which was mentioned earlier). This means the InvoiceNumber field CANNOT be auto-incremented.

No confusion about it whatsoever. I understood completely what he wanted. What I didn't understand (and still don't) is the absolute requirement that the invoice number be allowed to be issued in such a haphazard fashion as manually inputting manually created Invoice numbers which are input in random order on a sporadic basis. And then wanting to know which ones were missing. There is a completely different use for the 'Sequence number' of the database, I agree.
This goes beyond the actual coding of this problem. It has to do with the systemic problem of Invoice Control and regulation of the Invoice Numbering system. I have worked at Companies which produce an Invoice Number based on an alphabetic system , not numeric at all, and could still tell you when one was missing or out of place. And your code will only work with truly sequential numeric values, by the way.
There may be valid reasons why the Invoice number MUST be issued manually. That is an Accounting System decision. Let the programmer sort out how to track them after that. There doesn't need to be an accountant involved in writing the code, is all I am saying. Use the auto increment feature of the database to track the Invoices 'sequentially' regardless of what the 'number' is on the piece of paper you hand the Customer. There need not be a relationship between the 'auto-increment' number and the 'Invoice' number. In fact, then you have the accounting system meddling in affairs they know nothing about. Trust me, I've been there before. You will find that the 'sequencing' problem will be difficult enough to deal with. Even harder will be a duplicate number.

And your code will only work with truly sequential numeric values, by the way

I know what my code does. Unlike everyone else in this thread, I've actually tried to answer the original poster's quesiton. My code does exactly what was asked for. The requirements were spelled out and were very simple to understand. The original poster wanted a way to determine missing numbers from a sequence of numbers. I have solved the problem.

The other stuff you don't understand doesn't help solve the original problem. You can discuss "best practices" that should be used all you want but you don't know what the original poster's constraints are. What if the company already has 100000 invoices that were on paper and now they are entering them into a new system? In this case, the invoice numbers have already been "generated". The user simply wants to know if there were any missed invoices during the data entry.

If the original poster actually employed such systems (accounting, invoice number control), that you are so concerned about, you would have to spend a lot more time that a few minutes on this board to understand and make responsible suggestions for improvement. Chances are the original poster has the most knowledge of his system and he knows what he needs the most... not you.

And "Trust me", I know about billing systems and many other systems. It is part of my job.

I don't think the others understand the application. I think they were confused when you mentioned auto-increment. The application here is that the InvoiceID field is DIFFERENT from the InvoiceNumber. This is a totally acceptable way to set up your database schema. The application here is that the user may not enter the invoice numbers in order (for various reasons such as pending order which was mentioned earlier). This means the InvoiceNumber field CANNOT be auto-incremented.

Yes, this was what I really meant! Thanks for that nice rephrase of what I'm trying to say.

Also, since you got the picture of what I'm thinking, can you explain what your code does? I don't quite understand. Probably a little bit more explaination will do. Thanks..

The code simply compares each number to the next number. If it is not incrementing by 1, it determines how many are missing (by subtracting the two). The nested loop basically displays each one that is missing.

 

first line:

For (i=0,i<Count(Results)-1,i++) {

the first line sets up a loop that will run through ALL the numbers in the Results array (minus 1). Why minus 1? You will see later...

 

The Count(Results) part of the for loop basically determines how many elements are in the array.

So in this example, the number of elements returned will be 4. That means the counter "i" will go from 0 to 2 because i used the < instead of <=

 

second line:

  If ((Results[i+1]-Results[i])>1) {

the second line compares the current number with the next number. If the difference is > 1, then you know it is not in sequence.

 

third line:

	For (c=1,c<(Results[i+1]-Results[i]),c++) {

Now that we now the numbers are out of sequence, the third line is a nested for loop which counts HOW MANY numbers are missing from the sequence.

 

fourth line:

	echo Results[i]+c 

fourth line is part of the nested for loop and will basically display each number that is missing.

 

example:

Just use an example and follow it through. I will try to explain it but it is hard...

 

you have invoice numbers as follows

1001, 1002, 1004, 1007

 

assume you pulled these out of your database, ordered them in ascending order, and assigned them into the array Results. so...

Results[0]=1001

Results[1]=1002

Results[2]=1004

Results[3]=1007

 

For (i=0,i<Count(Results)-1,i++) {  If ((Results[i+1]-Results[i])>1) {	For (c=1,c<(Results[i+1]-Results[i]),c++) {	  echo Results[i]+c }}}

first time through the for outside loop (i=0)

- Results[0+1] - Results[0] = 1002 - 1001 = 1 ---- this is not > 1 so the if statement skips the nested for loop

- the if statement skips the display of missing numbers because no number was missing between 1001 and 1002

 

second time through the outside loop (i=1)

- Results[1+1] - Results[1] = 1004 - 1002 = 2 ---- this time, 2 > 1 so the code proceeds into the if statement.

-----inside the if statement------

nested for loop first time through (c=1, i=1)

- display missing number Result[1]+1 = 1002 + 1 = 1003 ---- 1003 is displayed on the screen which was the missing number between 1002 and 1004

 

nested for loop second time through (c=2, i=1)

- in this case the for loop will exit because the condition "c<(Results[i+1]-Results)" fails. ---- the second time through c=2 and the condition is that c < 2

-----if statement done-----

 

third time through the outside loop (i=2)

- Results[2+1] - Results[2] = 1007 - 1004 = 3 ---- 3 > 1 so the code proceeds into the if statement.

-----inside the if statement------

nested for loop first time through (c=1, i=2)

- display missing number Result[2]+1 = 1004 + 1 = 1005 ---- 1005 is displayed on the screen

 

nested for loop second time through (c=2, i=2)

- display missing number Result[2]+2 = 1004 + 2 = 1006 ---- 1006 is displayed on the screen

 

nested for loop third time through (c=3, i=2)

- in this case the for loop will exit because the condition "c<(Results[i+1]-Results)" fails. ---- the third time through c=3 and the condition is that c < 3

-----if statement done-----

 

fourth time through the outside loop (i=3)

- in this case, the loop will exit because the condition fails "i<Count(Results)-1"

i =3 and Count(Results) - 1 = 3

 

in the end 3 numbers were displayed on the screen

1003, 1005, and 1006

Edited March 12, 2006 by no9t9 (see edit history)

Wow.. great tutorial.. :(I'll copy this guide some where and wait til I get back to my workdesk, I'll test it out..Thanks alot..Please leave this thread opened, as I probably will need to post again if I have anymore problems.