How To Check If Incrementing Misses A Number

Inspiron

Mar 7 2006, 11:32 AM

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

Tyssen

Mar 7 2006, 10:44 PM

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.

Inspiron

Mar 8 2006, 06:19 AM

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?

Tyssen

Mar 8 2006, 12:27 PM

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.

Inspiron

Mar 8 2006, 03:33 PM

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?

jlhaslip

Mar 8 2006, 06:04 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.

QUOTE

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.

beeseven

Mar 11 2006, 05:10 AM

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?

Tyssen

Mar 11 2006, 06:56 AM

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.

Inspiron

Mar 11 2006, 07:11 PM

QUOTE(jlhaslip @ Mar 9 2006, 02:04 AM)

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.

QUOTE(beeseven @ Mar 11 2006, 01:10 PM)

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.

QUOTE(Tyssen @ Mar 11 2006, 02:56 PM)

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?

no9t9

Mar 12 2006, 12:27 AM

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.

CODE

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

Latest Entries

no9t9

Mar 22 2006, 12:53 AM

rsort should work. If you have copied your code directly out of your test page, it is not working because the variable you are using for the rsort function is missing the $.

Try that. It should work.

Inspiron

Mar 20 2006, 11:14 AM

QUOTE(jlhaslip @ Mar 19 2006, 08:53 AM)

Check out sort(), asort(), rsort(), arsort(), and ksort() at http://www.php.net.

I thought it would work. I tried out but it failed. It displayed nothing out. I've actually tried to integrate rsort() with the codes provided by no9t9. But it wasn't possible.

QUOTE(no9t9 @ Mar 20 2006, 08:01 AM)

I'm not sure why you need to do it in ascending order because it doesn't matter. If you simply want to display the missing invoice numbers in the reverse order, simply change the echo statement that displays the missing invoice number to assign them into an array. Then manipulate the array anyway you like.

As for the other question, I'm not sure what you are asking... What do you mean? Another thing, if you are just manipulating invoicenumbers, you don't need to SELECT * --- Just need to SELECT InvoiceNumber

In my script, I actually use the "SELECT" SQL statements to retrieve all the data from the database first. Then I store the total number of entries in the database in the array. With that array, I then used your codes which you've posted earlier on. Because initially I used the SQL statement "SELECT * FROM Table ORDER BY InvoiceNumber ASC", the Invoice Numbers extracted from the database are in accending order in the array as well. Hence your codes would work since it compares the current element in the array with the next element in the array. Your algorithm, let's call it a "+1" type, meaning it only compares the data in the element with the next (+1) element. However your codes cannot compare the current element with the previous element.

Currently, I'm extracting the data from the database in decending order, with the SQL statement as "SELECT * FROM Table ORDER BY InvoiceNumber DESC". Because the SQL statement is now in decending order, the new array that it stored the invoice numbers are also ordered in decending order. From then, the codes that you had provided is not possible to detect the missing invoice numbers, since your codes are the "+1" type as I've defined above. I believe you will need to edit the codes to become a "-1" type instead to work. But I don't know how.

I've also tried the suggestion from jlhaslip by using the rsort() function. I was trying to use the rsort() function to re-arrange the array order, which was then stored the invoice numbers in decending order with the SQL statement as "SELECT * FROM Table ORDER BY InvoiceNumber DESC". The rsort() function that I've used attempted to re-sort it back within the array itself, such that your previous codes can be used, using the "+1" type. However it failed.

This was what I've did, trying to use the rsort() function with the codes you've provided

CODE

                     $query = "SELECT * FROM Table ORDER BY InvoiceNumber DESC";

                     $result = mysql_query($query);
                     $num = mysql_numrows($result);
                     $i = 0;
                     $tempinv[] = "";
                     while ($i < $num)
                     {
                        $tempinv[$i] = mysql_result($result, $i, "InvoiceNo");
                        $i++;
                     }

                           rsort(tempinv);
                           for ($i = 0; $i < count($tempinv) - 1; $i++)
                           {
                              if (($tempinv[$i+1] - $tempinv[$i]) > 1)
                              {
                                 for ($c = 1; $c < ($tempinv[$i+1] - $tempinv[$i]); $c++)
                                 {
                                    echo $tempinv[$i]+$c." ";
                                 }
                              }
                           }

It did not worked. I believe the codes which you've provided earlier on should be changed to a "-1" type, such that it can compare the current element in the array with the element of invoice number smaller than the current one. In another words, the codes should now do the opposite.
How do you do that? Let me know if you are using the rsort() function or not as well. Thanks..

no9t9

Mar 20 2006, 12:01 AM

QUOTE(Inspiron @ Mar 15 2006, 07:05 AM)

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.

jlhaslip

Mar 19 2006, 12:53 AM

Check out sort(), asort(), rsort(), arsort(), and ksort() at http://www.php.net.

X155

Mar 18 2006, 09:40 PM

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;
?>

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE