[SOLVED] GROUPING by DATE and having each unique Date only display ONCE

[galvin]

Hi all,

Let's say I have a simple MySQL table with two columns (ITEM and DATE), such as...

 

Item1 - 06/10/09

Item2 - 06/12/09

Item3 - 06/10/09

Item4 - 06/12/09

Item5 - 06/10/09

Item6 - 06/14/09

Item7 - 06/14/09

Item8 - 06/14/09

Item9 - 06/18/09

Item10 - 06/18/09

 

 

I want to be able to query this information and GROUP it by DATE, which is easy enough, but I want to have the output page display each unique date only ONCE, above each grouping.  So I'd like the output to be in this basic format...

 

06/10/09

Item1

Item3

Item 5

 

06/12/09

Item2

Item4

 

06/14/09

Item6

Item7

Item8

 

06/18/09

Item9

Item10

 

 

So again, the gist is that I'm GROUPING by DATE and SORTING by DATE and want each unique DATE to display only ONCE above each Grouping.  I cannot figure out how to get the DATE to display only ONCE above each grouping.

 

If it's simple, please let me know how. If it's complicated, maybe you give me some basic guidance to get me in the right direction to figure it out on my own.

 

Thanks,

 

[rhodesa]

You can't do it with just MySQL. You need to use some Logic with your server side language.

 

That being said, when displaying the info, you have two options.

 

1) You can do a preliminary SELECT DISTINCT(DateField), then for each entry do another query for the items in that date. This way is easier to code, and may look cleaner (code wise) but it's very resource intensive since it makes lots of queries

 

2) The better way is to do a SELECT (*) with a ORDER BY DateField. Then, as you are displaying the output, track what the current date is, and print a new header when you get to a new date.

[PFMaBiSmAd]

Of course it is simple, how does a human do the same thing? Remember what the last value was and do something when the value changes?

 

 

$last_value = ''; // variable to remember the last value, initialize it to a value that will never exist as data, i.e. a blank
// your exiting loop to iterate over the result set -
while(){
    // test if the last_value and the current value are different 
    if($last_value != $row['date']){
        // do any special processing here, such as starting a new section
        echo $row['date'] . '<br />';

       $last_value = $row['date']; // remember the new value
    }

    // do any normal processing here, output the item information
    echo $row['item'] . '<br />';

}

[galvin]

Great, thanks so much to both of you!