john-formby Posted March 19, 2013 Share Posted March 19, 2013 Hi, I am trying to write a query, but it is not giving the correct results. I have 2 mysql tables: roomsbooked = records the date (date datatype) and userID of who has a room booked rooms = a list of all rooms What I need to do is look at the roomsbooked table for a given date and create an array of any rooms from the rooms table that are not included for that date in roomsbooked. Essentially, I am trying to find out what rooms are still available. I have written a query, but it is not giving me the correct data: $sql3 = mysql_query("SELECT * FROM rooms, roomsbooked WHERE roomsbooked.event_date = '$currentDate' && rooms.roomID != roomsbooked.roomID"); $numrows3 = mysql_num_rows($sql3); echo $numrows3; while($row3 = mysql_fetch_array($sql3)) { $rooms[] = $row3['rooms.roomnum']; } print_r($rooms); The problem is that when I echo numrows3 I get zero. I know that there is a record in the roomsbooked table for the event_date I am querying and I have lots of room in the rooms table. Please can someone kindly point me in the right direction. Many Thanks, John Quote Link to comment Share on other sites More sharing options...
fooDigi Posted March 19, 2013 Share Posted March 19, 2013 You should probably try joining the tables more like this... SELECT * FROM rooms r LEFT OUTER JOIN roomsbooked rb ON r.roomID = rb.roomID where rb.roomID = NULL if that makes any sense... I threw this out there, and obviously havent tested... Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 19, 2013 Share Posted March 19, 2013 You should probably try joining the tables more like this... SELECT * FROM rooms r LEFT OUTER JOIN roomsbooked rb ON r.roomID = rb.roomID where rb.roomID = NULL if that makes any sense... I threw this out there, and obviously havent tested... Close, but pretty sure you have to use "IS NULL" or "IS NOT NULL" and not "= NULL". Also, you will need to use the date to limit the records being joined based on the date being checked. This should work SELECT rooms.roomID, rooms.roomDescription FROM rooms LEFT OUTER JOIN roomsbooked ON rooms.roomID = roomsbooked.roomID AND roomsbooked.event_date = '$currentDate' WHERE roomsbooked..roomID IS NULL Put the fields you need returned in the SELECT clause - don't use '*'. Quote Link to comment Share on other sites More sharing options...
fooDigi Posted March 19, 2013 Share Posted March 19, 2013 SELECT r.roomID R, rb.roomID RB FROM rooms r LEFT OUTER JOIN roomsbooked rb ON r.roomID = rb.roomID WHERE rb.roomID IS NULL should query all room records that are not booked.... i think thanks psycho, for the correction. Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 19, 2013 Share Posted March 19, 2013 Since you don't need any data from the roomsbooked table, this might be more efficient since a JOIN is not needed. SELECT rooms.roomID, rooms.roomDescription FROM rooms WHERE rooms.roomID NOT IN ( SELECT roomID FROM roomsbooked WHERE roomsbooked.event_date = '$currentDate' ) Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 19, 2013 Share Posted March 19, 2013 (edited) SELECT r.roomID R, rb.roomID RB FROM rooms r LEFT OUTER JOIN roomsbooked rb ON r.roomID = rb.roomID WHERE rb.roomID IS NULL should query all room records that are not booked.... i think thanks psycho, for the correction. The OP needed all unbooked rooms - for a specific date. Both of my solutions would do that. And why would you want to return the room ID from the booked table? It would be NULL and of no value. Edited March 19, 2013 by Psycho Quote Link to comment Share on other sites More sharing options...
fooDigi Posted March 19, 2013 Share Posted March 19, 2013 I could be wrong, but I was assuming the issue was with the join of the tables... the date filter can be added later. well, let's see if he fixed it... Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 19, 2013 Share Posted March 19, 2013 I could be wrong, but I was assuming the issue was with the join of the tables... the date filter can be added later. well, let's see if he fixed it... Why would you want to apply that filter later when you can simply apply it in the SELECT query? That only creates more overhead. Besides, the OP clearly stated his intent What I need to do is look at the roomsbooked table for a given date and create an array of any rooms from the rooms table that are not included for that date in roomsbooked. Quote Link to comment Share on other sites More sharing options...
fooDigi Posted March 19, 2013 Share Posted March 19, 2013 Because that would be too easy... lol, You had very good suggestions, and OP should listen to you. I shouldn't be clogging up the flow. first time i've used this site in years. Quote Link to comment Share on other sites More sharing options...
john-formby Posted March 19, 2013 Author Share Posted March 19, 2013 Hi, Thank you for all the replies. I have just read through and tried changing the query to a join and the other one with the two select statements, but am still not getting the results. Below is some data for the two tables I have and I will explain what the output should be: Table 1 = rooms Fields roomID roomNum Data 1, 12 2, 14 3, 18 Table 2 = roomsbooked Fields event_date roomID Data 2013-03-25, 12 I am querying based on an event_date (e.g. 2013-03-25). What I need to do is return roomID = 2 and roomID = 3 into an array from the rooms table as these two rooms do not appear in the roomsbooked table. I have been trying to get this to work, but nothing I try gives me the result I need. Many thanks for all your help. John Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 19, 2013 Share Posted March 19, 2013 Well, if what you have above is representative of your data, then that is your problem. In the "rooms" table you have a roomID field with values of 1, 2, and 3. In the "roomsbooked" table you also have a roomID field which SHOULD be the same roomID value from the "rooms" table. But, instead you have a room NUMBER: "12". If you start storing the correct value for the roomID in the "roomsbooked" table you will get the correct results using either of the two queries I provided. Using your sample data, but changing the "12" to a "1" in the rooms booked table I ran this query: SELECT roomID, roomNum FROM rooms WHERE rooms.roomID NOT IN ( SELECT roomID FROM roomsbooked WHERE roomsbooked.event_date = '2013-03-25' ) And got these results: roomID | roomNum 2 14 3 18 Quote Link to comment Share on other sites More sharing options...
john-formby Posted March 19, 2013 Author Share Posted March 19, 2013 Oops, sorry, yes the roomID in the roomsbooked table should have been 1 not 12. It is getting late :-) I can get your code to work perfectly when I run it on its own, but as soon as I try to nest it inside my for loop, it returns all the records from the rooms table, not just the empty ones. I know this is cheeky, but would it be possible for you to see if you get the same result? for($i=0;$i<5;$i++) { $currentDate = date('Y-m-j', strtotime('Monday+'.$i. ' day')).'<br />'; echo $currentDate; $sql3 = mysql_query("SELECT roomID, roomNum FROM rooms WHERE rooms.roomID NOT IN (SELECT roomID FROM roomsbooked WHERE roomsbooked.event_date = '$currentDate')"); $numrows3 = mysql_num_rows($sql3); echo $numrows3; while($row3 = mysql_fetch_array($sql3)) { $rooms[] = $row3['roomNum']; } print_r($rooms); } I am trying to loop through each day for the following week (Monday to Friday) and return the available rooms for that day. It just doesn't ignore any rooms that are already occupied. Many thanks, John Quote Link to comment Share on other sites More sharing options...
jcbones Posted March 19, 2013 Share Posted March 19, 2013 (edited) Don't run queries in loops. It will turn into a nightmare. Un-Tested added to Psycho's code SELECT rooms.roomID, rooms.roomDescription, roomsbooked.event_date FROM rooms LEFT OUTER JOIN roomsbooked ON rooms.roomID = roomsbooked.roomID AND roomsbooked.event_date BETWEEN '$startDate' AND '$endDate' WHERE roomsbooked.roomID IS NULL ORDER BY roomsbooked.event_date Edited March 19, 2013 by jcbones Quote Link to comment Share on other sites More sharing options...
john-formby Posted March 19, 2013 Author Share Posted March 19, 2013 Thanks jcbones, I am beginning to see that :-) The problem is that I need to run the query five time, once for each day of the following week as I have more code to put under this. I am not sure how to do this without nesting the query within the loop? Many thanks, John Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 19, 2013 Share Posted March 19, 2013 The problem is that I need to run the query five time, once for each day of the following week as I have more code to put under this. I am not sure how to do this without nesting the query within the loop? No, you do not need to run the query five time. As jcbones stated you should NEVER run queries in loops. 95% of the time you can run ONE query to get what you need. In the other 5% of the times you probably have a bad database design. Based on your code above you want all the vacancies that occur on each of the days in the date range. This is a little more difficult, but still does not require a loop. You could use PHP to dynamically create the multiple queries ad use a UNION or you could take a different approach entirely and instead just query the list of all room IDs and the booked rooms by date and put the logic of determining what is vacant in the PHP logic. Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 19, 2013 Share Posted March 19, 2013 (edited) Here's a solution using UNION (tested) $dayQueries = array(); for($i=0;$i<5;$i++) { $bookDate = date('Y-m-j', strtotime('Monday +'.$i. ' days')).'<br />'; $dayQueries[] = "SELECT roomID, roomNum, '{$bookDate}' FROM rooms WHERE rooms.roomID NOT IN (SELECT roomID FROM roomsbooked WHERE roomsbooked.event_date = '{$bookDate}')" } $SingleQueryToRun = implode("\nUNION\n", $dayQueries); Edited March 19, 2013 by Psycho Quote Link to comment Share on other sites More sharing options...
john-formby Posted March 19, 2013 Author Share Posted March 19, 2013 Hi Psycho, Thank you for the help with this. How do I get the code above to output the records? I have run the code, but nothing is displayed in the browser. Many thanks, John Quote Link to comment Share on other sites More sharing options...
Solution Psycho Posted March 19, 2013 Solution Share Posted March 19, 2013 (edited) You have to execute the query and then extract the results - just like you would when running any other query. <?php mysql_connect('MySQL_Server_URL', 'username', 'password'); mysql_select_db('database_name'); $dayQueries = array(); for($i=0;$i<5;$i++) { $bookDate = date('Y-m-j', strtotime("Monday +{$i} days")); $dayQueries[] = "SELECT roomID, roomNum, '{$bookDate}' FROM rooms WHERE rooms.roomID NOT IN (SELECT roomID FROM roomsbooked WHERE roomsbooked.event_date = '{$bookDate}')" } $SingleQueryToRun = implode("\nUNION\n", $dayQueries); $result = mysql_query($query) or die(mysql_error()); echo "<table border='1'>\n"; while($row = mysql_fetch_assoc($result)) { echo "<tr><td>" . implode("</td><td>", $row) . "</td></tr>\n"; } echo "</table>\n"; ?> Edited March 19, 2013 by Psycho Quote Link to comment Share on other sites More sharing options...
john-formby Posted March 19, 2013 Author Share Posted March 19, 2013 Hi Psycho, Thank you again, I ran it as it was, but no records were returned. I changed the $query on line $result = mysql_query($query) or die(mysql_error()); to $SingleQueryToRun and it returns results. It is now returning all the rooms again, including the one that should be excluded as it appears in the roomsbooked table. Sorry to be such a pain, I just have no idea how to get this to work. Many thanks, John Quote Link to comment Share on other sites More sharing options...
john-formby Posted March 20, 2013 Author Share Posted March 20, 2013 Hi, I just figured out the problem. It was because of the line break tag at the end of this line: $bookDate = date('Y-m-j', strtotime('Monday +'.$i. ' days')).'<br />'; Once I removed this, it gives the correct results. Thank you so much to everyone who has helped with this. John Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.