[SOLVED] MySQL dates - checking less than more than a date i

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

MySQL dates - checking less than more than a date in PHP?

Post by mjseaden »

Dear All,

I have a date stored in a MySQL table in standard MySQL 'date' format.

I also have a PHP string stored in DD/MM/YYYY format. Is there any way I can check whether this date is 'more' or 'less' than the date stored in the MySQL table? Or for example how many days away it is from this date?

Many thanks,

Mark
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

You might just be able to use a standard less than comparison but if that doesn't work you may need to make unix time stamps to determine the right dates/times.
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

To get the UNIX timestamp (the number of seconds since 1st Jan 1970 I think), you need to use the time() function. Can you tell me how I'd use this to make the comparison?

Many thanks

Mark
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

yeah, easy, just use the less than/more than comparison

Code: Select all

SELECT * FROM some_table WHERE old_date > new_date
Mark
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Bech100,

Thanks for your suggestion. Rather than select rows in a table via a MySQL query, I need to make a comparative 'IF' statement - so I need the equivalent of

Code: Select all

if ( $thisdate > $thatdate )
{
    /* execute code */
}
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

..in that case, you will need to convert each of the dates into a timestamp and compare them.

mark
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

^

Code: Select all

SELECT UNIX_TIMESTAMP( `date` ) `date` FROM `table`
then
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Hi feyd,

Thanks for the help - can you clarify? What would I use to extract the UNIX_TIMESTAMP from the query? Then I would use time() to compare with the present date?

Many thanks

Mark
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

try the query..
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Hi feyd,

I've tried this for the date cell 'CustomerViewFrom':

Code: Select all

SELECT UNIX_TIME_STAMP('CustomerViewFrom') CustomerViewFrom FROM Customers
I get this output from $row['CustomerViewFrom']...

Code: Select all

SELECT UNIX_TIME_STAMP('CustomerViewFrom') CustomerViewFrom FROM Customers
Which is the same as the query.

Many thanks

Mark
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

how'd you run the query?
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Sorry my mistake - I asked my error output to display the query, not the error. It's generating the following error:

Code: Select all

Error performing MySQL query - You have an error in your SQL syntax near '('CustomerViewFrom') CustomerViewFrom FROM Customers' at line 1
Cheers

Mark
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you may want to use UNIX_TIMESTAMP instead of UNIX_TIME_STAMP
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Hi feyd - the date I am looking at is 07/07/2004, however the query seems to be reporting a timestamp of 0.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what's the code you have for it?

My local test of this returned:
[CustomerViewFrom] => 1089183600
Post Reply