I'm clueless - date availability

[derekcpt]

Hey there.

I really did not want to post this request, since I think it might be too complicated, well, even to explain. So, if some could crack this for me, PLEASE.

I have searched the web for ages now, and tried several scripts and even tried to figure out the code of existing scripts, but everything that I got on the net, is either too bloated or far from what I am looking for.

I tried to get something together to post this together with this posting, but since I have no idea, I really can't add anything.

Well, let me try to explain. I just want a very very simple script/code.

Lets say I have 5 rooms. Now the only thing that I'd like to achieve, is to see whether the room is available to book or not, by getting the dates booked from my mysql database.

Well, since I don't have a clue, I thought that it might work if I select the dates booked from the database.

Ok, let me put it this way. I can achieve viewing all the dates booked, but I don want to achieve this. I want to enter 2 dates, and after submitting the dates, I want to see which rooms are available or not.

Since it is not effective to create a table and enter all the possible dates, and just define whether it is available or not. So I guess that I have to try something whereby the script will display all the rooms not booked, in other words, rooms with no dates tagged.

I can;t really try to put down a method or a way to do this, because I've flattered my brains already and can't find a solution.

So please, please, if anybody can just assist me here.

I just want to define lets say 5 rooms. And I'd like to enter the dates into the sql database where the rooms are booked. So then I'd like to enter 2 different dates, ie. arrival and departure, and when submitting, I have to see the available rooms only.

Please, please/

All my hopes,

Regards.

[derekcpt]

Hey guys.

I found something on the net, which I assume explain the way to go, but I have no clue how to use the JOIN sql method.

Could you please have a look if you can crack this for me please. The sample I have found:

table: room_types
id,name,...
1,basic single,...
2,basic double,...
...
7,honeymoon,...
...

table: rooms
id,id_room_type,room_number,smoking,...
1,2,1,0,...
...

table: reservations
id,id_room,date_start,date_end,...
1,1,200600401,20060408,...
...

Then to find free rooms your query would be something like
SELECT rooms.*
FROM rooms LEFT JOIN reservations
ON reservations.id_room = rooms.id
WHERE date_start > '$date' OR date_end < '$date'

[enim213]

hi, ummm.. you want to find free rooms from selected date..
if you're using dropdown to find date.. use mysql between.

if you have further questions.. let us know.

enim :D

[enim213]

sample query: uses dropdown selection on dates

SELECT rooms.*
FROM rooms LEFT JOIN reservations
ON reservations.id_room = rooms.id
where calldate between '".$_GET['yearfrom']."-".$_GET['monthfrom']."-".$_GET['dayfrom']." ".$_GET['timefrom']."' and '".$_GET['yearto']."-".$_GET['monthto']."-".$_GET['dayto']." ".$_GET['timeto']."'";

[derekcpt]

sample query: uses dropdown selection on dates

SELECT rooms.*
FROM rooms LEFT JOIN reservations
ON reservations.id_room = rooms.id
where calldate between '".$_GET['yearfrom']."-".$_GET['monthfrom']."-".$_GET['dayfrom']." ".$_GET['timefrom']."' and '".$_GET['yearto']."-".$_GET['monthto']."-".$_GET['dayto']." ".$_GET['timeto']."'";

Hi.

Thanx for the reply.

I'm quickly training myself the JOIN method.

You see, I can figure this out, but the problem is that it has display available rooms between selected dates. So I don't know how I am going to achieve this, but I'm going to keep you posted. Also, if somebody has a proper solution, please drop me a post.

Thanx guys.

[derekcpt]

Ok, I have tried to put something together.

I really need the advice of a guru here ;-)

Please see the files below, but note that the post function does not work / useless at this stage.

Please, anybody, please assist.

SQL Data

