Page 1 of 1

How to autoincrement a particular format(DD-MMM) in Php Mysq

Posted: Wed Jul 29, 2009 9:46 pm
by aditi_19
Hi,

I have a problem. I am storing projectid (varchar) as a field in this format :- DD-NNN where DD is the last two digits of the year and NNN is the next project number within that year (i.e. 09-500 is the 500th project in year 2009). The user has the ability to modify the automatically generated projectid.

Plus, there is 1 internal only project for year 2009 (09-000). This project should automatically be created at the start of a new year.

How can this be done? Please help me out !

Thanks

Re: How to autoincrement a particular format(DD-MMM) in Php Mysq

Posted: Wed Jul 29, 2009 10:06 pm
by requinix
If you never delete records from this table then you can
- Insert a new row
- Get the auto_increment ID for that row
- Find the row with that ID-1
- Get the project ID and add one

If anyone wants to suggest "look up the highest value and add one" then they aren't familiar with a little thing called concurrency.

Re: How to autoincrement a particular format(DD-MMM) in Php Mysq

Posted: Wed Jul 29, 2009 10:11 pm
by aditi_19
But the ID needs to be set to zero once a new year starts. How can this happen?

Re: How to autoincrement a particular format(DD-MMM) in Php Mysq

Posted: Wed Jul 29, 2009 11:43 pm
by requinix
At the beginning of the year you automatically create the 000 record.

For normal records, figure out the latest project ID from the current year. If there isn't one use 001, otherwise use that value plus one.