PHP Mysql display Rooms available

I think your query should be as attached, instead.

include ("include.php");
		$date11 = '2009-01-01';
		$date22 = '2009-01-09';
$query = "SELECT * FROM kyte_bookings WHERE datefrom >= '$date11' AND dateto <= '$date22'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
	$roomtaken = $row['roomno'];
	$ning = "SELECT * FROM kyte_rooms WHERE room != '$roomtaken'";
$ninq = mysql_query($ning);
while(list($room) = mysql_fetch_row($ninq))
print("$room");echo'<br>';
}

Select allOpen in new window

Still gives me the same results just not in same format:- see below

23456789101112131415
13456789101112131415

Please post where this variable is declared:
$roomtaken

If you can, post up your whole code.

Hi in the middle of the code i have put some carriage returns in so you can see it. This is the whole code at present. the include is just the username password and database details.

include ("include.php");
                $date11 = '2009-01-01';
                $date22 = '2009-01-09';
$query = "SELECT * FROM kyte_bookings WHERE datefrom >= '$date11' AND dateto <= '$date22'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){



        $roomtaken = $row['roomno'];    //VARIABLE is declared there


        $ning = "SELECT * FROM kyte_rooms WHERE room != '$roomtaken'";
$ninq = mysql_query($ning);
while(list($room) = mysql_fetch_row($ninq))
print("$room");echo'<br>';
}

Oh, I see. I flipped the first query as I now see the script is supposed to first get the taken rooms, then filter.

include ("include.php");
		$date11 = '2009-01-01';
		$date22 = '2009-01-09';
$query = "SELECT * FROM kyte_bookings WHERE datefrom <= '$date11' AND dateto >= '$date22'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
	$roomtaken = $row['roomno'];
	$ning = "SELECT * FROM kyte_rooms WHERE room != '$roomtaken'";
$ninq = mysql_query($ning);
while(list($room) = mysql_fetch_row($ninq))
print("$room");echo'<br>';
}

Select allOpen in new window

More over, I would do this a bit differently. I would get all the rooms, put them into an array; then, run a query that doesn't return the items in the array.

nope, it just finds the first record and does not find the other record so i get

23456789101112131415

so it see's room 1 is taken but does not notice room 2 is taken. as room 2 is taken from 1-1-09 to 4-1-09 and room 1 is taken from 1-1-09 to 9-01-09 so i need it to check the date range so that the whole range from 1-1-09 to 9-1-09 is free and which room is free.

can you give me that way of working in an example please.

Yeah, and I may not be understanding what you're trying to do, exactly. The attached code is what I think you should do...

// include file
include ("include.php");
 
// assign taken dates
$date11 = '2009-01-01';
$date22 = '2009-01-09';
 
// retrieve taken rows
$query = "SELECT * FROM kyte_bookings WHERE datefrom >= '$date11' AND dateto <= '$date22'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
 
// put taken rows into an array
do {
	$takenrooms[] = $row['roomno'];
} while ($row = mysql_fetch_assoc($query));
 
// echo free rooms
foreach ($takenrooms as $takenroom) {
	$query = "SELECT * FROM kyte_rooms WHERE room != '$takenroom'"
	$result = mysql_query($query) or die("Error: " . mysql_error());
	$row = mysql_fetch_assoc($result);
	echo $row['roomno'];
}

Select allOpen in new window

>> check the date range so that the whole range from 1-1-09 to 9-1-09 is free and which room is free.

This is slightly more complex than it seems. You can't just check if the range $date11 - $date22 is between datefrom and dateto, because there could be different overlaps. All these reservations would make a room unavailable for the period 1-1-09 to 9-1-09:

31-12-08 to 2-1-09
8-1-09 to 10-1-09
4-1-09 to 5-1-09
31-12-08 to 15-1-09

The start date can be before or after $date11, and the end date can be before or after $date22.

These reservations would NOT make a room unavailable for the period 1-1-09 to 9-1-09, even if they include the dates of the requested period:

31-12-08 to 1-1-09
9-1-09 to 10-1-09

Try this:

include("include.php");
 
function AvailableRooms($datefrom,$dateto) {
  $query = "select kyte_rooms.room
    from kyte_rooms
    left join (select roomno,count(*) as cnt
      from kyte_bookings
      where
        '$datefrom' between datefrom and dateto - interval 1 day OR
        '$dateto' between datefrom + interval 1 day and dateto OR
        datefrom between '$datefrom' and '$dateto' - interval 1 day
      group by roomno
      having cnt > 0) taken on taken.roomno = kyte_rooms.room
    where taken.roomno is null";
  $result = mysql_query($query) or die("Error: " . mysql_error());
  $rooms = array();
  while($row = mysql_fetch_assoc($result))
    $rooms[] = $row['room'];
  return $rooms;
}
 
$date11 = '2009-01-01';
$date22 = '2009-01-09';
$available = AvailableRooms($date11,$date22);
var_dump($available);

Select allOpen in new window

Hi cxr, That worked a treat, just one last thing. how would i make it look a little more nice on screen so i could search the numbers that are free and display them if they are double twin single.

so say as it does displays 3 through to 15 i would like to show just one of each type available, but if say they put in 4 people two rooms i would like it to show there is 2 available, not the amount available so it would not look like there is tons of rooms free just enough that they would require.

or show a message is there is not enough rooms for people chosen.

i.e if there was 4 people and there was a double and a single it would be short but if there was a double and a twin it would say there was two available

so maybe look something like the code snippet.

so it would show room type that is available then the next column would show the drop down to show the max number they search for or the amount available. so if there is 2 rooms required and there is one 1 double room left it would show 1 in the drop down. then last there is a check box that can be ticked to say i would like this room this many etc. then a book button to save the results onto mysql i will also be integrating this to worldpay as well if you know any easy ways for that as well so it accepts payment then save in mysql. i'll up the point as this is over the initial.

<form id="form" name="form" method="post" action="">
<table width="229" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="83">Room Type</td>
    <td width="69">Required</td>
    <td width="77">Required</td>
  </tr>
  <tr>
    <td>Double</td>
        <td>
      <label>
        <select name="roomreqno[]" id="roomreqno[]">
          <option value="1">1</option>
          <option value="2">2</option>
        </select>
      </label>
    </td>
    <td>
      <label>
        <input type="checkbox" name="req[]" id="req[]" />
      </label>
    </td>
  </tr>
  <tr>
    <td>Twin</td>
    <td><select name="roomreqno[]" id="roomreqno[]">
      <option value="1">1</option>
      <option value="2">2</option>
    </select></td>
    <td><input type="checkbox" name="req[]" id="req[]" /></td>
  </tr>
  <tr>
    <td colspan="3">
      <label>
        <input type="submit" name="book1" id="book1" value="Book" />
      </label>
    </td>
  </tr>
</table></form>

Select allOpen in new window

points inceased

brilliant the other question is Q_24372524