I have a parking reservation program with table

and when the booking takes place, 'id_prenotazione' is entered for the booking id and the 'value' from 1 becomes 0.

now the problem is that if I filter from these two dates he occupies me all the 'sala' in that date range and does not stop at just 'sala' with a single 'table' if I change the type of query trying to select only one sala with a table by setting an order by id to have them loaded in order of sala and table eg. from 15-06-2020 to 17-06-2020 I have a reservation then I make another one from 15-06-2022 to 20-06-2022 he does not book me every day but only makes 18-19-20 to finish the 'hall'

the question is: how can I execute the correct query to insert the reservation_id in a date range that occupies only one place and not all of them?

i do this

$sql = "select  * from risto_prenota where valore='1' AND id_prenotazione IS NULL and data between '$data_inizio' and '$data_fine' ORDER BY `risto_prenota`.`id` DESC";
$resultSet2 = mysqli_query($db, $sql); 
while ($row = mysqli_fetch_array($resultSet2)) {
$sala =  $row["sala"];
$tavolo=$row["tavolo"];}

and then this:

$sql = "select  * from risto_prenota where valore='1'  and data between '$data_inizio' and '$data_fine' order by id DESC";
$resultSet2 = mysqli_query($db, $sql); 
while ($row = mysqli_fetch_array($resultSet2)) {
$data_prenota=$row["data"];
//echo $data_prenota;
//echo "</BR>";
$id_pren=$row["id"];

//echo $id_pren;

$query = "UPDATE risto_prenota SET valore = '0', id_prenotazione='$id_prenotazione' where id = '$id_pren' and sala='$sala' and tavolo='$tavolo' ";
if ($link->query($query) === TRUE) {
} else {
} 

it doesn't work right if i make a reservation from 2022-06-11 to 2022-06-13 it becomes with code up:

then if i make a reservation from 2022-06-11 to 2022-06-15 it becomes:

skip days

CodePudding user response：

UPDATE risto_prenota
JOIN (
    SELECT sala, tavolo
    FROM risto_prenota
    WHERE data BETWEEN @from AND @till
    GROUP BY 1, 2
    HAVING NOT SUM(id_prenotazione IS NOT NULL)
    LIMIT 1
) updated_data USING (sala, tavolo)
SET risto_prenota.id_prenotazione = @id, 
    risto_prenota.valore = 0
WHERE risto_prenota.data BETWEEN @from AND @till;

where

• @from - booking start date
• @till - booking end date
• @id - the value to be assigned into id_prenotazione

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=92506e2b73374a128627eda9325a5eb0

PS. The query assumes that there is no "holes" in the dates (in risto_prenota.data).

PPS. You may add some ORDER BY into the subquery and provide definite place selection for booking. If the holes are possible than add AND COUNT(DISTINCT data) = DATEDIFF(@till, @from) 1 to the HAVING condition of the subquery.

PPPS. I recommend to create stored procedure instead of the query.