Retreaving certain number of days back

[Karl Blessing]

I have made the switch from an ASP/Access site, to a PHP/MySQL site, the queries I've used thus far seem to work ok , all except for one. I have a query that pulls back records no older than 2 days from the latest record. The kind of syntax I have been using is :

[tt]
Postings where PostDate >= (Select Dateadd("d", -1, Max(PostDate)) From Postings)
[/tt]

above would pull back 1 days worth of records from the latest record. Is there a way I can do this in MySQL?

(also if you know PHP what method would I use if I wanted to pull back 2 days, but have PHP seperate the days when I hit a new date) Karl Blessing aka kb244{fastHACK}

 

[Guest_imported]

There is something call Date_Add() and Date_Sub(), where you can search for data within certain period of time. Syntax is like
SELECT DATE_SUB ("date input", INTERVAL # YEAR/MONTH/DAY)

so for you it's like SELECT DAT_SUB (CURRENT_DATE, INTERVAL 1 DAY);

 

[Karl Blessing]

currently I couldnt figure that out, I know that Date_sub would return a date for whatever the interval was before that date, BUT , that applies to all dates in the table. For example I cannot do something like.

Select * from News where PostDate > Date_Sub(PostDate, InterVal 1 Day);

I seen an example like this

[tt]
Select * from News Where TO_DAYS(NOW()) - TO_DAYS(PostDate) =< 30;
[/tt]

but I wanted to have it from the highest date but mySQL cannot use sub-selects like MS SQL and MS Access can, cuz I was trying to do either TO_DAYS(MAX(PostDate)) or even TO_DAYS((SELECT Max(PostDate) From News)) instead of TO_DAYS(NOW())

But I finally had to give up on a single query and just do it in a two query step , something like this (I'm recalling this from memory so syntax may not be correct)
(this is done in PHP)

$query="Select Max(PostDate) as MaxDate FROM News";
$result=msql_query($query);
$row=mysql_fetch_array($result);
$MaxDate=$row["MaxDate"];
mysql_release_result($result);

$query=&quot;Select * from News Where TO_DAYS(&quot;.$MaxDate.&quot;) - TO_DAYS(PostDate) <= 2 ORDER BY PostDate DESC&quot;;
....

and so forth.

It would be nice if MySQL had the grouping, or select within select for this kind of inner nesting purposes, I'm just fortunate that Join was supported (the above query would have been joined with another table before trying to find the two latest days)

Hopefully this thread will help others, I found TO_DAYS command in the MySQL manual, but had to use two queries to get it to do what I wanted. Karl Blessing aka kb244{fastHACK}