[RESOLVED] Math with date ()?

dapuxter

I'm trying to limit the results of a query to database entries entered less than a week ago. Here is my select statement:

$result = mysql_query("SELECT * FROM `outage` where status='resolved' or status='clear' or status='cleared' order by ID");

In order to get my cut off date, I use this bit:

$timestamp = time();
$newstamp = $timestamp-604800;

That subtracts a week from the current date, giving me the date of one week ago. The problem I'm having is inserting that information into my select statement. My date is stored in my table as just mm/dd. How can I do the comparison in the select statement to get a valid result? I've tried this:

$timestamp = time();
$newstamp = $timestamp-604800;
$date = date("m/d", $newstamp);
$result = mysql_query("SELECT * FROM `outage` where status='resolved' or status='clear' or status='cleared' and date >= $date order by ID");

When I just echo $date, I get a result of 06/05, but the table still shows results with dates older than that. I've tried backticks around date, single and double quotes around $date, and get the same results. Am I even doing the math part right?

bpat1434

Well how is the date stored in the database? What's the column type? If it's 0000-00-00 00:00:00 (DATETIME) then look into using DATE_SUB() (a mySQL function) to do it. Your query would look something like:

SELECT * 
FROM `outage` 
WHERE status='resolved' 
    OR status='clear' 
    OR status='cleared' 
    AND date >= SUBDATE(date, INTERVAL 1 weeks)
ORDER BY ID

Hope that helps.

dapuxter

Column type in my table is varchar. I tried changing it to date, but that forces me to enter my dates in the specific format of YYYY-MM-DD, where I want to use MM/DD. I know that I can take the user input, change it to fit that format, etc, but I was hoping to be able to find a shortcut, so to speak.

I suppose I should go ahead and think long-term and make provisions for the year as well, though. Thanks!