MySQL select where record does not exist in other table

[john-formby]

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

[fooDigi]

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

[Psycho]

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

[fooDigi]

 

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.

[Psycho]

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'
)

[Psycho]

 

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

[fooDigi]

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

[Psycho]

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.

[fooDigi]

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.

[john-formby]

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

[Psycho]

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

[john-formby]

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

[jcbones]

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

[john-formby]

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

[Psycho]

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.

[Psycho]

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

[john-formby]

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

[Psycho]

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

[john-formby]

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

[john-formby]

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