Comparing my user date checkin and checkout input to find apartment availability [php,mysql]

Question

Hi id like to search for available apartments within a specific date (user specified)

MySQL system_apartment table:

MySQL system_booking table:

This is the current code for my php process

if($_POST)
{
$user_checkin    = test_input($_POST['checkin']);
$user_checkout   = test_input($_POST['checkout']);
$user_noofpeople = test_input($_POST['noofpeople']);
$user_roomtype   = test_input($_POST['roomtype']);


try
{
    $stmt = $db_con->prepare("
        SELECT r.* 
          FROM system_apartment AS r 
         WHERE r.apartment_id 
        NOT IN
             (
                SELECT b.booking_apartmentid 
                  FROM system_booking AS b
                 WHERE (b.booking_date_checkout >= $user_checkin AND b.booking_date_checkin <= $user_checkout)
                    OR (b.booking_date_checkout <= $user_checkin  AND b.booking_date_checkin >= $user_checkout)
             )
    ");
    $stmt->execute();
    $count = $stmt->fetchAll();
    $count = count($count);

    if($count==0){
        echo "working"; //  not available
    }
    else{
        echo "not working"; //  not available
    }

}
catch(PDOException $e){
    echo $e->getMessage();
}

I would very much appreciate it if anyone can tell me where I'm going wrong!

My date is being sent as a (input type="text") is that the reason? or is there a work around if so because i am using jquery validate to make sure the field is being send — Luke Williams, Mar 26 '16 at 18:35
You cannot directly input `php `variables inside of a `mysqli prepared statement`. You must use `bind_param` and the placeholder `?` where you would normally use a variable and then assign it later. — Ohgodwhy, Mar 26 '16 at 18:36
please consider create a fiddle with the problem, http://sqlfiddle.com/ — Roger Russel, Mar 26 '16 at 18:39
`$user_checkin` and `$user_checkout` are what, integers or strings? Doubting you're checking for errors here. — Funk Forty Niner, Mar 26 '16 at 18:41
What the result are you getting, and what the expected result ? What do you mean by not working if count equals to 0? Would you consider using a join instead? — frz3993, Mar 26 '16 at 18:45
Moreover, the second WHERE clause is unneeded unless time can flow along negative direction. — Reversal, Mar 26 '16 at 18:45
see the answer below then. Nothing more I can do here, till I know exactly what animal we're dealing with. Good luck — Funk Forty Niner, Mar 26 '16 at 18:48
@Reversal how would you perform the sql statement using a join? It is intending to search the database with the given dates provided by the user to see if there are any available apartments with the specified apartment_type within the given dates. If there are some available then they should be displayed, otherwise if they are unavailable then don't display them. — Luke Williams, Mar 26 '16 at 20:21
`WHERE (b.booking_date_checkout >= :user_checkin AND b.booking_date_checkin <= :user_checkout)` is enough to select every apartment busy in the window `($user_checkin,$user_checkout)`. — Reversal, Mar 26 '16 at 21:32
@Reversal Did you miss out the last bit? Where it says ($user_checkin....) is that supposed to be : $stmt->execute(array(':user_checkin'=>$user_checkin, ':user_checkout'=>$user_checkout)); — Luke Williams, Mar 26 '16 at 22:23
Yes, I just wrote down WHERE clause. The execution can be done the way you said. — Reversal, Mar 27 '16 at 15:46

avip · Accepted Answer · 2016-03-26T18:52:51.147

Assuming your SQL is correct for your task, fix this bit of code to:

    $stmt = $db_con->prepare("
        SELECT r.* 
          FROM system_apartment AS r 
         WHERE r.apartment_id 
        NOT IN
             (
                SELECT b.booking_apartmentid 
                  FROM system_booking AS b
                 WHERE (b.booking_date_checkout >= :user_checkin AND b.booking_date_checkin <= :user_checkout)
                    OR (b.booking_date_checkout <= :user_checkin AND b.booking_date_checkin >= :user_checkout)
             )
    ");

$stmt->bindParam(':user_checkin', $user_checkin);
$stmt->bindParam(':user_checkout', $user_checkout);

From the docs:

The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style. Use these parameters to bind any user-input, do not include the user-input directly in the query.

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

Sidenote: the BETWEEN operator might be of use to you, doc page here

Sure it might be a possible fix, but what about explaining where they might have made errors? — Funk Forty Niner, Mar 26 '16 at 18:43
@Fred-ii- Added a reference to the potential explanation of errors/motivation for the answer — avip, Mar 26 '16 at 18:48
How would I use the between statement instead? Could you show how the code would look? sorry my brain is fried — Luke Williams, Mar 26 '16 at 18:54
@LukeWilliams You don't have to use it; in fact, it may not even be possible with the logic you have in your query. Just noting it in case it may come in handy, [this](http://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) is how it's used. — avip, Mar 26 '16 at 19:03

Comparing my user date checkin and checkout input to find apartment availability [php,mysql]

1 Answers1