CODEWITHSUNDEEP
phpmysqlsql
Spence
Spence

Reputation: 35

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

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 

001
002
003
005
ABC006

However the result should be..

001
002
003
ABC004

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.

Upvotes: 1

Views: 286

Answers (3)

ahmeti
ahmeti

Reputation: 504

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

Upvotes: 3

cowbert
cowbert

Reputation: 3463

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%'

Upvotes: 4

user4513271
user4513271

Reputation:

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

Upvotes: 3

Related Questions