selecting consecutive numbers using SQL query

Question

Here is a theater seats booking plan.

Seat No Status
1 Booked
2 Available
3 Available
4 Available
5 Available
6 Available
7 Booked
8 Available
9 Available
10 Available

If someone wants to book 6 tickets, he will get Seat No. 2 to 6 and seat No. 8 And if someone wants to book only 5 tickets, he will get Seat No. 2 to 6

How do I know using SQL query (or PHP code) if the adjacent seats available are more than the seats requested?

Sequential seat selection is the primary goal that I need to achieve.

Answer 1

Edit: Since i have misunderstood the questions here an SQL statement that will return all the first free seat and the count of adjacent seats from the first free. The bigger counts of free seats come first.

SELECT count(1) free,(
 CASE status
  WHEN "Booked" THEN
   @prev:=NULL
  ELSE
   @prev:=COALESCE(cast(@prev as unsigned), seat_no)
  END) first
FROM 
 (SELECT @prev:=null) f,
 (SELECT seat_no, status FROM seats ORDER BY seat_no) seats
GROUP BY first
HAVING first>=0
ORDER BY 1 DESC, 2

So for your example it will return:

free | first
-----------
   5    2
   3    8

If you are only interested in the first sequencial seat that can fit your request and no more just add a condition on the free seat count, so if you want 3 seats adding free>=3 will do it :

SELECT count(1) free,(
 CASE status
  WHEN "Booked" THEN
   @prev:=NULL
  ELSE
   @prev:=COALESCE(cast(@prev as unsigned), seat_no)
  END) first
FROM 
 (SELECT @prev:=null) f,
 (SELECT seat_no, status FROM seats ORDER BY seat_no) seats
GROUP BY first
HAVING first>0 AND free>=3
LIMIT 1

this will output:

free | first
------------
   5    2

Answer 2

One pass. Put you number in place of ?. Gives you the number of the seat in the first sequence, when your requirement was met, or NULL if no sequence found.

SET @FOUND = 0;
SET @SEAT_MATCHED = NULL;

SELECT
    IF(@FOUND < ?,
        @FOUND := IF(status == 'Booked', 0, @FROM + 1),
        @SEAT_MATCHED := IFNULL(@SEAT_MATCHED, seat_no)
    )
FROM seats
ORDER BY seat_no

SELECT @SEAT_MATCHED;

More reading: Control Flow Functions, User Variables

NB! This approach is only applicable if there are few records in the analyzed interval!

Update. Perhaps you can store bitmask of booked seats in the row as an integer. For instance, for 16-seats row the number 36884 (1001000000010100 in binary) means 3rd, 5th, 13th and 16th seats are booked. It would cut down MySQL load. And then you could do the code like this:

<?php

header('Content-Type: text/plain');

// data you get from DB
$seats = bindec('1001000000010100');
$num_seats = 16;

// calculate consecutive free seats
$seats_info = array();
for ($i = 0; $i < $num_seats; $i++, $seats >>= 1) {
    if ($seats & 1) {
        if (isset($first)) {
            $seats_info[$first] = $i - $first;
            unset($first);
        }
    }
    else {
        if (!isset($first)) {
            $first = $i;
        }
    }
}

// output sequences
var_export($seats_info);

?>

This outputs:

array (
  0 => 2,
  3 => 1,
  5 => 7,
  13 => 2,
)

0 is the 1st seat.

Answer 3

it's better to represent Booked/Available as binary numbers (e.g. 1-free, 0-booked). If you do so, you can elegantly make use of aggregate functions:

  select seat as n from seats where
      $num_seats = (select sum(status) from seats
         where seat between n and n + $num_seats - 1)

Answer 4

I'd suggest one recursive algorithm using SQL and PHP. You need X seats.

1. Select all Available seats using SQL query, you receive N available seats (If N < X, error)

2. In php analyze the results and store them using the adjacent seats groups sizes as a key (there could be more than one group with the same size)

   '5' => ( 2, 3, 4, 5, 6 )

   '2' => ( 8, 9 )

3. Try to find the group with X seats

4. If not found, pick the closest group with size > X ( for X = 4 it's group '5' )

5. If no bigger groups found, take out the biggest available (group with size Y), then repeat steps 3 - 5 with X = X - Y

Answer 5

Try this:

select seat, status
from seats
where seat >= (
   select a.seat
   from seats a
      left join seats b on 
         a.seat < b.seat and
         b.seat < a.seat + 4 and
         b.status = 'Available'
   where a.status = 'Available'
   group by a.seat
   having count(b.seat)+1 = 4
   )
limit 4

This is set to select four consecutive seats. Adjust all instances of "4" to the desired number of seats to get what you want.

Answer 6

Why does it have to be in a Query? Isn't it more efficient to retrieve the whole table into PHP and work on it from there?