php/mysql date

[tuananh87vn]

hi,
I have some problems related to date type:
In mysql, I created a table with some fields of date type, so how can I use php to insert date value in into database (then db will store date format, instead of breaking it into separate smaller attributes: date, month, year...with int type like i did before- I don't like it)

Then when I call out these value, how can I perform calculation (in php, of course). For example: how many days from 13/3/08 to 31/4/08 or from 20/5/2008 to 10/6/2008

thanks

[rpnew]

hi,
I have some problems related to date type:
In mysql, I created a table with some fields of date type, so how can I use php to insert date value in into database (then db will store date format, instead of breaking it into separate smaller attributes: date, month, year...with int type like i did before- I don't like it)

Then when I call out these value, how can I perform calculation (in php, of course). For example: how many days from 13/3/08 to 31/4/08 or from 20/5/2008 to 10/6/2008

thanks

Hi,
There is datatype date and datetime in MySql in which you can store your date and date with timestamp respectively. So you can define that column in database and using date function in PHP you can insert it into database.

Not much sure about calculation regarding dates...

Regards,
RP

[ronverdonk]

When possible try to calculate the date and time differences in your MySQL select, there are many functions in MySQL that allow that.

Is it really necessary to calculate these values in PHP, i.e. outside MySQL?

Ronald

[tuananh87vn]

I've found way to insert and print out the date-type data in 'date-month-year' (insead of 'Year-month-date' form in mysql) by using strtotime()function, however still dont know how to subtract two dates.

Hmm, actually I may not care whether it's done inside or outside mySQL. Given a case that I insert job information into database, which includes add_date and expire_date. so I need to calculate the substraction of the two date, then when the result is equal or greater than 0 (i.e the job is expired) then the job status will change from available to expired. If this can hardly be done in php so how to do it in form of sql query then using php's mysql_query() to run it.

The 2nd case is that when a user logs out of a system, that moment will be stored in db as last login time, and now I want to query all the jobs which are added from the last login time until the moment the user logs in again (i.e all new added jobs). Can it be like "SELECT * FROM job_table WHERE add_date > lastlogin AND add_date <= $now" or else?

[rpnew]

I've found way to insert and print out the date-type data in 'date-month-year' (insead of 'Year-month-date' form in mysql) by using strtotime()function, however still dont know how to subtract two dates.

Hmm, actually I may not care whether it's done inside or outside mySQL. Given a case that I insert job information into database, which includes add_date and expire_date. so I need to calculate the substraction of the two date, then when the result is equal or greater than 0 (i.e the job is expired) then the job status will change from available to expired. If this can hardly be done in php so how to do it in form of sql query then using php's mysql_query() to run it.

The 2nd case is that when a user logs out of a system, that moment will be stored in db as last login time, and now I want to query all the jobs which are added from the last login time until the moment the user logs in again (i.e all new added jobs). Can it be like "SELECT * FROM job_table WHERE add_date > lastlogin AND add_date <= $now" or else?

Hi,
You can use following...

select * from table where date between start_date and end_date;
 

Some formating might be needed with date.. not sure though..

Regards,
RP