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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
aditi_19
Forum Newbie
Posts: 23
Joined: Sun Jun 21, 2009 3:09 pm

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

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
aditi_19
Forum Newbie
Posts: 23
Joined: Sun Jun 21, 2009 3:09 pm

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

Post by aditi_19 »

But the ID needs to be set to zero once a new year starts. How can this happen?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
Post Reply