Getting the last group of entries by date

[lorax]

Let's say I publish news and for the most part it's a daily thing. Along with the news, I want to let folks know when the entries were compiled and published (I track each entry with a datetime stamp in MySQL). Piece o' cake so far.

Now, on occassion, I'm waylaid on some faraway beach with no access to the internet and I can't update the news. But I don't know for how long. SO I want the pubdate of the articles to remain displayed even if it's a week or so old. Still easy.

The part I'm having a brain fart over is how best to determine what's the freshest group of news entries from the db in the most efficient manner. I'm leaning towards using

GROUP BY TO_DAYS(pubdate)
ORDER BY pubdate DESC

which I believe will organize the entries by day of the pubdate in descending order but then I need to create a WHERE clause that will extract the latest group without knowing what the pubdate of the group is.

I get the feeling it's got to be a simple answer but I can't see it at this moment. Or worse, I'm headed down the wrong tracks...

[brucec]

I had this same dilemna and I solved it in ASP, but PHP will work the same way.

What I did was "cycle" the news each month and it works well.

Have a couple of IF statements and extract the month or week number using the PHP DATE function from today's date (whatever today is).

Your IF statements will compare the week number to todays date and you can extract it from your MySQL database and say that the news as published on todays date.

For repeat traffic this may not be practical since you are on the beach and not updating the latest current event news. So, make sure you have an ample number of news records in your database to fool the repeat traffic into believing that they never read the news before.

I hope this helps

[Netizen]

I think this should work:

SELECT something
FROM thetable
WHERE TO_DAYS(pubdate)=TO_DAYS(max(pubdate))
ORDER BY pubdate DESC

[not tried it myself]