generate next asset_tag from mysql with php

[bowen73]

im trying to make an asset list and the DB/php us getting a bit beyond my limited knowledge and searching online the suggestions are giving me errors or not incrementing!

 

basically i have a column (asset_tag) that contains a code...lets say MM00001.  As we already have tags not in a DB that i need to add these into the DB (which is the easy part) but im also after a 'generate'  when adding an item to the DB that does not already have a tag.  so then it looks at the DB from asset_tag ( MM00001  ) and increments to the next number into a variable ready to add to the DB with product info.  but if a number has been manually added (lets say MM03003)  then i want to generate a number up to that then skip past as it already exists.

 

ive tried to get started with things like:

SELECT * FROM asset_list
WHERE asset_tag = (SELECT MAX(asset_tag) FROM asset_list)

ive also tried this just to see if i get back the test number of MM00005 in the DB to get started:

$row = mysql_fetch_assoc( mysql_query( "SELECT asset_tag FROM asset_list ORDER BY asset_tag DESC LIMIT 1" ) ); 
$number = $row['asset_tag']; 

echo $number 

but i just get this error:  

Fatal error: Uncaught Error: Call to undefined function mysql_fetch_assoc() in C:\xampp\htdocs\Magic\test.php:20 Stack trace: #0 {main} thrown in C:\xampp\htdocs\Magic\test.php on line 20

ive also looked at insert_id but the examples ive seen return the ID after its been added rather than generating the next one to use...

 

ive been scratching my head at this for a while to try and get somewhere, but i have to admit defeat and request some help to get me in the right direction, thanks

[Psycho]

The easiest solution that comes to mind is to use an auto-increment integer id field for the records. Then do not use the "MM" before the id value (in the database). you can always append the "MM" when selecting the records or when displaying them.

 

When a user manually enters an ID you can choose whether to allow them to enter with or without the "MM" (or both) and just strip the MM before inserting.

 

Using an auto-increment ID field will work without any special logic. You can add a new integer ID that does not already exist and if you try to insert a new record without the ID a new one will be generate automatically that is +1 from the highest existing value.

[benanamen]

You are using obsolete code that has been removed from Php. Use PDO

https://phpdelusions.net/pdo

[bowen73]

The easiest solution that comes to mind is to use an auto-increment integer id field for the records. Then do not use the "MM" before the id value (in the database). you can always append the "MM" when selecting the records or when displaying them.

 

When a user manually enters an ID you can choose whether to allow them to enter with or without the "MM" (or both) and just strip the MM before inserting.

 

Using an auto-increment ID field will work without any special logic. You can add a new integer ID that does not already exist and if you try to insert a new record without the ID a new one will be generate automatically that is +1 from the highest existing value.

 

HI, Thanks for the reply.  I do have asset_id  set as the primary key and auto increment but thought an ID couldnt be entered and needed to auto increment and didnt want to replace all the tags that we already have.

 

so if i understand what youve said, doing it that way, if i set int(5) and autofill zero, then the codes would run 00001, 00002, 00003 etc....  then if an entry was done manually i.e  00303  then it will continue to auto increment from 00003 to 00302 then skip to 00304?

 

how can i get it to display on the page if there is a 'generate' button, so if there isnt a current tag on it and a new one needs to be generated for it to +1 from 00003 to 00004 instead of getting the max value and showing 00303.  I need this to be show as i intend to get an asset label printer, so on the 'generated' code i can 'save & print' to get a label and save the into to the DB

 

thanks

[bowen73]

You are using obsolete code that has been removed from Php. Use PDO

https://phpdelusions.net/pdo

 

thanks for that i'll take a look.  ive just been using code examples from searches and not come across pdo.  i  thought the newest was mysqli instead on mysql.

[Jacques1]

Why is it so important for you to fill the gaps between the manually entered numbers? It would be far easier to just start after the last manual number (maybe even add a safety margin) and use plain old auto-incremented numbers without any skipping magic.

[bowen73]

Why is it so important for you to fill the gaps between the manually entered numbers? It would be far easier to just start after the last manual number (maybe even add a safety margin) and use plain old auto-incremented numbers without any skipping magic.

 

its just how i want it, and a little OCD too of not wanting gaps.  I may even just scrap the tags we have and start again from 1 just to keep my brain happy :-)

[Jacques1]

its just how i want it

 

Yeah, well, that's not really how making technical decisions works.

 

If this is an actual project, I strongly recommend you pick the simplest and most robust solution, that is, you either reset the numbering completely or skip to the last manually entered number.

Edited November 5, 2016 by Jacques1

[bowen73]

technical decisions come from requirements and my requirement comes from not wanting gaps.  

 

our asset tags comprise 5 numbers, when i roll the same system across our other regions i dont want the possibility of running out of numbers within a year or 2, so using blank spaces will help to aleviate this problem.  i'll prob go with auto increment on another column for asset and keep ID as primary so i can guarantee a unique line for when an item is EOL'd.

 

ive got this to 'fill the gaps'  which seams to give me the right results, so just need to throw it in with my coding.

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

include("include/connect.php");

$sql = "SELECT MIN(t1.asset_id + 1) AS nextID FROM asset_list t1 LEFT JOIN asset_list t2 ON t1.asset_id + 1 = t2.asset_id WHERE t2.asset_id IS NULL";
$result = mysqli_query($connect, $sql);
   
   while($row    = mysqli_fetch_assoc($result))
  {
	   echo "<li>" . sprintf("%05d", $row['nextID'] ). "</li>";
  }

$connect->close();
?>

[Barand]

 

i dont want the possibility of running out of numbers within a year or 2

If you add 100 new ones every second you are in danger of running out of numbers in only 2,924,712,086 years

[Psycho]

so if i understand what youve said, doing it that way, if i set int(5) and autofill zero, then the codes would run 00001, 00002, 00003 etc....  then if an entry was done manually i.e  00303  then it will continue to auto increment from 00003 to 00302 then skip to 00304?

 

No. Any auto-generated ID would be 1 more than the current highest number. I thought that was what you wanted, but apparently not. I agree with the others that what you are trying to achieve is not worthwhile. Requirements should be based on a legitimate business need, not because someone wants the data in the database to be neatly organized how they *think* it should be organized. What do you want to happen if an item is ever deleted? Should that ID be reused?

 

What you want can be achieved pretty easily, but I'm not going to provide a solution when I feel it is counter to good practices.

Edited November 7, 2016 by Psycho