[RESOLVED] Stuck on selecting newest item by day of the week.

[dmacman1962]

Hi All,

I have a news page that I want to have it select the most current items by the day of the week. So if today is Tuesday, I only want all articles from last Wednesday to today.

When the article is entered into the database, I use the Date and TIME stamp, ie (2006-08-11 09:11:43) and I let the administrator assign the listing order and set the article to active.

Note, some days there may be one article that day others 5, so I cant use a LIMIT 7 (the # of days in a week) approach, since that would list the last 7 article and there may be 18 that week.

I can check the day by the following...

$dayoftheweek = date("l");
echo '<br> The day is ' . $dayoftheweek;

I know this is farily straight forward, but my searchs have not shown anything similar for me to apply.

Thanks in advance,

Don

[dmacman1962]

I resolved this...

///set the date lookup for the last 7 days.
$pf_time = strtotime("-7 days");
//format the date using the timestamp generated
$pf_date = date("Y-m-d", $pf_time);


$sql = "SELECT * FROM daily_events WHERE Active='1' AND Date>='$pf_date' ORDER BY EventOrder ASC";

Thanks anyway.

Don

[Installer]

If you want to do it in the query, something like this should work:

$query = "SELECT *, 
            DATE(date_time_column) AS date, 
            DATE_FORMAT(date_time_column, '%W') AS dow 
          FROM test 
          WHERE date_time_column > DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
          ORDER BY date";