MYSQL NOT IN Query not working unless hard coded

Question

I'm fairly new to Javascript and PHP so I have no idea what I'm doing for the most part. I'm trying to make a Room Booking System.

$booking_id = '';
if(isset($_POST['edit_button_booking'])){
    $booking_id = $_POST['edit_id'];
}

echo "<script>alert('$booking_id')</script>";

$today_date = date('Y-m-d');
$room_query = "SELECT * FROM rooms";
$room_run = $conn->query($room_query);
if($room_run->num_rows>0){
    $options = mysqli_fetch_all($room_run, MYSQLI_ASSOC);
}

$start_from = 8;
$end_from = 21;
$start_to = $start_from+1;
$end_to = $end_from+1;
$from_booked = [];
$to_booked = [];
if(isset($_POST['value'])){
    $room_id = $_POST['value'];
    $specify_query = "SELECT * FROM rooms WHERE room_id = '$room_id'";
    $specify_run = $conn->query($specify_query);
    $row = mysqli_fetch_assoc($specify_run);
    $room_name = $row['room_name'];
    $disable_query = "SELECT booking_id, room_id, booking_date, booking_start, booking_end FROM booking WHERE booking_date = '$today_date' AND room_id = '$room_id' AND booking_id NOT IN (SELECT booking_id FROM booking WHERE booking_id = '$booking_id')";
    $disable = $conn->query($disable_query);
    if($disable->num_rows > 0){
        while($row = $disable->fetch_assoc()){
            $from_booked_hour = date_parse($row['booking_start'])['hour'];
            $to_booked_hour = date_parse($row['booking_end'])['hour'];
            $from_booked = array_merge($from_booked, range($from_booked_hour, $to_booked_hour-1));
            $to_booked = array_merge($to_booked, range($from_booked_hour+1, $to_booked_hour));
        }
    }
}

This is my code. What this code is supposed to do is to display the booked time slots for the room except for the chosen time slot.

And yet, it keeps on displaying both booked times when I want the 9 -12 time slot to be omitted from the time table The alert that I used show that $booking_id has the value '000053' which is the id of the booking that I want not shown in the time table. If I were to hard code it to $booking_id = '000053' or $booking_id = 53, it works.

$booking_id = '';
if(isset($_POST['edit_button_booking'])){
    $booking_id = $_POST['edit_id'];
}

$booking_id = 53;

echo "<script>alert('$booking_id')</script>";

$today_date = date('Y-m-d');
$room_query = "SELECT * FROM rooms";
$room_run = $conn->query($room_query);
if($room_run->num_rows>0){
    $options = mysqli_fetch_all($room_run, MYSQLI_ASSOC);
}

$start_from = 8;
$end_from = 21;
$start_to = $start_from+1;
$end_to = $end_from+1;
$from_booked = [];
$to_booked = [];
if(isset($_POST['value'])){
    $room_id = $_POST['value'];
    $specify_query = "SELECT * FROM rooms WHERE room_id = '$room_id'";
    $specify_run = $conn->query($specify_query);
    $row = mysqli_fetch_assoc($specify_run);
    $room_name = $row['room_name'];
    $disable_query = "SELECT booking_id, room_id, booking_date, booking_start, booking_end FROM booking WHERE booking_date = '$today_date' AND room_id = '$room_id' AND booking_id NOT IN (SELECT booking_id FROM booking WHERE booking_id = '$booking_id')";
    $disable = $conn->query($disable_query);
    if($disable->num_rows > 0){
        while($row = $disable->fetch_assoc()){
            $from_booked_hour = date_parse($row['booking_start'])['hour'];
            $to_booked_hour = date_parse($row['booking_end'])['hour'];
            $from_booked = array_merge($from_booked, range($from_booked_hour, $to_booked_hour-1));
            $to_booked = array_merge($to_booked, range($from_booked_hour+1, $to_booked_hour));
        }
    }
}

I don't know what is causing this issue. Any help will be appreciated.

The var_dump of both queries are as follows:

Generated SQL:

string(156) "SELECT booking_id, room_id, booking_date, booking_start, booking_end FROM booking WHERE booking_date = '2022-07-22' AND room_id = '1' AND booking_id != '53'"

Hard Coded:

string(156) "SELECT booking_id, room_id, booking_date, booking_start, booking_end FROM booking WHERE booking_date = '2022-07-22' AND room_id = '1' AND booking_id != '53'"

UPDATE:

The var_dump()'s I posted earlier were outside of the if(isset($_POST['value'])) block since I thought the variable $booking_id can be accessed in the if block as well. I tried to use var_dump() inside the if block and it turned out that the $booking_id was turning up blank.

string(154) "SELECT booking_id, room_id, booking_date, booking_start, booking_end FROM booking WHERE booking_date = '2022-07-22' AND room_id = '1' AND booking_id != ''"

This was the result of the var_dump() running inside of the if block. I don't understand why it is doing this though. My $today_date variable was defined outside of the if block as well and that's working.

**Warning**: You are wide open to [SQL Injections](https://php.net/manual/security.database.sql-injection.php) and should use parameterised **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. [Escaping is not enough](https://phpdelusions.net/top#escaping) — Phil, Jul 22 '22 at 01:45
$booked_id was an attempt to use a for loop to try and hard code the value to be the same as the $booking_id. I scraped that idea since it didn't work either. It still doesn't work with $booking_id in the query — Ram, Jul 22 '22 at 02:04
`echo` the generated SQL out and compare it to the hard-coded — Chris Haas, Jul 22 '22 at 02:34
I did as you asked but the output of the generated SQL and the hard coded are the exact same. — Ram, Jul 22 '22 at 02:57
Your queries are identical. It's literally impossible for them to return different results for the same data / state — Phil, Jul 22 '22 at 03:21

MYSQL NOT IN Query not working unless hard coded

0 Answers0