Auto Increment Field how to generate a field that increments since the last access

[villajay 0]

Still learning php and mysql so hoefully this is an easy one. so the procedure goes as follows: user enters a new widget into the database, and the system automatically assigns an inventory number in the form ##-###. the first 2 numbers is the last 2 digits of the year, and the last 3 is a number that increments. auto-increment will not work since next year, it will have to start at 1. TIA!

[sonesay 7]

So you wanted to have records of 08-001, 08-002, 08-001... and then 09-001, 09-002... ? If that is the case then you would need to use the date function in php to return the year (last two digits) and add it onto the -### part before inserting a new record. To get the -### part you would need to query and count the number of records for that year first and generate the next one appropriately. That is the simplest way I can think of doing it. Sorry I cannot provide any code at this time since I am working on something at the moment.

[jlhaslip 4]

I would be tempted to do it differently than the above suggestion.Use a full auto increment id for the entry, then a Date field for the year and then a combination Date-id field as the 'key' for the entry.From past experience with a large Equipment Database, I suggest to you that having the "inventory number" be significant to the particular inventory item is not a good choice to make. Particularly if you attempt to limit the digits in an Inventory number. Trust me.You are better off assigning the atomic parts and building up from there.Example: Database contains: Id, Group number, Category number, size, colour, date and other identifying informationCreate another element in the Database to define the identifying number based on the sub-parts, ie: grp-cat-IdForcing an auto-incrementing number will cause havoc at some point. Auto-incrementing ID's are best left to the machine/software to deal with.