How to check repetitions in the database and issue a number?

Question

PHP

Hello. I am writing a script for booking hotel rooms.
There are two tables like this:
allnomer sqlfiddle.com/#!9/75102 where all numbers are listed. The hotel has 3 lux rooms: 301, 302 and 303
and main sqlfiddle.com/#!9/02c4e where all reservations are listed.
I'm trying to make a script that will check the rooms for occupancy, and if there is a free room, it will issue this number to the reservation.
A little more. I have a check in date and a check out date for a new reservation: 2016-11-28 and 2016-11-30. This is a date range. That is, people are going to live in the hotel on the 28th, 29th and 30th. The hotel has already booked two rooms. The first (301) for the dates from 2016-11-29 to 2016-12-01, the second (302) for the dates from 2016-12-01 to 2016-12-02. A person cannot call in room 301, since this number will be busy on the 29th and 30th. We have two numbers left: 302 and 303. The 302 number is only occupied by 01 and 02. None of the dates intersect and therefore the 302 number suits us. The $namenomer variable takes the value 302 and is substituted into the script.
Can you tell me how to check if the number is free, so that in case of at least one match, the next number is checked? The probability that at this stage there will not be a single free number is excluded. Before this stage, there is already a script that checks all numbers for availability. However, it checks all numbers. The same script is needed to check certain numbers.
This is the code I have now:

SELECT a.nomer
FROM allnomer a
LEFT JOIN main m ON
a.nomer = m.numbernomer AND (
m.datestart BETWEEN '2016-11-28' AND '2016-11-30' OR
m.dateend BETWEEN '2016-11-28' AND '2016-11-30' )
WHERE a.TYPE = 'lux' AND m.id IS NULL

And he processes these dates correctly. I get a 302 response.
But if the structure of the main table is like this sqlfiddle.com/#!9/fba1bd
And the query is like this:

SELECT a.nomer
FROM allnomer a
LEFT JOIN main m ON
a.nomer = m.numbernomer AND (
m.datestart BETWEEN '2016-12-23' AND '2016-12-23' OR
m.dateend BETWEEN '2016-12-23' AND '2016-12-23' )
WHERE a.TYPE = 'lux' AND m.id IS NULL

Then 301 is returned, even though 23 is in the main table's range of numbers.
It's the same with all the numbers that are in this interval, except for the 19th and 25th.

Answer 1

First, it is worth increasing the normalization:

create table rooms (id integer primary key, type varchar(7), number integer);

insert into rooms
    (id, type, number)
values
    (1, 'lux', 301),
    (2, 'lux', 302),
    (3, 'lux', 303);

create table booking (
  id integer primary key,
  start_date date,
  end_date date,
  room integer,
  foreign key(room) references rooms(id)
);
  
insert into booking (id, start_date, end_date, room)
values
    (1, '2016-11-29', '2016-12-01', 1),
    (2, '2016-12-01', '2016-12-02', 2),
    (3, '2016-12-02', '2016-12-02', 1),
    (4, '2016-12-02', '2016-12-02', 3);

Second, it's easier to check for overlapping date ranges:

select number from rooms
where id not in (
    select room from booking
    where start_date > '2016-11-28' and start_date < '2016-11-30'
) and type = 'lux'
order by number
limit 1;

Will return the first room in order not booked ...