DROP TABLE IF EXISTS `booked`;
CREATE TABLE IF NOT EXISTS `booked` (
  `bookID` int(11) NOT NULL auto_increment,
  `RoomName` text,
  `CheckIn` date NOT NULL default '0000-00-00',
  `CheckOut` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`bookID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `rooms`;
CREATE TABLE IF NOT EXISTS `rooms` (
  `roomID` int(11) NOT NULL auto_increment,
  `RoomName` text,
  `Desc` text,
  `Rate` text,
  PRIMARY KEY  (`roomID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `rooms` (`roomID`, `RoomName`, `Desc`, `Rate`) VALUES
(1, 'Room 1', 'This is room 1', '150.00'),
(2, 'Room 2', 'This is room 2', '150.00'),
(3, 'Room 3', 'This is room 3', '200.00'),
(4, 'Room 4', 'This is room 4', '200.00'),
(5, 'Room 5', 'This is room 5', '250.00'),
(6, 'Room 6', 'This is room 6', '400.00'),
(7, 'Room 7', 'This is room 7', '550.00'),
(8, 'Room 8', 'This is room 8', '550.00'),
(9, 'Room 9', 'This is room 9', '550.00'),
(10, 'Room 10', 'This is room 10', '550.00');

Then the file named index.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<p><a href="addroom.php">add room</a><br>
  <a href="booking.php">add booking</a></p>
<form name="form1" method="post" action="results.php">
  <p>Check In<br>
    <input name="CheckIn" type="text" id="CheckIn" value="YYYYMMDD"> 
  </p>
  <p>CheckOut<br>
    <input name="CheckOut" type="text" id="CheckOut" value="YYYYMMDD">
</p>
  <p>
    <input type="submit" name="Submit" value="Submit">
</p>
</form>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>

Page names addroom.php

<?php require_once('Connections/join.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO rooms (roomID, RoomName, `Desc`, Rate) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['roomID'], "int"),
                       GetSQLValueString($_POST['RoomName'], "text"),
                       GetSQLValueString($_POST['Desc'], "text"),
                       GetSQLValueString($_POST['Rate'], "text"));

  mysql_select_db($database_join, $join);
  $Result1 = mysql_query($insertSQL, $join) or die(mysql_error());

  $insertGoTo = "index.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">RoomName:</td>
      <td><input type="text" name="RoomName" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Desc:</td>
      <td><input type="text" name="Desc" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Rate:</td>
      <td><input type="text" name="Rate" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="roomID" value="">
  <input type="hidden" name="MM_insert" value="form1">
</form>
<p>&nbsp;</p>
</body>
</html>

Page named booking.php

<?php require_once('Connections/join.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO booked (bookID, RoomName, CheckIn, CheckOut) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['bookID'], "int"),
                       GetSQLValueString($_POST['RoomName'], "text"),
                       GetSQLValueString($_POST['CheckIn'], "date"),
                       GetSQLValueString($_POST['CheckOut'], "date"));

  mysql_select_db($database_join, $join);
  $Result1 = mysql_query($insertSQL, $join) or die(mysql_error());

  $insertGoTo = "index.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_join, $join);
$query_rooms = "SELECT * FROM rooms ORDER BY RoomName ASC";
$rooms = mysql_query($query_rooms, $join) or die(mysql_error());
$row_rooms = mysql_fetch_assoc($rooms);
$totalRows_rooms = mysql_num_rows($rooms);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">RoomName:</td>
      <td>
        <select name="RoomName">
          <?php 
do {  
?>
          <option value="<?php echo $row_rooms['RoomName']?>" ><?php echo $row_rooms['RoomName']?></option>
          <?php
} while ($row_rooms = mysql_fetch_assoc($rooms));
?>
        </select>
      </td>
    <tr>
    <tr valign="baseline">
      <td nowrap align="right">CheckIn:</td>
      <td><input name="CheckIn" type="text" value="YYYYMMDD" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">CheckOut:</td>
      <td><input type="text" name="CheckOut" value="YYYYMMDD" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="bookID" value="">
  <input type="hidden" name="MM_insert" value="form1">
</form>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($rooms);
?>

Page named results.php

<?php require_once('Connections/join.php'); ?>
<?php
$PostCheckIn = $_POST['CheckIn'];
$PostCheckOut = $_POST['CheckOut'];

mysql_select_db($database_join, $join);
$query_selection = "select * from rooms LEFT JOIN booked on rooms.RoomName = Booked.RoomName";
$selection = mysql_query($query_selection, $join) or die(mysql_error());
$row_selection = mysql_fetch_assoc($selection);
$totalRows_selection = mysql_num_rows($selection);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="678" border="0">
  <tr>
    <td width="178"><strong>Name</strong></td>
    <td width="225"><strong>Desc</strong></td>
    <td width="133"><strong>Rate</strong></td>
    <td width="124"><strong>Booked</strong></td>
  </tr>
  <?php do { ?>
  <tr>
    <td><?php echo $row_selection['RoomName']; ?></td>
    <td><?php echo $row_selection['Desc']; ?></td>
    <td><?php echo $row_selection['Rate']; ?></td>
    <td><?php echo $row_selection['Date']; ?></td>
  </tr>
  <?php } while ($row_selection = mysql_fetch_assoc($selection)); ?>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
<p><a href="index.php">Index Page </a></p>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($selection);
?>

Please, anybody!

I just want toi have the available rooms displayed. That is it. Also, you will note that I joined the 2 tables, and that I joined it by the field RoomName. Now the RoomName result does not display.

Nonetheless, please help me cracking this.

[derekcpt]

Forgot to add:

On the same root level of the pages above, the collowing file in a folder named Connection:

Connection/join.php

<?php
$hostname_join = "server host";
$database_join = "databasename"; // I named it "join"
$username_join = "username";
$password_join = "pass";
$join = mysql_pconnect($hostname_join, $username_join, $password_join) or trigger_error(mysql_error(),E_USER_ERROR); 
?>