webdeveloper123

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.

Hey Barand, Still can't return any search results. Been at it for hours now. I tweaked the SQL and added some more aliases as there were 3 x description attributes in the select. Here is my code: SELECT rs.description, rs.sleeps, rs.image, rs.price, f.description as rmfac, rt.description as rmtype, r.room_id, r.room_number FROM roomsize AS rs JOIN room_facility AS rf ON rf.size_id = rs.size_id JOIN room as r on r.size_id = rs.size_id JOIN facility as f ON rf.facility_id = f.facility_id JOIN roomtype as rt ON r.roomtype_id = rt.roomtype_id JOIN room_booking as rb on r.room_id = rb.room_id WHERE rb.arrivalDate >= '2024-01-01' AND rb.departureDate < '2024-01-05'; Tried loads of different things like changing the dates, using different operators etc. It runs if I swap AND for OR but that's not right as the room has to be available between both dates, not just one. And it runs if I take out the WHERE clause. Test data: room_booking room_booking_id room_id booking_id arrivalDate departureDate 33 2 1 2023-08-10 2023-12-31 34 4 10 2023-08-10 2023-12-31 35 7 5 2023-08-10 2023-12-31 This continues as 16/32 rooms are booked. All have the same arrival and departure Date. I did this because I knew I wouldn't finish the project within a week or so and I would have to keep inserting new data. Thanks