bowen73 Posted November 4, 2016 Share Posted November 4, 2016 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 4, 2016 Share Posted November 4, 2016 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. Quote Link to comment Share on other sites More sharing options...
benanamen Posted November 4, 2016 Share Posted November 4, 2016 You are using obsolete code that has been removed from Php. Use PDO https://phpdelusions.net/pdo Quote Link to comment Share on other sites More sharing options...
bowen73 Posted November 4, 2016 Author Share Posted November 4, 2016 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 Quote Link to comment Share on other sites More sharing options...
bowen73 Posted November 4, 2016 Author Share Posted November 4, 2016 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. Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted November 4, 2016 Share Posted November 4, 2016 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. Quote Link to comment Share on other sites More sharing options...
bowen73 Posted November 5, 2016 Author Share Posted November 5, 2016 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 :-) Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted November 5, 2016 Share Posted November 5, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
bowen73 Posted November 6, 2016 Author Share Posted November 6, 2016 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(); ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted November 6, 2016 Share Posted November 6, 2016 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 7, 2016 Share Posted November 7, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.