Custom auto incremental field

[anirban1087]

Hi, I am working on a Inventory management system. No I would like to have a custom bill no, which will be changed on each month, i.e. the first bill of March, 2011 will have a no like "Mar/2011/01", the next will be "Mar/2011/02" and so on, until it is March, 2011. On April 2011 it will start again from one, as "Apr/2011/01"..... and so on. I am using Mysql & PHP for the project. How to achieve this???

[pritaeas]

If you have a timestamp column in your table, then you can do the following, just before your insert (or as part of your insert query, or as an insert trigger):

SELECT CONCAT(
  DATE_FORMAT(NOW(), '%b/%Y/'), 
  (
    SELECT 1 + COUNT(*) 
    FROM bills 
    WHERE YEAR(`timestamp`) = YEAR(NOW()) 
    AND MONTH(`timestamp`) = MONTH(NOW())
  )
)

This will return your next bill number. Note that this will fail should you start deleting bills, which is normally not allowed.

[smantscheff]

I suggest you use something like pritaeas' expression in a BEFORE INSERT trigger and store it in a invoice_number field. Instead of using the count(*) function I'd rather check for the highest number in this month and store it in a separate field. In your trigger BEFORE INSERT:

SET new.invoice_number = 0 + (SELECT max(invoice_number FROM bills WHERE YEAR(timestamp) = YEAR(NOW()) AND MONTH(timestamp) = MONTH(NOW()))

[anirban1087]

Thanks to all. Its working for me.