CODEWITHSUNDEEP
phpmysql
seoppc
seoppc

Reputation: 2824

MySQL ids by proper series

We are building an invoice management app and need help/suggestions with the following...

  1. Invoice should start with 1 and according to series like 1 2 3 4 5 6 7 8 not 1 3 4 7
  2. It should start with 1 again each financial year for example it should start with 1 again in 2012
  3. how to show all invoices older than febuary 2011 and newer than march 2010 means financial year

We want your suggestions to achieve this. thanks.

Upvotes: 2

Views: 76

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125304

Create the primary key as in heximal's answer. Then use this to insert a new id:

insert into my_table (id, year)
   values (
      coalesce (
      (
         select new_id = max(id) + 1
         from my_table
         where year = @year
      ), 1)
      , @year
   )

Upvotes: 1

Marc B
Marc B

Reputation: 360732

  1. The incrementing ID is trivial in MySQL. A simple auto_Increment primary key column does the trick.
  2. Having the number wrap back to 1 each year can't be done with vanilla SQL without triggers and/or client-side involvement.
  3. The fiscal year business is more of a client-side issue. Simply store a "created on" timestamp with each record and then decide on the filtering rules in the client, then you simply do select ... where date_of_record between $start_of_period and $end_of_period.

Upvotes: 1

heximal
heximal

Reputation: 10517

you need compound primary key. eg:

year int
invoice_id int

Upvotes: 3

Related Questions