to_char function in php

[yllai]

in my system, i have table file with fields id, file_name, file_desc, file_date added, retention_period, dispost_date.

Now for my id, default is set as automatic increase index as 1, 2, 3...but i want ti set the id in 5 digits start with 0. basically id for first record is 1, i want to set it as 00001. How can i do it? I have tried to set id as 1,2,3..in the database but when i query from the database, i use the below statement to query, make the id display as 00001, but error message displayed. below is my statement. Any idea?

$result=sql_query('SELECT TO_CHAR(id,'09999'),file_name,file_desc,file_date_add,retention_period,dispost_date WHERE id="$id"');

Basically, dispost_date=file_date_add + retention_period. if I want every time i query all the record, it will search out and highlight which file should be dispost on the same day i querying the data.How can I do it? and example?

thanks for your consideration.

[bsterz]

If I understand the question, you could do:

$result=sql_query('SELECT LPAD(id, 5,'0') ,file_name,file_desc,file_date_add,retention_period,dispost_date WHERE id="$id"');

[dev.mysql.com...]

Hope this helps,

Bill

[yllai]

I will try it.

How about my second question? anyone have idea of it? that is releated to the date.

[bsterz]

Sorry, I don't fully understand the 2nd question. Can you explain a little further?

Thanks,

Bill

[yllai]

the value that i will going to insert into field 'dispost_date' in table 'file' is the result of adding value $file_date_add with $retention_period which is entered by user.

$file_date_add (datatype is datetime) is the date and time of when the record added..system get this value from now(). $retention_period (datatype is int) means after how many year the file will be dispost. if user create a file record on 03-07-2004 (value for $file_date_add) and user enter value 2 for $retention_period...then system should insert value 03-07-2006 for $dispost_date.

When i query all the record from database on 03-07-2006, the above example record should be highlighted.

Any idea?

[bsterz]

I think I understand - thanks.

SELECT DATE_ADD(now(), INTERVAL 3 YEAR);

With your variable:
SELECT DATE_ADD(now(), INTERVAL $retention_period YEAR);

[dev.mysql.com...]

Hope this helps,

Bill