Check availability of apartments-CodePudding

I have 2 tables in database:

apartaments: id_apartmana

reservations: id_rezervacije, check_in, check_out

How can I construct a query for when user inputs 2 dates (arrival and departure of tourists) it shows available apartments in that period? I have if statement that doesn't do what it is supposed to do:

<?php
include_once 'database.php';

$check_in = $_POST['datum1'];
$check_out = $_POST['datum2'];


$result = mysqli_query($conn,"SELECT check_in, check_out, id_apartmana FROM rezervacije");

    while($row=mysqli_fetch_array($result))
{ 
if ($_POST['datum1'] < $row['check_in'] && $_POST['datum2'] <= $row['check_in'] or $_POST['datum1'] >= $row['check_out'] && $_POST['datum2'] >= $row['check_out']){
echo $row['id_apartmana'];

} else {
    echo"Apartman nije slobodan :(";
    break;
}
 
}

mysqli_close($conn);
?>

CodePudding user response：

What if you checked what reservations you have on selected dates and subtract these rooms from the list of all rooms?

SELECT id_rezervacije FROM reservations WHERE check_in BETWEEN date('datum1') AND date('datum2') AND check_out BETWEEN date('datum1') AND date('datum2');

CodePudding user response：

It is impossible to be sure why your PHP code is not working as you expect as you have not included enough information. There are two obvious potential issues -

maybe the dates received via POST are not in the correct format (need to be yyyy-mm-dd) to perform a meaningful comparison.
your combinations of ANDs and ORs need parentheses - (condition1 && condition2) or (condition3 && condition4) or (condition5 && condition6)

Even with the above issues resolved, your current code would still be unable to provide the result you are looking for as it is looking at reservations individually.

It would be much more efficient to do these checks in your SQL -

SET @start = DATE(20220108);
SET @end   = DATE(20220115);
SELECT * FROM apartmana WHERE NOT EXISTS (
    SELECT *
    FROM rezervacije
    WHERE rezervacije.id_apartmana = apartmana.id_apartmana AND (
        (check_in < @start AND check_out > @start) OR
        (check_in < @end   AND check_out > @end) OR
        (check_in > @start AND check_out < @end)
    )
);

Here's a db<>fiddle

Obviously, instead of using the SQL variables you will be using prepared statements.

<?php
include_once 'database.php';

$sql = '
SELECT * FROM apartmana WHERE NOT EXISTS (
    SELECT *
    FROM rezervacije
    WHERE rezervacije.id_apartmana = apartmana.id_apartmana AND (
        (check_in < ? AND check_out > ?) OR
        (check_in < ? AND check_out > ?) OR
        (check_in > ? AND check_out < ?)
    )
)';

/* create a prepared statement */
$stmt = $conn->prepare($sql);

/* bind parameters for markers */
$stmt->bind_param('ssssss', $check_in, $check_in, $check_out, $check_out, $check_in, $check_out);

$check_in = $_POST['datum1'];
$check_out = $_POST['datum2'];

/* execute query */
$stmt->execute();

$result = $stmt->get_result();

while($row = $result->fetch_array()) {
    // do something
}