troubleshooting Question

Ids not being selected on the database

Avatar of Jorge Batres
Jorge BatresFlag for United States of America asked on
DatabasesJavaScriptPHPMySQL ServerSQL
48 Comments2 Solutions65 ViewsLast Modified:
I have this PHP script to query a database and return available hotels and their respective room types for a particular date range (number of nights) but it is not working, and I don't get any errors on my site logs.

The destinations (Orlando, Cancun), etc are arrays with the ids for the hotels, and I just need to return available hotels by site (Cancun, Orlando, Cabo, and so on) and exclude the hotels for other destinations. I am planning on using a copy of the same script on each destination website.
here is the code:
<?php
header("content-type: application/json");

// Your mySQL database information
$db_host         = "localhost";
$database        = "xxxxxxx";
$db_user         = "xxxxxxx";
$db_pass         = "xxxxxxx";

// Update to use PDO
$conn = new PDO('mysql:host='.$db_host.';dbname='.$database, $db_user, $db_pass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$date = $_GET['adate'];

$hotels = array(
"Orlando" => array(99,100,101,102,107),
"Hilton Head" => array(86,87),
"Cancun" => array(90,91,92,93,97,98),
"Cabo" => array(94,95,96),
"Vallarta" => array(89),
);

$site = empty($_POST['site'])?'':$_POST['site'];

$ids = $site != "" ? $hotels[$site] : "";
$where = $site == "" ? " h.hotel_id = hr.hotel_id " : " h.hotel_id IN (" . implode(',', $ids) . ")";


$endDate = date( "Y-m-d", strtotime( "+". $_GET['days'] - 1 ." day", strtotime($date) ));

$sqlStatement = "SELECT h.hotel_id, h.name as hotel_name, rt.name as room_type, rt.room_type_id
   FROM hotel h, hotel_to_room_type hr, room_type rt
   WHERE $where
   AND hr.room_type_id = rt.room_type_id
   AND h.hotel_id NOT IN ( 
   SELECT appt.hotel_id
   FROM appointment appt
   LEFT JOIN available_status s ON (appt.available_status_id = s.available_status_id)
   WHERE appt.`date` >= :date
   AND appt.`date` <= :endDate
   AND s.name = 'Sold Out'
   AND (appt.room_type_id = rt.room_type_id OR appt.room_type_id = 1)
   GROUP BY 1)
   ORDER BY h.name, rt.name";

$statement = $conn->prepare($sqlStatement);

$statement->execute(array(':date'=>$date, ':endDate'=>$endDate));
   
echo '{"available_hotels":'.json_encode($statement->fetchAll(PDO::FETCH_ASSOC)).'}';


ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 48 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 48 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros