[SOLVED] MySQL and dealing with dates...

[me1000]

Hello,

 

The user will have the ability to determine the expiration period of an entry. (1 week, 2 weeks, 1 month, 2 months, 3 months, 6 months)

 

The problem is I am unsure of how to filter the results to only show the entries that are not past the expiration.

 

Is there an easy SQL or PHP command to convert dates to make them much easier to work with?

 

Thanks,

 

 

[AndyB]

If want to store useful dates in a database, also use the ISO standard format - yyyy-mm-dd. That allows you to sort in calendar order, do 'math', and you can use whatever format you want to display the date as needed.

[me1000]

ok, so basically im out of luck making it any easier on me.

At first it doesnt seem that hard, however the more I think about it, what if in those 7 days the month changes? I have to write that into my code. Now what happens if the year also changes within that 7 days too?

 

is there no easy(er) way to manage this?

[AndyB]

Look at the use of strtotime() in example #1 at http://ca.php.net/manual/en/function.time.php

 

Using yyyy-mm-dd format allows you to perform all the manipulation of dates that you'll ever need without worry about what if the month changes, what if the year changes, etc.

[me1000]

ok thank you,

 

I believe that was the function I was looking for.

 

[me1000]

ok,

So when I attempt to work with this, im not having a lot of luck

echo date("2008-05-26", strtotime('+1 year'));

 

from what I understand this should return

"2009-05-26" right?

 

Im getting the exact same date as I entered into it...

"2008-05-26"

 

im not sure what the problem is here though...

[BlueSkyIS]

<?php
echo date("Y-m-j", strtotime('+1 year'));
?>

[me1000]

So this will only work if I use PHP generated dates?

 

this could be a problem, because the date will be stored in the database, and pulled out when it needs to be calculated.

 

 

 

EDIT: im just going to mark this topic as solved, because for the moment I can just do the math on the dates before they get stored in the DB...

 

[AndyB]

So this will only work if I use PHP generated dates?

 

No. Suppose you retrieve the date from the database as the variable named $the_date.

 

list($y,$m,$d) = explode("-", $the_date); // gets y, m, and d from the stored date
$some_date = date("Y-m-d", mktime(0,0,0,$m,$d,$y+1)); // $some_date is one year later

 

Does that shed some light on things?