ordering by GROUP_CONCAT?

[bazz]

Hi,

Making progress for once but, I need you help with this bit.

How do I order the values of both GROUP_CONCATs? I want them in (date) order. Can it be done in the query or, should it be done in perl?


$VAR1 = \{
'GROUP_CONCAT( RB.check_in_date )' => '2008-06-14,2008-06-26,2008-06-04',
'booking_id' => '0000000004',
'room_status' => 'booked',
'GROUP_CONCAT( RB.check_out_date )' => '2008-06-20,2008-07-04,2008-06-14',
'room_name' => 'The Conal Suite',
'room_number' => '11'
};
After a lot of bleeding, this is my query


my $sth = $bookings_db_connect->prepare ("SELECT
RD.room_name,
RD.room_number,
GROUP_CONCAT( RB.check_in_date ),
GROUP_CONCAT( RB.check_out_date ),
RB.booking_id,
RB.room_status

FROM
tbl_rooms_details RD
left outer join tbl_rooms_booked RB
ON
RD.room_id = RB.room_id
AND business_id = ?
AND check_in_date <= ?
AND check_out_date >= ?
#ORDER BY GROUP_CONCAT( RB.check_in_date )
GROUP BY RD.room_number
") or die "prepare statement failed: $DBI::errstr\n";
And here is the beginning of the code


while ( $room = $sth->fetchrow_hashref)
{
$room_number = $room->{room_number};
$room_name = $room->{room_name};
@check_in_date = $room->{'GROUP_CONCAT( RB.check_in_date )'};
@check_out_date = $room->{'GROUP_CONCAT( RB.check_out_date )'};
$room_status = $room->{room_status};
$booking_id = $room->{booking_id};
print Dumper \$room;

print qq(
<tr>
<td class='rooms_label'>$room_number $room_name @check_in_date</td>
);


foreach my $day_number (1..$days)
{


bazz

[FishMonger]

Try this:
my $sth = $bookings_db_connect->prepare ("SELECT
RD.room_name,
RD.room_number,
GROUP_CONCAT( RB.check_in_date ORDER BY RB.check_in_date ASC ),
GROUP_CONCAT( RB.check_out_date ORDER BY RB.check_out_date ASC ),
RB.booking_id,
RB.room_status

FROM
tbl_rooms_details RD
left outer join tbl_rooms_booked RB
ON
RD.room_id = RB.room_id
AND business_id = ?
AND check_in_date <= ?
AND check_out_date >= ?
#ORDER BY GROUP_CONCAT( RB.check_in_date )
GROUP BY RD.room_number
") or die "prepare statement failed: $DBI::errstr\n";

[bazz]

Thanks FishMonger,

I have since discovered that I need to tie the booking_id with the check_in_date, check_out_date and likely, the room _status.

I'll try to work on that and at the same time, wil bear in mind your suggestion.

I may have to call on your expertise again soon guys.

bazz