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???

6 Years
Discussion Span
Last Post by anirban1087

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):

  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.

Edited by pritaeas: n/a


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()))

Edited by smantscheff: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.