• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

PHP Mysql display Rooms available

Hi All, im trying to get a list of rooms that are available. I can get it to show but for every room that is full i get a list of rooms that are not full again ignoring the first result so i get

2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
3
4
5
6
7
8
9
10
11
12
13
14
15

but would like to see

3
4
5
6
7
8
9
10
11
12
13
14
15

because room 1 and 2 are between the dates required.
include ("include.php");
		$date11 = '2009-01-01';
		$date22 = '2009-01-09';
$query = "SELECT * FROM kyte_bookings WHERE datefrom AND dateto BETWEEN '$date11' AND '$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>';
}

Open in new window

0
martin69
Asked:
martin69
  • 7
  • 5
  • 2
1 Solution
 
EMB01Commented:
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>';
}

Open in new window

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

23456789101112131415
13456789101112131415

0
 
EMB01Commented:
Please post where this variable is declared:
$roomtaken

If you can, post up your whole code.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
martin69Author Commented:
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>';
}
0
 
EMB01Commented:
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>';
}

Open in new window

0
 
EMB01Commented:
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.
0
 
martin69Author Commented:
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.
0
 
martin69Author Commented:
can you give me that way of working in an example please.
0
 
EMB01Commented:
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'];
}

Open in new window

0
 
Roger BaklundCommented:
>> 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);

Open in new window

0
 
martin69Author Commented:
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>

Open in new window

0
 
martin69Author Commented:
points inceased
0
 
Roger BaklundCommented:
>> 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.

Then don't use var_dump(). Use a loop, and output html.

Change the function, so that it returns the entire room record, not just the room number. Change the record fetching loop like this:

  while($row = mysql_fetch_assoc($result))
    $rooms[] = $row;
  return $rooms;

Now the $rooms array will contain all columns from the kyle_room record. Loop & make HTML similar to this:

echo '<table>';
foreach($rooms as $room)
  echo '<tr>'.
    '<td>'.$room['room'].'</td>'.
    '<td>'.$room['room_type'].'</td>'.
    '<td>'.$room['room_price'].'</td>'.
    '</tr>';
echo '</table>';

I'm just guessing about the column names, replace with your actual column names according to your needs.

>> i would like to show just one of each type available

You can do that in the output loop:

$allready_displayed_type = array();
echo '<table>';
foreach($rooms as $room) {
  if(in_array($room['room_type'],$allready_displayed_type)) continue;
  $allready_displayed_type[] = $room['room_type'];
  echo '<tr>'.
    '<td>'.$room['room'].'</td>'.
    '<td>'.$room['room_type'].'</td>'.
    '<td>'.$room['room_price'].'</td>'.
    '</tr>';
}
echo '</table>';

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

Assuming the variable $people_chosen contains the number of people chosen:

$beds_available = 0;
foreach($rooms as $room) {
  if($room['room_type']=='single')
    $beds_available += 1;
  elseif($room['room_type']=='double')
    $beds_available += 2;
}
if($beds_available < $people_chosen)
  echo '<p class="warn">There is not enough rooms!</p>';

I did not understand the last part. Please explain in more detail, or preferrably, ask a separate question as this seems to be a different issue.
0
 
martin69Author Commented:
brilliant the other question is Q_24372524
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now