Group Concat code not working as expected - and only showing 1 result

[webdeveloper123]

Hi Guys,

I am building an online room reservation system for a hotel. I got some SQL that returns available rooms between 2 dates and some other information such as room number, description etc. The code runs fine. Each room has amenities (called facilities in the database) such as air conditioning, safe , mini bar etc. The amenities are done by size_id in the database, not per room basis. So each room that is size_id = 1 will have the same amenities, as will size_id 2 and 3. The most basic room size_id = 1 has 6 amenities and this goes up as the rooms get bigger by size. There are 16 amenities in total. 

I obviously want to group the amenities in the search result so I am using this code:

<?php  

$sql = " SELECT rs.description, rs.sleeps, rs.image, rs.price, GROUP_CONCAT(DISTINCT f.description SEPARATOR '\n') AS rmfac, rt.description as rmtype, r.room_id, r.room_number
FROM roomsize AS rs
 LEFT JOIN room_facility AS rf ON rf.size_id = rs.size_id
  LEFT JOIN room as r on r.size_id = rs.size_id
 LEFT JOIN facility as f ON rf.facility_id = f.facility_id 
 LEFT JOIN roomtype as rt ON  r.roomtype_id = rt.roomtype_id 
 LEFT JOIN room_booking as rb on  r.room_id = rb.room_id

 AND rb.departureDate > :departure
AND rb.arrivalDate <= :arrival 

WHERE rb.room_id IS NULL ORDER BY room_id;";

$statement = $pdo->prepare($sql);
$statement->execute(array('departure' => $departure, 'arrival' => $arrival));
$availableRooms  = $statement->fetchAll();

<div class="searchresults">

<?php foreach ($availableRooms as $rooms) { ?>
     <p> <?= htmlspecialchars($rooms['rmtype']) ?>
</p>
 <p> <?= htmlspecialchars($rooms['description']) ?>
</p>
    <p> <?= htmlspecialchars($rooms['room_id']) ?>
</p>
    <p> <?= htmlspecialchars($rooms['room_number']) ?>
</p>
 <p> Sleeps <?= htmlspecialchars($rooms['sleeps']) ?>
</p>
 <p> <?= htmlspecialchars($rooms['price']) ?>
</p>
  <p> <?= htmlspecialchars($rooms['rmfac']) ?>
</p>
  <p> <?= htmlspecialchars($rooms['image']) ?>
</p>
<?php } ?>
</div>

?>

But this only displays 1 record, the very first room in the room table, and shows 15 amenities instead of the 6 for that room. I have 32 rooms and 16 are booked. So depending on the dates you search, either all 32 will come up or only 16(as all 16 are booked for the same dates). 

I have attached the ERD for reference.

Thanks

[Barand]

When you use an agregation function (like SUM() or GROUP_CONCAT() ) without a GROUP BY clause, you get a single aggregation for the whole table.

[webdeveloper123]

Thanks Barand! Done it!

[webdeveloper123]

For some reason it doesn't go on a new line, just prints it all out on one line with a space in between

[Barand]

Perhaps because your separator is "\n" and HTML treats all whitespace as a space. Try "<br>";

[Barand]

By the way, your query will not output the rooms that you want. Check the date join conditions between room and room_booking in your query against the query I gave you in your previous post.

Also, the only join that may or may not find a matching record is room->room_booking, so that needs to be a LEFT JOIN. All the other joins only need to be simple JOINs (which are much faster than LEFT JOINS)

SELECT rs.description
     , rs.sleeps
     , rs.image
     , rs.price
     , GROUP_CONCAT(DISTINCT f.description SEPARATOR '<br>') AS rmfac
     , rt.description as rmtype
     , r.room_id
     , r.room_number
