help please with a half-way solution between these two scripts.

[bazz]

I have now got two queries done and associated scripting.

The first image shows how the data is retrieved with the first query
The second image shows how the data is retrieved using GROUP_CONCAT. (second query). Don't worry about post-processing code as it is definitely not causing the issue.

I want the data to show all bookings that are shown in image 1 (query 1). However, I want the bookings to be one line per room as in image 2 (query 2). Please note that image 2 (query 2 uses group_concat and seems therefore only to be able to show one booking per room.

query 1

my $sth = $bookings_db_connect->prepare ("SELECT
RD.room_name,
RD.room_number,
RB.booking_id,
RB.check_in_date,
RB.check_out_date,
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 RD.room_number, RD.room_name, RB.check_in_date
#GROUP BY room_number
") or die "prepare statement failed: $DBI::errstr\n";
$sth->execute($business_id, $last_date, $first_date);

while ( $room = $sth->fetchrow_hashref)
{
$room_number = $room->{room_number};
$room_name = $room->{room_name};
$check_in_date = $room->{check_in_date};
$check_out_date = $room->{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</td>
);


query 2


my $sth = $bookings_db_connect->prepare ("SELECT
# GROUP_CONCAT(
RD.room_id,
RD.room_name,
RD.room_number,
RB.booking_id,
RB.check_in_date,
RB.check_out_date,
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 RD.room_number, RD.room_name, RB.check_in_date
GROUP BY RD.room_number
") or die "prepare statement failed: $DBI::errstr\n";
$sth->execute($business_id, $last_date, $first_date);

while ( $room = $sth->fetchrow_hashref)
{
$rooms{ $room->{room_id} }{'name'} = $room->{room_name};
$rooms{ $room->{room_id} }{'room_number'} = $room->{room_number};
$rooms{ $room->{room_id} }{'booking_id'} = $room->{booking_id};
$rooms{ $room->{room_id} }{'check_in_date'} = $room->{check_in_date};
$rooms{ $room->{room_id} }{'check_out_date'} = $room->{check_out_date};
$rooms{ $room->{room_id} }{'room_status'} = $room->{room_status};

#push @{ $rooms{ $room->{room_id} }{rate} }, $room->{room_rate} if $room->{room_rate};
# print Dumper \$room;
}

foreach my $room_id ( sort keys %rooms )
{
print qq(
<tr>
<td class='rooms_label'>$rooms{ $room_id}{'room_number'} $rooms{ $room_id}{'name'}
</td>
);
my $check_in = $rooms{ $room_id}{'check_in_date'};
my $check_out = $rooms{ $room_id}{'check_out_date'};
my $booking_id = $rooms{ $room_id}{'booking_id'};
my $room_status = $rooms{ $room_id}{'room_status'};
#}


Working on this now for two days and my head is busted.

any guidance most welcome.


bazz