MySQL sub query and join MySQL 5

[chrispos]

Aaron Rhodes

Thank you for your last comment

 

I have set up a hotel booking system and I use the day of year for the start date for the  booking in date and the day of year for the departure date. I have two tables that I want to link but only show the available rooms or if the room is booked echo "Booked";

 

The first table ` rooms` is where the room rid, number description and how many it sleeps

 

The next table is `bookings` with day, month, year, dayofyear, day1, month1, year1, dayofyear1

 

I have a page that I have used the code below but it shows all the rooms in the `booking` table and all the rooms in the `rooms` table but what I would like to do is just show the rooms that are available in that given period. You can see the site at bandbbookings.co.uk/booked1.php and this will give you an insight.

 

The code I used to test this is below. All the date functions for selecting the date before post and after ie can only book for a max period of 28 days leep years are all sorted this which I thought would be a walk in the park has turned out to be a nightmare. I have used a foreach loop because I need to know if a room is available if it falls in between another date.

$query = "SELECT * FROM `rooms` LEFT JOIN `bookings` ON rooms.rid = bookings.rid";
$result = mysql_query($query) or die (mysql_error());
if (mysql_num_rows($result)>0){ 
while ($row = mysql_fetch_row($result)) {
$rid=$row[0];
$number=$row[1];
$description=$row[2];
$sleeps=$row[3];
$bid=$row[4];
$rid=$row[5];
$d=$row[6];
$m=$row[7];
$y=$row[8];
$dy=$row[9];
$d1=$row[10];
$m1=$row[11];
$y1=$row[12];
$dy1=$row[13];
$available=$row[14];
$days = range($dy,$dy1);
foreach ($days as $key => $value)
{
if
($dy1 >= 63 && $dy <= 64)
$booking = BOOKED;
else
$booking = '<form id="form1" name="form1" method="post" action="/form2.php">
  <label>
  <input type="submit" name="Submit" value="Book Me" />
  </label>
</form>';
}
echo"$number\n";
echo"$description\n";
echo"$sleeps\n";
echo"$bid\n";
echo"$rid\n";
echo"$d\n";
echo"$m\n";
echo"$y\n";
echo"$dy\n";
echo"$d1\n";
echo"$m1\n";
echo"$y1\n";
echo"$dy1\n";
echo"$booking<br>";
}

I have tried a reverse of the $dy etc but then realized that the null column would not register and if the standard select clause would wipe out the area that I am trying to show.

 

Thanks for your time this is probably out of limits but you never know

[fenway]

use code tags next time... and what's the mysql question, exactly?