We are building a table (payment_slip) with following structure...

+------+------+-------------+-------------+----------+---------------------+
| id   |      | recipt_code | prodcuct    | user     | dnt                 |
+------+------+-------------+-------------+----------+---------------------+
| 4    |      | 2011/1      | ABC         | tester   | 2011-02-17 23:36:12 |
+------+------+-------------+-------------+----------+---------------------+

And we want to add field recipt_code by using auto increment as it should be unique and by proper series like 2011/1 2011/2 2011/3 2011/4, so what is the best way to achieve this? please help, thanks.

EDIT 2:

Try this code. New 'id' generation is incapsulated in a function generate_recipt_code , so you could call this function from your INSERT statement directly, in the script or in the php.

CREATE TABLE payment_slip(
  id INT(11) NOT NULL AUTO_INCREMENT,
  recipt_code VARCHAR(255) DEFAULT NULL,
  prodcuct VARCHAR(255) DEFAULT NULL,
  user VARCHAR(255) DEFAULT NULL,
  dnt DATETIME DEFAULT NULL,
  PRIMARY KEY (id)
);

DELIMITER $$

CREATE FUNCTION generate_recipt_code()
RETURNS VARCHAR(255) CHARSET latin1
BEGIN
  DECLARE new_recipt_code VARCHAR(255);

  SET @date = NOW();
  SET @year = IF(MONTH(@date) < 4, YEAR(@date) - 1, YEAR(@date));
  SELECT
    COALESCE(
    CONCAT(@year, '/', MAX(SUBSTRING_INDEX(recipt_code, '/', -1) * 1) + 1),
    CONCAT(@year, '/1')
    )
  INTO
    new_recipt_code
  FROM
    payment_slip
  WHERE
    SUBSTRING_INDEX(recipt_code, '/', 1) = @year;

  RETURN new_recipt_code;
END
$$

DELIMITER ;

INSERT INTO payment_slip VALUES(NULL, generate_recipt_code(), 'ABC', 'tester', '2011-02-17 23:36:12');

PHP code:

$mysqli = new mysqli('host_name', 'user_name', 'user_password', 'db_name', '3306');

$prodcuct = 'product1';
$user = 'user1';
$dnt = '2011-04-01 12:00:00';

$sql="INSERT INTO payment_slip VALUES(NULL, generate_recipt_code(), '$prodcuct', '$user', '$dnt')";
$mysqli->query($sql);

more