Mysql autoincrement column that resets every month

There are situations when we need an autoincrement field to reset every month or so.


Example :

Invoice Numbers :

Month of october

2011-10-01
2011-10-02
...
...
...
2011-10-800

Month of november

2011-11-01
2011-11-02
...
...

and so on.

Groupwise autoincrement
P1 , P2 , Q1 , Q2 , Q3 , Q4 , P3 , R1 , P4 ...

Myisam table has a feature where , if a primary key contains 2 columns where one is an autoincrement and other is a varchar , then for each value of the text column the autoincrement column will create separate sequence of autoincrement numbers.

Table

CREATE TABLE IF NOT EXISTS `vt_sequences` (
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`type`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

In the above table type and id columns form the primary key. If the following insert is made :

INSERT INTO `vt_sequences` (`type`) VALUES
('A'),
('A'),
('B'),
('B'),
('A');

The result should be :

A 1 2011-10-22 15:29:04
A 2 2011-10-22 15:29:04
B 1 2011-10-22 15:29:04
B 2 2011-10-22 15:29:04
A 3 2011-10-22 15:29:04

So the id column has a 1 for A and B separately. So now if we want autoincrement numbers resetting every month we could insert 2011-10 and 2011-11 in the type field and the autoincrement numbers would be independant.

Another very useful thing about this technique is that if a particular entry is deleted , say A 3 is deleted , then next time when A is insert 3 would be reused , so autoincrement numbers get reused.

Helper Functions

We can write some easy to use helper functions to manage multiple groups of autoincrements very easily. The first function will help us to get the next autoincrement for a particular group.

/**
	Get a sequence id
*/
function get_sequence( $type )
{
	//Database class object
        static $db = false;
	
	if($db == false)
	{
		$db = load_class('Database');
	}
		
	if(! $db->query("INSERT INTO vt_sequences(type , id ) VALUES( '$type' , NULL )") )
	{
		die( 'Sequence Generation failed' );
	}
	
	return $db->get_last_insert_id();
}

The function get_last_insert_id is nothing but output of "SELECT LAST_INSERT_ID();" query.

The next function will delete a particular id value for a group.

/**
	Delete a sequence id
*/
function del_sequence($type , $id)
{
	static $db = false;
	
	if($db == false)
	{
		$db = load_class('Database');
	}
	
	$db->query("DELETE FROM vt_sequences WHERE type = '$type' AND id = '$id'");
        return true;
}






A particular id should always be deleted , if not used , so that it is reused in the next call to get_sequence.

Now lets see how the functions perform :

get_sequence('2011-10'); //1
get_sequence('2011-10'); //2
get_sequence('2011-10'); //3
get_sequence('2011-10'); //4

get_sequence('2011-11'); //1
get_sequence('2011-11'); //2
get_sequence('2011-11'); //3

get_sequence('2011-10'); //5

//Delete a sequence
del_sequence('2011-10' , 5);

//Get it back
get_sequence('2011-10'); //5 - reused again !!

The get_sequence method inserts the type and NULL for the id , and this generates the next autoincrement number.
$db->get_last_insert_id(); could be your implementation of the sql query LAST_INSERT_ID().

References :
1. http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Last Updated On : 22nd October 2011

Subscribe to get updates delivered to your inbox

1 Comment + Add Comment

  • It’s always better to keep the business logic to your PHP code, and not have it, even if it’s that simple, in your MySQL database.

Leave a comment