[SOLVED] Availability : PHP/MySQL array isue - I'm so tired of struggling, I'm past dead

[janniesekind]

I've been sitting in front of this pc reading hundreds of threads and posts, and simply not getting anywhere. I'm so into reading, and really tired, becasue its (again) 3 in the morning, that I have to look up or stand up to realise where I am.

 

So if there is anyone who could assist, please do.

 

Its an availability script... yes, it is. When searching for rooms - the script is working fine when rooms are available for the selected dates. But once a room is taken / not available, I get the "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in..../connect_db.php on line 38" error.

 

So here is my code.

 

I THINK THE ERROR LIES SOMEWHERE HERE - PLEASE SCROLL DOWN FOR THE FULL SCRIPT

 

$start = "2009-05-01";
$end = "2009-05-02";



$sql = "SELECT DISTINCT room.RoomString ";
$sql .= "FROM bookings INNER JOIN room ON bookings.RoomString = room.RoomString ";
$sql .= "WHERE ((bookings.CheckOut > '" . $start . "' ";
$sql .= "AND bookings.CheckOut <= '" . $end . "') ";
$sql .= "OR (bookings.CheckIn < '" . $end . "' ";
$sql .= "AND bookings.CheckIn >= '" . $start . "') ";
$sql .= "OR (bookings.CheckIn <= '" . $start . "' ";
$sql .= "AND bookings.CheckOut >= '" . $end . "'))";

$unavailable_objets = db_query($database_name, $sql);

$unavailable_list = "";

while($unavailable_objets_ = fetch_array($unavailable_objets)) { $unavailable_list .= $unavailable_objets_["RoomString"] . ","; }
if($unavailable_list != "") { $unavailable_list = substr($unavailable_list, 0, strlen($unavailable_list)-1); } //shorts last comma


$sql = "SELECT DISTINCT * ";
$sql .= "FROM room ";
if($unavailable_list != "") { $sql .= "WHERE room.RoomString NOT IN ( " . $unavailable_list . " )"; }
$sql .= ";";

 

Apart from that - here's all the pages.

 

connect_db.php

 

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);

function db_query($db_name, $sql) {

$sql = str_replace("# ", "", $sql);
$sql = str_replace("#' ", "", $sql);

global $db_connection_type, $db_server_address, $db_user, $db_password;

switch($db_connection_type) {

case "odbc":
$db_connection = odbc_connect($db_name, $db_user, $db_password);
$result = odbc_exec($db_connection, $sql);
break;

case "mysql":
$db_connection = mysql_connect($db_server_address, $db_user, $db_password);
$result = mysql_db_query($db_name, $sql, $db_connection);
}

return $result;
}

function fetch_array($array) {

global $db_connection_type;

switch($db_connection_type) {

case "odbc":
$result = odbc_fetch_array($array);
break;

case "mysql":
$result = mysql_fetch_array($array);
}

return $result;
}
?>

 

 

config.php

 

<?php
$app_path = "http://localhost/avail/";
$db_server_address = "localhost";
$database_name = "room";
$db_user = "root";
$db_password = "";
$db_connection_type = "mysql";
$time_offset = 0;
?>

 

 

Then - probably the most important file, containing all the data ;

 

 

<?php
require "config.php";
require "connect_db.php";

$start = "2009-05-01";
$end = "2009-05-02";



$sql = "SELECT DISTINCT room.RoomString ";
$sql .= "FROM bookings INNER JOIN room ON bookings.RoomString = room.RoomString ";
$sql .= "WHERE ((bookings.CheckOut > '" . $start . "' ";
$sql .= "AND bookings.CheckOut <= '" . $end . "') ";
$sql .= "OR (bookings.CheckIn < '" . $end . "' ";
$sql .= "AND bookings.CheckIn >= '" . $start . "') ";
$sql .= "OR (bookings.CheckIn <= '" . $start . "' ";
$sql .= "AND bookings.CheckOut >= '" . $end . "'))";

$unavailable_objets = db_query($database_name, $sql);

$unavailable_list = "";

while($unavailable_objets_ = fetch_array($unavailable_objets)) { $unavailable_list .= $unavailable_objets_["RoomString"] . ","; }
if($unavailable_list != "") { $unavailable_list = substr($unavailable_list, 0, strlen($unavailable_list)-1); } //shorts last comma


$sql = "SELECT DISTINCT * ";
$sql .= "FROM room ";
if($unavailable_list != "") { $sql .= "WHERE room.RoomString NOT IN ( " . $unavailable_list . " )"; }
$sql .= ";";

$available_objects = db_query($database_name, $sql);
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../styles.css" rel="stylesheet" type="text/css">


</head>

<body>
<div align="center">
<table width="900" border="0" cellpadding="0" cellspacing="0">
<!--DWLayoutTable-->
<tr>
<td width="900" height="187" valign="top"><div align="center"><span id="title_" style="font-size:24px"><?php echo $start; ?>-<?php echo $end; ?></span>
<table width="299">
<tr class="page_nav_div">
<th width="168">Name</th>
<th width="115">Available</th>
</tr>
<?php while($available_objets_ = fetch_array($available_objects)) {


?>
<tr bgcolor="#F7F7F7">
<td nowrap><?php echo $available_objets_["Name"]; ?>
<div align="left"></div></td>
<td nowrap><?php echo $available_objets_["Desc"]; ?>
<div align="left"></div>
<div align="left"></div></td>
</tr>
<?php } ?>
</table>
</div></td>
</tr>
<tr>
<td height="37"> </td>
</tr>
</table>
</div>
</body>
</html>

 

 

And here is the SQL

CREATE TABLE IF NOT EXISTS `bookings` (
`bookID` int(10) NOT NULL auto_increment,
`RoomString` text,
`BookedBy` 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=7 ;

INSERT INTO `bookings` (`bookID`, `RoomString`, `BookedBy`, `CheckIn`, `CheckOut`) VALUES
(1, 'RO001', 'PER1', '2009-05-01', '2009-05-05'),
(2, 'RO001', 'PER2', '2009-05-10', '2009-05-15'),
(3, 'RO003', 'PER3', '2009-05-01', '2009-05-02');

CREATE TABLE IF NOT EXISTS `room` (
`roomID` int(10) NOT NULL auto_increment,
`RoomString` text,
`Name` text,
`Desc` text,
PRIMARY KEY (`roomID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `room` (`roomID`, `RoomString`, `Name`, `Desc`) VALUES
(1, 'RO001', 'Kamer1', 'Dit is kamer 1'),
(2, 'RO002', 'Kamer2', 'Dit is kamer 2'),
(3, 'RO003', 'Kamer3', 'Dit is kamer 3');

 

When configuring the database line when connecting to the database, to display errors, I get the following error :

 

(Database connection to display errors)

 

Error :

There was an error executing query 'ySQL stated: Unknown column 'RO001' in 'where clause'.

 

Please, if anyone could assist.

 

Thank you  ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ???