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.

 

 

 

Reply

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.

 

 

 

Reply

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..

Reply

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:


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:


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:


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:


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



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[i])" 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[i])" 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

Reply

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.

Reply

Might want to consider that given the nature of the task, there may be instances where the number range of the missing Invoices is greater than the example you give. You may want to cover this possibility by checking the range of missing numbers and echoing the start and end numbers instead of each individual number on the screen. Wouldn't take long to fill a screen or create a mass of output which isn't really required.

Reply

That's even easier than displaying all the numbers in between. I leave it up to the original poster to decide what they want.

Reply

We cannot rule out that possibility. Thanks for voicing out. However, chances are slim that voice numbers missed are greater than those that were entered in the database. Usually when a company misses an invoice number, it would most probably be that the order of the invoice has been broken such that the particular invoice number is being cancelled. It can also be that the invoice is torned or destroyed upon natural disasters such that a new invoice is being issued.

But anyway, that a very good point voiced. Now another question. How to check for invoice numbers for the point voiced by jlhaslip?

Reply

It worked! I'm quite surprised that you said you didn't test the code. A very big thank you to you.
But now I've another problem. It seemed easy again, but I took hours to use your codes and re-edit them to suit the SQL statement when the invoice numbers are arranged in decending order. Your code only works in acsending order, perfectly.

Another Question
Do you know how display those invoice numbers when the SQL statement is
"SELECT * FROM Table ORDER BY InvoiceNumber DESC" ?

Thanks again for being such a great help.. You saved me and my brain cells and helped me from going crazy.

Reply

Why don't you just allow MySQL to arrange the output in ascending order for the Invoice ID, then run a PHP code to check whether it is running in order within a specified value?

Example:

<?php
// Connect to DB
$invoiceidmin = 1000;
$invoiceidmax = 1054;
$counter = $invoiceidmin;
$result = mysql_query('SELECT * FROM table ORDER BY invoiceid ASC');
while ($row = mysql_fetch_array($result) && $invoiceidmax != $counter) {
if ($row["invoiceid"] != $counter) {
$missing[] = $counter;
}
$counter++
}
$csvmissing = implode(",", $missing);
print $csvmissing;
?>

Reply