Group and Distinct

[MrRundog]

Hi I have the following table structure


tbl_booking
id roomID the_date id_booking
301 13 2008-04-01 114
299 13 2008-04-25 112
298 20 2008-04-02 111
297 20 2008-05-01 111
296 13 2008-05-03 111
297 13 2009-01-08 114


What I would love to do is show the id_booking per month but only once so that i would end up with

April 2008
booking 111
booking 112
booking 114

May 2008
booking 111

January 2009
booking 114

There are likely to be many entries in the table so efficiency is important.
Any ideas please
Cheers

[Andrew Johnson]

Firstly, add "ORDER BY the_date" to the end of your query to ensure they always appear in the correct order.

Secondly, your best bet is to do this via whatever programming language will be connecting to and querying your database. You can't create a new row being pulled for each new month.

[MrRundog]

Sorry - I meant to mention it in php - Ok - would this thread belong in the PHP Section then?

[Fumigator]

You're really talking about organizing the resultset with a group heading, in this case your group is the month portion of the_date. The way I'd do it (and there are several different ways, this is just one way) is I would select the_date formatted with just month/year (i.e. "SELECT date_format(the_date, '%M %Y')" ) and then as I loop through the resultset, check to see when that value changes. Something like..


$query = "SELECT date_format(the_date, '%M %Y') as month_year FROM ...... WHERE ... ORDER BY the_date)";

$result = mysql_query($query);
if (!$result) {
die ("Query Error! Query text: $query<br >Error: " . mysql_error());
}

$prevMonthYear = 0;
//mysql_fetch_assoc
while ($row = mysql_fetch_assoc($result)) {
if ($row['month_year'] != $prevMonthYear) {
echo $row['month_year']."<br />"; //whatever HTML markup you're using
$prevMonthYear = $row['month_year'];
}
echo $row[...]; //print the detail
}