FROM room as r 
     JOIN roomtype as rt ON  r.roomtype_id = rt.roomtype_id
     JOIN roomsize as rs ON r.size_id = rs.size_id
     JOIN room_facility as rf ON rf.size_id = rs.size_id
     JOIN facility as f ON rf.facility_id = f.facility_id
     LEFT JOIN room_booking as rb ON r.room_id = rb.room_id
                                  AND rb.departureDate > :arrival
                                  AND rb.arrivalDate <= :departure
WHERE rb.room_id IS NULL 
ORDER BY room_id

 

[webdeveloper123]

21 minutes ago, Barand said:

Check the date join conditions between room and room_booking in your query

I took a look at that post on the sql forum and the LEFT JOIN from room to room_booking is the same as your code above and the code in the original post on this thread.

25 minutes ago, Barand said:

so that needs to be a LEFT JOIN. All the other joins only need to be simple JOINs

Thanks for spotting that and letting me know.

I am now using your above code instead of the code in the original post on this thread. Apart from I am using GROUP BY room_id instead of ORDER BY as per your advice. I did use ORDER BY but it gave me the same problem as the one for which I started this thread for. 

1 hour ago, Barand said:

because your separator is "\n" and HTML treats all whitespace as a space. Try "<br>";

I saw that on stack overflow but thought the guy must be wrong because how can you put HTML into an SQL statement. I tried '<br>' and '</br>' and "<br> and "</br>". The double quotes give me an error but the single quotes run, but it still doesn't put on new line. I get this:

55 Inch Smart TV<br>Mini-Bar<br>Queen Size Bed<br>Safe<br>Wheel chair access<br>WiFi

And if I use '</br>' I get the same as above but with the slash. It's a strange one 

[ginerjm]

Did you output an <html> tag first?

[Barand]

17 minutes ago, webdeveloper123 said:

the LEFT JOIN from room to room_booking is the same as your code above and the code in the original post on this thread.

To check for rooms booke between dates A and B you need to compare their

• departureDate against A, and
• arrivalDate against B

Your query above has ...

4 hours ago, webdeveloper123 said:

AND rb.departureDate > :departure
AND rb.arrivalDate <= :arrival

* * * * *

The <br> won't kick in until you you output to an HTML page.

[webdeveloper123]

8 minutes ago, Barand said:

Your query above has ...

4 hours ago, webdeveloper123 said:

AND rb.departureDate > :departure
AND rb.arrivalDate <= :arrival

Ohhh I just noticed that! How could I miss it 

 

16 minutes ago, ginerjm said:

Did you output an <html> tag first?

No, but I'll try that now!

[webdeveloper123]

19 hours ago, ginerjm said:

Did you output an <html> tag first?

I feel a bit silly for asking this but I can't figure out how to do it! My SQL is nested inside <html>   </html> as Is all my other PHP and other HTML. But I can't figure it out.

I tried '<html> <br> </html>'

and I tried putting the SQL like this <html> $sql </html> but I got an error

Edited September 15, 2023 by webdeveloper123

[Barand]

Example code (data from tutorial in my sig)

<?php
$res = $pdo->query("SELECT concat(fname, ' ', lname) as name
                         , group_concat(subject separator '<br>') as subjects
                    FROM pupil p 
                         JOIN choice c  USING (pupilID)
                         JOIN subject s USING (subjectID)
                    WHERE houseID = 1
                    GROUP BY p.pupilID;
                   "); 

$tdata = '';
foreach ($res as $row)  {
    $tdata .= "<tr><td>{$row['name']}</td>
                   <td>{$row['subjects']}</td>
               </tr>";
}
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset='utf-8'>
<title>Example</title>
<style type='text/css'>
    body {
        font-family: verdana;
    }
    table {
        border-collapse: collapse;
        width: 500px;
        margin: 50px auto;
    }
    td {
        padding: 4px 16px;
    }
</style>
</head>
<body>
    <table border='1'>
        <?= $tdata ?>
    </table>
</body>
</html>

output

[webdeveloper123]

Thanks, I'll give it a try