[mcrob]

Hello!

I would like to do a php date / time validation. Basically I want to enter the
day, month, year in the database, then have a code that does the calculation of how many days it has been since that stored date. So for example, A new user registered on april 10, 2008 on a website, and now todays date is april 20, 2008 for example, meaning this user has been registered for 10 days. I want to be able to find out how many days it has been since that user has been registered.

A user added a product to there shopping cart on April 28, 2008 and today's date is May 2, 2008, it has been 4 days and the entered product in the shopping cart has expired, I want to make a code where it calculated the amount of days it has been before it gets deleted.

I hope this makes sense...

[mab]

You can use the mysql datediff() function to find the number of days between a DATE or DATETIME data type in your database and any other date or datetime value.

Ref: http://dev.mysql.com/doc/refman/5.0/...ction_datediff

[mcrob]

This is what I was thinking, I was wondering if I could do this..

PHP Code:

// current date: april 20, 2008

$date = mktime(0,0,0, date("m"), date("d"), date("Y"));

//outputs this: 1208664000 

The current date gets entered into the database. Four days later the
product is now expired for example

PHP Code:

// four days later the $date will output this: 1209009600 

Could I make it in the database so it does
SELECT * from date WHERE entered_date < '$date' (april 24, 2008
date, outputs 1209009600) where the entered date was april 20, 2008
1209009600)

[mab]

Ummm. Your statement of what you want changed from "find the number of days between dates" to "select the rows more than 4 days ago."

Note: The following assumes that you are using a mysql DATE data type.

To select rows more than 4 days ago, just form the date and compare it in your query -

Code:

SELECT * from date WHERE entered_date < DATE_SUB(CURDATE(),INTERVAL 4 DAY)

The reason I mentioned using a DATE data type is because a Unix timestamp requires a conversion or several slow php statements at some point to use it for any purpose other than to sort in ascending or descending order. A DATE data type allows you to use about 20-30 mysql functions directly in a query and you can output it in any format you want using the mysql DATE_FORMAT() function.

You can use a mysql FROM_UNIXTIME() function in your query to produce a standard DATE value from a Unix timestamp, but this also slows everything down over just using a DATE column directly.

[mcrob]

wow Im going to try this! This seems like an interesting function.

PHP Code:

SELECT * from date WHERE entered_date < DATE_SUB(CURDATE(),INTERVAL 4 DAY) 

On there it shows "interval 4 day", what kind of query could I use in that area?

could I do this?

PHP Code:

$currentdate = date("m d Y");

SELECT * from date WHERE entered_date < DATE_SUB(CURDATE(),"$currentdate") 

[mab]

The link in post #2 in this thread takes you to the mysql manual section where all the date and time functions are documented.

[mcrob]

this works great!!! I have some questions tho.

Yes I learned in order for this function to work, the data type in the table has to be DATE and it is formatted
year,month,day.

I made it so the date is 2008-04-16 as a test and I did this code

PHP Code:

SELECT * from test WHERE dateadded < DATE_SUB(CURDATE(),INTERVAL 4 DAY) 

I did the practice test in phpmyadmin. from april 16 2008 to april 20, 2008 is 4 days. why is it that it wouldnt show the results if it was the past 4 days but if I changed it to interval 3 day it would show? It doesnt make much sense to me

[mab]

Because the test is less than <, which translates to more than 4 days ago. If you want 4 or more days ago, use less than or equal to <=

[mcrob]

perfect!!!! Learned something new now.. thank you!!

[mcrob]

sorry another question since Im doing some studying on these functions.

Instead Im trying out the date_add function. I only wanted to pull up the records for 1 day later this is what I tried.

PHP Code:

SELECT * from test WHERE dateadded > DATE_ADD(CURDATE(),INTERVAL 1 DAY) 

but it still shows....

April 29, 2008 and not April 21, 2008