Generating Job Number with date() and auto-increment

holstead

I'm try'n to give each project for my company a job# in the following format:

yy#####

Format: Last 2 digits of current year followed by a 5 digit number starting with 00001.

date('y') generates the last 2 of the current year.

My problem is when it turns 2005, how do I start the auto_increment back to 1 and still preserve the previous years #'s.

I'm leaning towards storing 'jobno' as a seperate field from 'id' (auto_increment) and somehow incrementing from the previous job#.

Any better ideas? Suggestions?

Thanks!

devinemke

you should read this thread. it deals with almost the exact problem you have. i wrote an example function there to calculate job numbers like you are trying to do.

holstead

I'm still left with the problem of reseting the last 5 digits to 00001 at the first of every year.

example:
Job numbers for 2004 look like:
040001, 040002, 040003, etc...

2005:
050001,050002, 050003.

devinemke

did you read that thread i refered you to? as i said, i had written a function to calculate job#'s exactly like you are trying to do. i will repost it again. you may have to tweak this code to suit your exact data structure.

function get_invoice_number($job_id) 
{ 
	$result = mysql_query('SELECT datetime, YEAR(datetime) AS year FROM jobs WHERE job_id = ' . $job_id);
	$row = mysql_fetch_assoc($result);
	$result = mysql_query("SELECT COUNT(*) AS count FROM jobs WHERE YEAR(datetime) = '" . $row['year'] . "' AND datetime >= '" . row['datetime'] . "'");
	$count = mysql_result($result, 0);
	$invoice_number = $row['year'] . sprintf('%05s', $count);

return $invoice_number; 
}

Roger_Ramjet

The 'standard' db solution for this is to create your own 'autoincrement'. Just have a table with one field and one row: index = the last number used. You add one to that number, use it and update the number. At year end you just reset the index to 1.

This requires that you use database transactions with your queries when you do this to ensure that updates do not overlap with the index being used twice.

The reason that this is preferable to counting the actual entries in the table to get the next number is that any databse corruption or any row deletion will result in you calculating the wrong number and issuing the same job number twice.

When MySQL includes Triggers (ver 5.1) then you can use a trigger to update the index. When a row is added to your jobs, the trigger reads the job number, strips the year digits and updates the index accordingly. You will still need to use transactions to ensure that two updates do not overlap.