sql query to check availability of rooms between check in and check out dates using php form

Question

I have 2 tables in my database. First table is room_details and second table is booking_details.

room_details table:

 Room_Id |  Category  |  Type    |  Price
---------|------------|----------|---------
 R 001   |   DELUXE   |  NON-AC  |  2500
         |            |          |
 R 002   |   DELUXE   |  AC      |  3500
         |            |          |
 R 003   |   GRAND    |  NON-AC  |  4500
         |            |          |
 R 004   |   GRAND    |  AC      |  5500
         |            |          |
 R 005   |   SUITE    |  NON-AC  |  6500
         |            |          |
 R 006   |   SUITE    |  AC      |  7500

booking_details table:

 Room_Id  |  Booking_Id  |  Booking_Date  | Check_in_Date |  Check_out_Date
----------|--------------|----------------|---------------|-----------------
  R 001   |    B 001     |   2017-03-25   |   2017-03-26  |   2017-03-28
          |              |                |               |
  R 001   |    B 002     |   2017-03-25   |   2017-03-29  |   2017-04-01
          |              |                |               |
  R 004   |    B 003     |   2017-03-25   |   2017-03-26  |   2017-03-30
          |              |                |               |
  R 005   |    B 004     |   2017-03-25   |   2017-03-26  |   2017-03-28

Query:

$sql  = 'SELECT * FROM room_details
         WHERE room_id NOT IN(
               SELECT room_id FROM booking_details
               WHERE '$check_in' BETWEEN Check_in_Date AND Check_out_Date
                 OR '$check_out' BETWEEN Check_in_Date AND Check_out_Date
         )';

When I take the check_in and check_out dates as input from user using php to display all the available rooms then it displays a error as given below :

Parse error: syntax error, unexpected '$check_in' (T_VARIABLE)

What is wrong with this query?

This is the complete php code:

<?php

  $a = $_POST["data_1"];
  $b = $_POST["data_2"];

  $check_in = date("Y-m-d", strtotime($a));
  $check_out = date("Y-m-d", strtotime($b));

  $dbhost = "localhost:3306"; 
  $dbuser = "root"; 
  $dbpass = ""; 
  $dbname="hotel";

  //create connection
  $conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

  //check connection 
  if( $conn->connect_error ) { 
    die("Connection failed:" . $conn->connect_error);
  }

  $sql  = 'SELECT * FROM room_details
           WHERE room_id NOT IN(
             SELECT room_id FROM booking_details
             WHERE '$check_in' BETWEEN Check_in_Date AND Check_out_Date
               OR '$check_out' BETWEEN Check_in_Date AND Check_out_Date
           )';

  $result = $conn->query($sql);

 if ($result->num_rows > 0) {
   echo "<table border=1>
           <tr>
             <th>Room_Id</th>
             <th>Category</th>
             <th>Type</th>
             <th>Price</th>
           </tr>";

   // output data of each row
   while($row = $result->fetch_assoc()) {
     echo "<tr>";
     echo "<td>" . $row['Room_Id'] . "</td>";
     echo "<td>" . $row['Category'] . "</td>";
     echo "<td>" . $row['Type'] . "</td>";
     echo "<td>" . $row['Price'] . "</td>";
     echo "<td>" . "<a href = room_booking.php> BOOK NOW </a>" . "</td>";
     echo "</tr>";
   }
   echo "</table>";
 } else {
   echo "Error:" . $sql . "<br>" . $conn->error;
 }
 $conn->close();
?>

quotes, quotes, quotes, keep them in pairs, link them well, otherwise they become invalid. — RST, Mar 25 '17 at 10:04
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Lucero, Mar 25 '17 at 10:06

Gravy · Answer 1 · 2017-03-25T10:14:55.703

With single quotes you need to concatenate the string using .

$sql  = 'SELECT * FROM room_details WHERE room_id NOT IN(SELECT room_id FROM booking_details WHERE  "'.$check_in.'"  BETWEEN Check_in_Date AND Check_out_Date OR "'.$check_out.'"  BETWEEN Check_in_Date AND Check_out_Date)';

With double quotes, you can inject the $check_in and $check_out inline

$sql  = "SELECT * FROM room_details WHERE room_id NOT IN(SELECT room_id FROM booking_details WHERE  '$check_in'  BETWEEN Check_in_Date AND Check_out_Date OR '$check_out'  BETWEEN Check_in_Date AND Check_out_Date)";

For readability, I prefer sprintf.

$sql = sprintf('SELECT * FROM room_details WHERE room_id NOT IN(SELECT room_id FROM booking_details WHERE  "%s"  BETWEEN Check_in_Date AND Check_out_Date OR "%s"  BETWEEN Check_in_Date AND Check_out_Date)', $check_in, $check_out);

But really, (security wise) you should use PDO to bind your parameters. http://php.net/manual/en/pdostatement.bindparam.php

$stmt = $db->prepare('SELECT * FROM room_details WHERE room_id NOT IN(SELECT room_id FROM booking_details WHERE  :check_in  BETWEEN Check_in_Date AND Check_out_Date OR :check_out  BETWEEN Check_in_Date AND Check_out_Date)');
$stmt->bindParam(':check_in', $check_in, PDO::PARAM_STR);
$stmt->bindParam(':check_out', $check_out, PDO::PARAM_STR);
$stmt->execute();

score 0 · Answer 2 · answered Mar 25 '17 at 10:01

0

I think the issue is of string concatenation, so modify your query like:

$sql  = "SELECT * FROM room_details WHERE room_id NOT IN(SELECT room_id FROM booking_details WHERE "'.$check_in.'" BETWEEN Check_in_Date AND Check_out_Date OR "'.$check_out.'" BETWEEN Check_in_Date AND Check_out_Date)";

Try this query.

answered Mar 25 '17 at 10:01

Mayank Pandeyz

25,704
4
40
59

After replacing the query, I get Parse error: syntax error, unexpected ''.$check_in.'' (T_CONSTANT_ENCAPSED_STRING) – Neha Mar 25 '17 at 10:19
Is there any value exist in `$check_in`? – Mayank Pandeyz Mar 25 '17 at 10:21
because answer is wrong. Should be `'".$check_in."'` and not `"'.$check_in.'"` – Gravy Mar 25 '17 at 10:23

sql query to check availability of rooms between check in and check out dates using php form

2 Answers2