MySQL Field AUTO_INCREMENT according to YEAR

Question

I have a table for bills and I need to create the ID field (Primary Key) with this format:

YYYYXXXX -> YEAR+INT(4) Auto_increment

For example: 20150000, 20150001, ...., 20159999

And it should close at the end of the year and start again at zero in the next year: 20160000, 20160001, ... with the posibility to change it manually too.

Maybe some of you could help me as I'm new to mysql and php.

Thanks

Answer 1

You need to update auto increment counter to achieve this. Sample code attached.

ALTER TABLE  `TABLE_NAME` AUTO_INCREMENT =2015000;

However you need to make sure to update this once one the beginning of 2016 again like : ALTER TABLE TABLE_NAME AUTO_INCREMENT =2016000;

You can make a CRON job & add this conditional logic there :

alter.php:
<?php
 $conn = mysql_connect(.....);
 $selDb = mysql_select_db(....);
 $sql = 'ALTER TABLE  `TABLE_NAME` AUTO_INCREMENT ='.date("Y").'000';
 mysql_execute($sql);
?>

CRON:
1 1 1 1  * php alter.php