Next Number In Sequence Following Specific Value

[dlebowski]

Ok. I have spent a couple of days on this and have had no luck figuring it out. It seems easy enough, but apparently i'm not smart enough to figure it out.

 

Anyway, here is what I want to do:

 

Table "lots" has the following column:

 

LotNumber

1

2

4

5

6

7

100

102

104

 

Here is my code:

 

$j = mysql_num_rows($res);
$i = 0;
while ($i++ < $j and mysql_result($res, $i - 1, 0) == $i);

 

 

This code works great if I need the next available number starting from "0". That value would be "3". But lets say I want to get the next available number starting at 100. How do I do that? For example, the next available number after 100 is "101". I can't get it to do this for me. Any help would be appreciated. Thank you in advance.

 

Ryan

[requinix]

Hmm. You can do a LEFT JOIN from the table back onto itself looking at the current lot number + 1, and if the other side is NULL then there's no matching row and you can return the number + 1.

SELECT one.LotNumber + 1 FROM lots one LEFT JOIN lots two ON one.LotNumber + 1 = two.LotNumber WHERE two.LotNumber IS NULL ORDER BY one.LotNumber ASC LIMIT 1

Give or take.

[dlebowski]

Thank you for the reply! What if I want to specify whether I start with "0" or "100" as my starting point? AND I only wanted it for a certain date.

 

So for example:

If I start with "0" and date of 2012-11-15, the next available number is: 3.

If I start with "101" and date of 2012-11-15, the next available number is: 103.

 

Also, what if my table looked like this:

 

LotNumber Date

1 2012-11-15

2 2012-11-15

3 2011-10-12

4 2012-11-15

5 2012-11-15

6 2012-11-15

7 2012-11-15

100 2012-11-15

102 2012-11-15

103 2011-10-12

104 2012-11-15

[Barand]

Table "lots" has the following column:

 

LotNumber

 

then

 

Also, what if my table looked like this:

 

LotNumber Date

 

If there is one thing developers love it's people telling us one thing then telling us that that wasn't the whole truth.

 

If you are serious about wanting help, tell the whole story - not bits at a time.

[dlebowski]

I understand. I wasn't trying to just tell you "bits at a time". I thought I could figure it out if I had the first part of my question answered. I'm not able to. So what you see in this post is the whole story. Any help would be appreciated. Thanks.

[akphidelt2007]

I would just do a query and loop through it to get the next number. For example

 

$lotNumber = 100;

//this gets all lot numbers between 100 and 200
$qry = "SELECT lotNumber FROM lots WHERE lotNumber BETWEEN $lotNumber AND ".($lotNumber + 100)." ORDER BY lotNumber";

$result = mysql_query($qry);

while($row = mysql_fetch_array($result))
{
	 if($row[0] != $lotNumber)
		 break;
	 else
		 $lotNumber++;
}
[/Code]

 

Now the next available number is $lotNumber and just insert the value in to your db

Edited November 15, 2012 by akphidelt2007

[Barand]

Can you have the same lotNumber with 2 different dates?

[requinix]

Thank you for the reply! What if I want to specify whether I start with "0" or "100" as my starting point? AND I only wanted it for a certain date...

What I said before still works, just constrain it by the lower LotNumber limit (on the first table) and the date on the second table being before the date you want.

[dlebowski]

Thanks for help!

[requinix]

and the date on the second table being before the date you want.

Correction: on or after. The JOIN is supposed to check if the next row is not usable.