I have a form and a mysql table name parcel. I need to insert tracking number in the table which is personally generated based on year,category and status. But the tracking number must auto increment. For example :

category has two values : D and L

status has four values : 01, 02, 03, 04

number : will be increment

year (based on current date) :14

for example I insert category: D, status: 02, and year.

so the tracking number will be : (category)/(status)/(here will be number with increment)/(year)

this will be tracking number : D/02/01/14

But the problem is the increment number will be based on the category.

for example if the category is D,then the number will start with 1 and will be increment.

Same goes with category L, it will start with 1 and will increment it.

So the tracking number for D will be D/02/01/14, D/02/02/14, D/02/03/14... and so on.

the tracking number for L will be L/02/01/14, L/02/02/14, L/02/03/14, L/02/04/14.. and so on.

SO, I cant use auto_increment for the number field in my table because the number will be repeated because the category.

Please help me.