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

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!

Comment/Reply (w/o sign-up)

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.

Comment/Reply (w/o sign-up)

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 information
Create another element in the Database to define the identifying number based on the sub-parts, ie: grp-cat-Id

Forcing an auto-incrementing number will cause havoc at some point. Auto-incrementing ID's are best left to the machine/software to deal with.

Comment/Reply (w/o sign-up)