how can i make sn number start from 1 when the new year is start.

[khs555]

Hi evryone ,
I'm working in a program for storing inbox and outbox documentsand
and in mysql the sn number is the primary key.
the number must be as 01/2006 the next nuber will be 02/2006
my problem is how can i make this sequance number start from 1 when the year will come 2007 and the first number will be 01/2007 and also for the next years>>

here example for may data:
[table][tr][td][color=red]SN[/color][/td][td][color=red]Subject[/color][/td][td][color=red]Type[/color][/td][/tr][tr][td]215/2006[/td][td]last in year 2006[/td][td]inbox[/td][/tr][tr][td]01/2007[/td][td]first in year 2007[/td][td]outbox[/td][/tr][/table]

Any help regarding this will be highly appreciated.

[taith]

[code]
echo date(z).'/'.date(Y);
[/code]

date(z) counts from 0-365 (includes leap year)

[khs555]

[quote author=taith link=topic=116384.msg474147#msg474147 date=1164569659]
[code]
echo date(z).'/'.date(Y);
[/code]

date(z) counts from 0-365 (includes leap year)
[/quote]

Hi taith, thank you
but the problem is the number of documents is more than 365.
in each day my I add 20 document at once.
so thats mean the number will be more than 1000 document/year

[Barand]

If you have over 1000/year then 01 won't cut it, you'll need 0001.

Define the PK as (year, sn) and the auto_increment should commence at 1 for each new year value.

[code]
CREATE TABLE documents (
  year int(4) ,
  sn  int(4) not null auto_increment zerofill,
  ... ,
  ... ,
  PRIMARY KEY (year, sn)
);
[/code]

[khs555]

thanks alot Barand

but can we put it in one feild. Imean can we store it like:
0001/2006
0002/2006
0003/2006
in one feild?

[Barand]

Not if you want to auto_increment the serail number.

You can do this however

SELECT CONCAT(sn, '/', year) as docsn FROM documents

to get in the format you want

[khs555]

Thanks Barand  I'm very happy.
I wish to phpfreaks light futuer.
go on.