Results grouped by date?

jung

On 'nuke' boards they have a module that will display results grouped by date... I believe this is from ONE select...

It would look like this...

jan 28
article
article
etc.

jan 25
article

jan24
article
article
article

Separate queries by date seems the wrong way/bloated way to go...

help?

Roger_Ramjet

The page layout would come from the way the results are processed and the html formatted in php. Getting the results into a suitable order for this is just a case of using the correct ORDER BY clause

SELECT * FROM articles ORDER BY article_date, article_number

jung

Thanks Roger...

I do get the articles and order them by date.... (it's a datetime field)

What want to do is list them GROUPED under a date....

Roger_Ramjet

So you need to put a condition into your while loop that processes the results. Before starting the loop, create a var to hold the group date. As you process each row, compare it's date to the group date. If it is the same then just add to the group. When it changes, start a new group and record the date as the new group date.

jung

Originally posted by Roger Ramjet
So you need to put a condition into your while loop that processes the results. Before starting the loop, create a var to hold the group date. As you process each row, compare it's date to the group date. If it is the same then just add to the group. When it changes, start a new group and record the date as the new group date.

wow... that get's me close ... have you an example of this?

I also sense I will need to redifine the article date... meaning it's a datetime field so it won't group until I converted it to JUSt the date...

any help is appreciated.

Roger_Ramjet

Search this forum for grouping, lots of threads with examples.

If the time of the article is important then use Date(datetimefield) in your query. Date() is a native mysql function that returns the date from a datetime or timestamp. If it is not important, and it usually isn't, just convert the field to a date field only.