Determine the next number in database query with while loop in php

Question

I have a Part Management system I've created in PHP with MySQL. What I'm trying to create is something that will generate the next Part Number for me. All part numbers start with a 3 letter prefix (which is determined by the product family/category) followed by their number.

For example 'ABC001'

What I have below is something that I'd like to use to determine what the next number is having already 'ABC001', 'ABC002' & 'ABC003' so I would like it to recognize what the next number is by querying until the query comes back false because that product number doesn't exist yet.

$abc_query = "SELECT * FROM products WHERE id LIKE 'ABC%'";
$abc_result = $mysqli2->query($abc_query);
while($row = $abc_result->fetch_assoc()) {
		$rowid = $row["id"];
		$pnumber = substr($rowid, 3, 3);
		echo $pnumber. '<br/>';
		$int = (int)$pnumber;
		$abc_query2 = "SELECT * FROM products WHERE id 'ABC" . sprintf('%03s', $int);
		
		for ($abc_query2 = true; $abc_query2 = false; $int++){
			echo $int;
		}$abc_nextnumber = $int +1;
}
$abc_newnumber = 'ABC' . sprintf('%03s', $abc_nextnumber);
echo $abc_newnumber;

The result I get is

However the result should be..

code update I've updated the code but it doesn't seem to stop at ABC004 if I have an 005. It will go to 006.

why not have mysql split the column on ABC, cast the remainder to integer, then select max() on that? — cowbert
– cowbert, Commented Oct 25, 2017 at 19:12
Is that difficult? I don't want to change the database at all. — Spence
– Spence, Commented Oct 25, 2017 at 19:20

cowbert · Accepted Answer · 2017-10-25 19:26:34Z

4

You should have the db do this instead of your app:

select t.id_prfx, max(t.id_num) as latest_num from 
    (select substring(id, 1, 3) as id_prfx, 
     cast(substring(id,4) as integer) as id_num) t
    group by id_prfx

This will give you a result table where you get the highest part number for each prefix.

If you really really only want prefixes of 'ABC' then:

select max(cast(substring(id,4) as integer)) as max_num from table
where id LIKE 'ABC%'

edited Oct 25, 2017 at 19:26

answered Oct 25, 2017 at 19:20

cowbert

3,4833 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Spence Over a year ago

The reason I'd like it to count until its false is because there may be some instances where we have 'ABC001', 'ABC002', 'ABC003' & 'ABC005' and I'd like it the find the next available one which would be 'ABC004'

ahmeti · Accepted Answer · 2017-10-25 20:54:09Z

3

Could you try this query?

~~SELECT MAX(SUBSTR(id, 4)) as last_id FROM products WHERE SUBSTR(id, 1, 3)='ABC'~~

EDİT:

products TABLE
==============
ABC001
ABC002
ABC003
ABC005
==============

We want to find 4 in products table.

SELECT SUBSTR(t1.id, 4) + 1 as POSSIBLE_MIN_ID
FROM products t1
WHERE NOT EXISTS (
    SELECT * 
    FROM products t2
    WHERE SUBSTR(id, 1, 3)='ABC' AND SUBSTR(t2.id, 4) = SUBSTR(t1.id, 4) + 1
) LIMIT 1

RESULT: POSSIBLE_MIN_ID : 4

edited Oct 25, 2017 at 20:54

answered Oct 25, 2017 at 19:23

ahmeti

5046 silver badges13 bronze badges

6 Comments

Spence Over a year ago

The reason I'd like it to count until its false is because there may be some instances where we have 'ABC001', 'ABC002', 'ABC003' & 'ABC005' and I'd like it the find the next available one which would be 'ABC004'. Looking for the best way to do that

ahmeti Over a year ago

@Spence I did not understand the question correctly. I updated my answer.

Spence Over a year ago

That actually gives me the result I'd like when querying in PHPMyadmin. How would I echo out this out or put in a variable?

Spence Over a year ago

Nevermind I figured it out! Thank you. I will mark this answer as complete.

Spence Over a year ago

How would I go about having it start at 100, for example ABC101. ABC102 etc. ?

|

score 3 · Accepted Answer · 2017-10-25 19:22:32Z

3

If anyone knows how I can have it add automatic zeros to the into the query (as it will be different amount of 0s once it gets to 'ABC011') instead of typing them in that would also be very helpful.

Here's how to automatically handle the prepended zeroes.

 $sql3 = "SELECT * FROM products WHERE id 'ABC" . sprintf('%03s', $int);

edited Oct 25, 2017 at 19:22

answered Oct 25, 2017 at 19:14

user4513271

4 Comments

Spence Over a year ago

Thanks for your response. The main answer I'm looking for is the first part. For the sprint - It would be at most 2 zero, but would only be 1 if we end up at 'ABC010' - How would I go about that?

user4513271 Over a year ago

The zeroes will automatically change in count depending on the value of $int. So if $int is 1, the code above will return 001. If $int is 10, it will return 010. If $int is 900, it will return 900. It's basically saying to prepend 0s until the the length is to 3 places long.

Spence Over a year ago

Thanks so much!! Wish I'd found this earlier!

ahmeti Over a year ago

You may need to do a lot of queries to get the result

Collectives™ on Stack Overflow

Determine the next number in database query with while loop in php

3 Answers 3

1 Comment

6 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related