webdeveloper123

Hey Guys, Sorry if this thread is in the wrong place, I posted it here because I wasn't sure if the problem was with the PHP, JS or CSS (or maybe all!) I am making an online room reservation system. I'm returning search results. Now, in the results I am limiting to only show 5 room amenities (called facilities in the sql). This bit is fine. Rooms range from having 6 to 14 amenities, so because I didn't want to overload with information and wanted all the search result boxes to be the same size I thought I'd have the Amenities listed and then next to it I would have a "See All" text that when you click on it, it shows all amenities for that room in a JS popup. I've got the code for the popup. Now here is my SQL: $sql = "SELECT rs.description , rs.sleeps , rs.image , rs.price , substring_index(GROUP_CONCAT(f.description separator ' &bull; '), ' &bull; ', 5) as rmfac , rt.description as rmtype , r.room_id , r.room_number , GROUP_CONCAT(DISTINCT f.description SEPARATOR '<br>') AS rmfac1 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 GROUP BY room_id;"; rmfac displays the 5 amenities which is fine. rmfac1 is supposed to show all of them. If I do this: <?php echo "<p class='details'> Amenities: {$rooms['rmfac1']}</p>";?> It shows all amenities, no problem. But as soon as I try make it appear in the popup, it will only show maximum of 6, even though some have more than 6. Now even in the popup, If I view HTML source, I can see the amenities output for all rooms, all match up - but only show 6 in the popup First I thought it was my CSS, so I played around with it, made it massive in size thinking it's hidden somewhere, but to no avail. Here is my code: (relevant parts) <div class="searchresults"> <?php foreach ($availableRooms as $rooms) { ?> <div class="perpicture"> <img class="picture" src="images/<?= htmlspecialchars($rooms['image']) ?>"> </div> <div class="resultdetails"> <h2> <?= htmlspecialchars($rooms['rmtype']) ?></h2> <h1> <?= htmlspecialchars($rooms['description']) ?></h1> <p class="details"> Sleeps <?= htmlspecialchars($rooms['sleeps']) ?></p> <p class="details"> Price <?= htmlspecialchars($rooms['price']) ?></p> <?php echo "<p class='details'> Amenities: {$rooms['rmfac']}</p>";?> <div class="popup" onclick="myFunction()">See all <span class="popuptext" id="myPopup"><?php echo "<p class='details'> Amenities: {$rooms['rmfac1']}</p>";?></span> </div> </div> <?php } ?> </div> <script> // When the user clicks on div, open the popup function myFunction() { var popup = document.getElementById("myPopup"); popup.classList.toggle("show"); } </script> Here is the CSS for the popup: .popup { position: relative; display: inline-block; cursor: pointer; -webkit-user-select: none; -moz-user-select: none; -ms-user-select: none; user-select: none; } /* The actual popup */ .popup .popuptext { visibility: hidden; width: 360px; background-color: #BEBEBE; color: black; text-align: center; padding: 8px 0; position: absolute; z-index: 1; bottom: 50%; left: -60px; margin-left: -80px; height: 500px; top: 110%; } } /* Toggle this class - hide and show the popup */ .popup .show { visibility: visible; -webkit-animation: fadeIn 1s; animation: fadeIn 1s; } /* Add animation (fade in the popup) */ @-webkit-keyframes fadeIn { from {opacity: 0;} to {opacity: 1;} } @keyframes fadeIn { from {opacity: 0;} to {opacity:1 ;} } Can someone help please? Thanks

I can't believe it! It does it! 😄 Thanks Barand!

Thanks for the code Barand, I'll give it a try

I don't even know how to do that, this is going to take me forever

So would I have to base it on something like this: SELECT * FROM (SELECT description, facility_id, row_number() OVER (order by facility_id) AS row_num FROM facility) f WHERE row_num <= 5; From the top of the thread?

I don't get it. I thought that Was the last little bit of code?

Hey Barand, Thanks for the code but sorry I'm getting a bit confused. I'm using: SELECT rs.description , rs.sleeps , rs.image , rs.price , GROUP_CONCAT(DISTINCT f.description SEPARATOR ' &bull; ') 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 AND row_number() OVER (order by f.facility_id) <= 5 GROUP BY room_id; I tried it several ways, different order of code but keep getting the same error: Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 4015 Window function is allowed only in SELECT list and ORDER BY clause in /var/www/vhosts/:86 Stack trace: #0 /var/www/vhosts/(86): PDO->prepare() #1 {main} thrown in /var/www/vhosts/ on line 86 Thanks

I did swap them out, I am getting search results but some of them it limits to 5 facilities shown, some of them it doesn't.

Actually, did you mean swap out row_num for f.description?

I don't get it. I have to put the whole row_number code in the WHERE clause?

I got to this: WHERE row_num <= 5 AND rb.room_id IS NULL GROUP BY room_id; But now I get another error: #1054 - Unknown column 'row_num' in 'where clause' Is my row_number code even correct?

Would it be correct in this situation to Join the WHERE clauses using AND?

No, sorry I can't. Is it because there are 2 x WHERE clauses? Or is it in the wrong order? It's something blatantly obvious isn't it?

Sorry I noticed an error in the above code which doesn't run. I have an extra ; at the end of GROUP BY then have another ; after WHERE row_num <=5. Got rid of it but still the same error

Hi guys, I am trying to limit an attribute in an sql select statement to only return 5 rows. I've been researching it and apparently I have to use row_number. I am trying to limit description as this has the facilities for the hotel room. There could be anywhere bewteen 6 and 14 , but I only want to show a maximum of 5. I have got this far: SELECT * FROM (SELECT description, facility_id, row_number() OVER (order by facility_id) AS row_num FROM facility) f WHERE row_num <= 5; It seems to be giving me the correct results. But now I have to merge it into this: SELECT rs.description , rs.sleeps , rs.image , rs.price , GROUP_CONCAT(DISTINCT f.description SEPARATOR ' &bull; ') 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 GROUP BY room_id; I have tried several ways including this: SELECT rs.description , rs.sleeps , rs.image , rs.price , GROUP_CONCAT(DISTINCT f.description SEPARATOR ' &bull; ') AS rmfac , row_number() OVER (order by f.facility_id) AS row_num , 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 > '2024-01-01' AND rb.arrivalDate <= '2024-01-05' WHERE rb.room_id IS NULL GROUP BY room_id; WHERE row_num <= 5; But I get the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE row_num <= 5' at line 1 At first I thought it was because I had 2 x WHERE clauses, so I just joined them together using AND I still get an error. Is ORDER BY and GROUP BY clashing? Can someone help please? Thanks

Do you want the warning messages to not display because you have moved from development to live mode, or just don't want warning messages to appear while your developing?

Thanks, I'll give it a try

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

Ohhh I just noticed that! How could I miss it No, but I'll try that now!

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. 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. 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

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

Thanks Barand! Done it!

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

Yes! it runs and I get search results! Thanks Barand! 😁 I took your logic from the code above and put it in the earlier sql statement further up the thread!

hmm I sort of get the diagram. It shows which room spans which dates they are occupied. I want to show rooms available between those dates.