I want to remove a "DateJoined" field from a membership database. I would like to hold this information in the userid field. That is, I want to set MYSQL (using a trigger?) such that when a new user joins the user id assigned will be in the format: yydddxxx where yy = year, ddd = day of the year, xxx = index of user for that day.
I know how to generate the 'yyddd' portion. My question is: How can I set up the database to reset the 'userid' (AUTO_INCREMENT field) to begin counting afresh when the date changes?
Today is - 11321 (321st day of 2011)
At midnight the 'userid' field should reset to begin counting at 11322000 (322nd day of 2011) such that the first user will be assigned userid = 11322000