superlative Posted December 5, 2013 Share Posted December 5, 2013 Hi, I am building an online ticketing system for a theatre where there is allocated seating. There are 5 rows, each with 25 seats. E1 to E2 D1 to D25 C1 to C25 B1 to B25 A1 to A25 E row is at the front of the cinema. A is at the back. At the moment I have got as far as to check a database for booked seats, and then have two arrays - one with the seats that are booked, and one with all seats there are in the cinema. One array subtracts duplicates from the other, so that the seats we are left with are seats that have not been booked. I have the list arranged in A-Z order. So at the moment the online ticketing sells from back left (being A1) to the right and then on to the next row and so on. I have two issues, and I really need someone who has done alot of coding to explain the best way to do this. I need to have the seating allocation start from the centre of the front row (E) and work outwards. For example if someone books two seats and they are the first booking they may get E12/E15. Then next booking may be for four people and it would get four seats from the middle available maybe E8, E9, E10, E11. The next problem is that say if you had all but 2 seats booked in the first row. Seats E24 and E25 are left. Someone books seats online for 3 people, the computer would give them E24, E25 and D12 for example. I need the script to check to make sure the number of tickets booked all have seat numbers starting with the same row number. The maximum tickets that can be booked online is 5 in any one transaction. So I need it to look for up to 5 seats in a row, there cant be a booking with split rows. Again, any help appreciated. Please see below my current code: $ArraySeats = array('A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10', 'A11', 'A12', 'A13', 'A14', 'A15', 'A16', 'A17', 'A18', 'A19', 'A20', 'A21', 'A22', 'A23', 'A24', 'A25','B1', 'B2', 'B3', 'B4', 'B5', 'B6', 'B7', 'B8', 'B9', 'B10', 'B11', 'B12', 'B13', 'B14', 'B15', 'B16', 'B17', 'B18', 'B19', 'B20', 'B21', 'B22', 'B23', 'B24', 'B25','C1', 'C2', 'C3', 'C4', 'C5', 'C6', 'C7', 'C8', 'C9', 'C10', 'C11', 'C12', 'C13', 'C14', 'C15', 'C16', 'C17', 'C18', 'C19', 'C20', 'C21', 'C22', 'C23', 'C24', 'C25','D1', 'D2', 'D3', 'D4', 'D5', 'D6', 'D7', 'D8', 'D9', 'D10', 'D11', 'D12', 'D13', 'D14', 'D15', 'D16', 'D17', 'D18', 'D19', 'D20', 'D21', 'D22', 'D23', 'D24', 'D25','E1', 'E2', 'E3', 'E4', 'E5', 'E6', 'E7', 'E8', 'E9', 'E10', 'E11', 'E12', 'E13', 'E14', 'E15', 'E16', 'E17', 'E18', 'E19', 'E20', 'E21', 'E22', 'E23', 'E24', 'E25'); $result = mysql_query("SELECT seat_number FROM ticket_sales where seat_number <> '' AND void IS NULL AND session_id = $session_id ORDER BY seat_number ASC") or die(mysql_error()); // Set initial ArraySeatsTaken as blank so that it can never be null. $ArraySeatsTaken[] = ''; // keeps getting the next row until there are no more to get while($row = mysql_fetch_array( $result )) { $ArraySeatsTaken[] = $row['seat_number']; } $ArraySeatsAvail = array_diff($ArraySeats, $ArraySeatsTaken); // Set sequential keys for the array. $ArraySeatsAvail = array_values($ArraySeatsAvail); $seat_number[1] = $ArraySeatsAvail[0]; $seat_number[2] = $ArraySeatsAvail[1]; $seat_number[3] = $ArraySeatsAvail[2]; $seat_number[4] = $ArraySeatsAvail[3]; $seat_number[5] = $ArraySeatsAvail[4]; Quote Link to comment Share on other sites More sharing options...
Barand Posted December 5, 2013 Share Posted December 5, 2013 Something like this will find the available number of seats in the order you want (row E to A and giving priority to centre seats) $tktsRequired = 4; $session = 3; $sql = " SELECT session_id, row , CASE WHEN seatnumber < 13 THEN seatnumber - $tktsRequired ELSE seatnumber - blocksize END as fromseat , CASE WHEN seatnumber < 13 THEN seatnumber - 1 ELSE seatnumber - blocksize + $tktsRequired - 1 END as toseat FROM ( SELECT IF(@prevsess=session_id AND @prevrow=row AND @prevseat!=seatnumber-1, seatnumber-@prevseat-1, IF(@prevrow<>row, seatnumber-1, 0)) as blocksize , @prevrow := row as row , @prevseat := IF(@prevrow=row,seatnumber,0) as seatnumber , @prevsess := session_id as session_id FROM ticket_sales JOIN (SELECT @prevrow:='' , @prevseat:=0, @prevsess:=0) as init ORDER BY session_id, row, seatnumber ) as blocks WHERE blocksize >= $tktsRequired AND session_id = $session ORDER BY session_id, row DESC, IF(seatnumber<13, 13-seatnumber, seatnumber-12)"; $res = $db->query($sql); /* SAMPLE RESULTS for BLOCK of 4 tickets ** +------------+------+----------+--------+ | session_id | row | fromseat | toseat | +------------+------+----------+--------+ | 3 | E | 3 | 6 | | 3 | D | 7 | 10 | | 3 | D | 2 | 5 | | 3 | B | 11 | 14 | | 3 | B | 1 | 4 | | 3 | A | 9 | 12 | | 3 | A | 3 | 6 | +------------+------+----------+--------+ */ My sample data: mysql> SELECT * FROM ticket_sales; +---------+------+------------+------+------------+ | idsales | row | seatnumber | void | session_id | +---------+------+------------+------+------------+ | 1 | A | 2 | NULL | 1 | | 2 | A | 5 | NULL | 1 | | 3 | A | 6 | NULL | 1 | | 4 | A | 7 | NULL | 1 | | 5 | A | 8 | NULL | 1 | | 6 | A | 10 | NULL | 1 | | 7 | A | 12 | NULL | 1 | | 8 | A | 13 | NULL | 1 | | 9 | A | 16 | NULL | 1 | | 10 | A | 17 | NULL | 1 | | 11 | A | 18 | NULL | 1 | | 12 | A | 19 | NULL | 1 | | 13 | A | 21 | NULL | 1 | | 14 | A | 22 | NULL | 1 | | 15 | A | 23 | NULL | 1 | | 16 | B | 8 | NULL | 1 | | 17 | B | 10 | NULL | 1 | | 18 | B | 15 | NULL | 1 | | 19 | B | 18 | NULL | 1 | | 20 | B | 21 | NULL | 1 | | 21 | B | 22 | NULL | 1 | | 22 | B | 23 | NULL | 1 | | 23 | B | 24 | NULL | 1 | | 24 | C | 3 | NULL | 1 | | 25 | C | 6 | NULL | 1 | | 26 | C | 7 | NULL | 1 | | 27 | C | 8 | NULL | 1 | | 28 | C | 9 | NULL | 1 | | 29 | C | 10 | NULL | 1 | | 30 | C | 18 | NULL | 1 | | 31 | C | 22 | NULL | 1 | | 32 | C | 23 | NULL | 1 | | 33 | C | 24 | NULL | 1 | | 34 | D | 1 | NULL | 1 | | 35 | D | 3 | NULL | 1 | | 36 | D | 4 | NULL | 1 | | 37 | D | 6 | NULL | 1 | | 38 | D | 7 | NULL | 1 | | 39 | D | 11 | NULL | 1 | | 40 | D | 14 | NULL | 1 | | 41 | D | 15 | NULL | 1 | | 42 | D | 17 | NULL | 1 | | 43 | D | 20 | NULL | 1 | | 44 | E | 1 | NULL | 1 | | 45 | E | 3 | NULL | 1 | | 46 | E | 6 | NULL | 1 | | 47 | E | 9 | NULL | 1 | | 48 | E | 12 | NULL | 1 | | 49 | E | 13 | NULL | 1 | | 50 | E | 14 | NULL | 1 | | 51 | E | 16 | NULL | 1 | | 52 | E | 18 | NULL | 1 | | 53 | E | 21 | NULL | 1 | | 54 | E | 24 | NULL | 1 | | 55 | E | 25 | NULL | 1 | | 56 | A | 3 | NULL | 2 | | 57 | A | 5 | NULL | 2 | | 58 | A | 10 | NULL | 2 | | 59 | A | 12 | NULL | 2 | | 60 | A | 15 | NULL | 2 | | 61 | A | 16 | NULL | 2 | | 62 | A | 20 | NULL | 2 | | 63 | A | 21 | NULL | 2 | | 64 | A | 24 | NULL | 2 | | 65 | A | 25 | NULL | 2 | | 66 | B | 1 | NULL | 2 | | 67 | B | 2 | NULL | 2 | | 68 | B | 3 | NULL | 2 | | 69 | B | 4 | NULL | 2 | | 70 | B | 6 | NULL | 2 | | 71 | B | 7 | NULL | 2 | | 72 | B | 11 | NULL | 2 | | 73 | B | 12 | NULL | 2 | | 74 | B | 13 | NULL | 2 | | 75 | B | 14 | NULL | 2 | | 76 | B | 15 | NULL | 2 | | 77 | B | 24 | NULL | 2 | | 78 | C | 1 | NULL | 2 | | 79 | C | 2 | NULL | 2 | | 80 | C | 3 | NULL | 2 | | 81 | C | 5 | NULL | 2 | | 82 | C | 7 | NULL | 2 | | 83 | C | 10 | NULL | 2 | | 84 | C | 15 | NULL | 2 | | 85 | C | 17 | NULL | 2 | | 86 | C | 20 | NULL | 2 | | 87 | C | 23 | NULL | 2 | | 88 | D | 1 | NULL | 2 | | 89 | D | 3 | NULL | 2 | | 90 | D | 5 | NULL | 2 | | 91 | D | 7 | NULL | 2 | | 92 | D | 10 | NULL | 2 | | 93 | D | 11 | NULL | 2 | | 94 | D | 12 | NULL | 2 | | 95 | D | 13 | NULL | 2 | | 96 | D | 15 | NULL | 2 | | 97 | D | 16 | NULL | 2 | | 98 | D | 18 | NULL | 2 | | 99 | D | 19 | NULL | 2 | | 100 | D | 21 | NULL | 2 | | 101 | D | 24 | NULL | 2 | | 102 | D | 25 | NULL | 2 | | 103 | E | 2 | NULL | 2 | | 104 | E | 5 | NULL | 2 | | 105 | E | 8 | NULL | 2 | | 106 | E | 12 | NULL | 2 | | 107 | E | 14 | NULL | 2 | | 108 | E | 16 | NULL | 2 | | 109 | E | 18 | NULL | 2 | | 110 | E | 19 | NULL | 2 | | 111 | E | 20 | NULL | 2 | | 112 | E | 22 | NULL | 2 | | 113 | E | 23 | NULL | 2 | | 114 | E | 25 | NULL | 2 | | 115 | A | 7 | NULL | 3 | | 116 | A | 8 | NULL | 3 | | 117 | A | 13 | NULL | 3 | | 118 | A | 17 | NULL | 3 | | 119 | A | 19 | NULL | 3 | | 120 | A | 20 | NULL | 3 | | 121 | B | 5 | NULL | 3 | | 122 | B | 9 | NULL | 3 | | 123 | B | 10 | NULL | 3 | | 124 | B | 15 | NULL | 3 | | 125 | B | 19 | NULL | 3 | | 126 | B | 20 | NULL | 3 | | 127 | B | 23 | NULL | 3 | | 128 | B | 24 | NULL | 3 | | 129 | C | 1 | NULL | 3 | | 130 | C | 3 | NULL | 3 | | 131 | C | 6 | NULL | 3 | | 132 | C | 8 | NULL | 3 | | 133 | C | 10 | NULL | 3 | | 134 | C | 11 | NULL | 3 | | 135 | C | 14 | NULL | 3 | | 136 | C | 17 | NULL | 3 | | 137 | C | 19 | NULL | 3 | | 138 | C | 22 | NULL | 3 | | 139 | D | 1 | NULL | 3 | | 140 | D | 6 | NULL | 3 | | 141 | D | 11 | NULL | 3 | | 142 | D | 12 | NULL | 3 | | 143 | D | 14 | NULL | 3 | | 144 | D | 16 | NULL | 3 | | 145 | D | 20 | NULL | 3 | | 146 | D | 23 | NULL | 3 | | 147 | D | 24 | NULL | 3 | | 148 | E | 1 | NULL | 3 | | 149 | E | 2 | NULL | 3 | | 150 | E | 7 | NULL | 3 | | 151 | E | 11 | NULL | 3 | | 152 | E | 14 | NULL | 3 | | 153 | E | 16 | NULL | 3 | | 154 | E | 17 | NULL | 3 | | 155 | E | 19 | NULL | 3 | | 156 | E | 20 | NULL | 3 | | 157 | E | 22 | NULL | 3 | | 158 | E | 23 | NULL | 3 | | 159 | E | 24 | NULL | 3 | +---------+------+------------+------+------------+ Quote Link to comment Share on other sites More sharing options...
Barand Posted December 5, 2013 Share Posted December 5, 2013 After posting the above I noticed that if the last ticket sold in a row was #21 then 22-25 would not be found as it looks for gaps between seat numbers. This revised version adds dummy sales for seat 26 for each row for each session to create the gap between 21 and 26. $tktsRequired = 4; $session = 3; $sql = " SELECT session_id, row , CASE WHEN seatnumber < 13 THEN seatnumber-$tktsRequired ELSE seatnumber-blocksize END as fromseat , CASE WHEN seatnumber < 13 THEN seatnumber-1 ELSE seatnumber-blocksize+$tktsRequired-1 END as seatto FROM ( SELECT IF(@prevsess=session_id AND @prevrow=row AND @prevseat!=seatnumber-1, seatnumber-@prevseat-1, IF(@prevrow<>row, seatnumber-1, 0)) as blocksize , @prevrow := row as row , @prevseat := IF(@prevrow=row,seatnumber,0) as seatnumber , @prevsess := session_id as session_id FROM ( SELECT row, seatnumber, session_id FROM ticket_sales UNION SELECT row, seatnumber, session_id FROM ( SELECT row, 26 as seatnumber FROM ticket_sales GROUP BY row ) as endseats CROSS JOIN ( SELECT DISTINCT session_id FROM ticket_sales ) as sessions ORDER BY session_id, row DESC, seatnumber ) as add_dummies JOIN (SELECT @prevrow:='' , @prevseat:=0, @prevsess:=0) as init ) as allsales WHERE blocksize >= $tktsRequired AND session_id = $session ORDER BY session_id, row DESC, IF(seatnumber<13, 13-seatnumber, seatnumber-12)"; $res = $db->query($sql); Quote Link to comment Share on other sites More sharing options...
Barand Posted December 8, 2013 Share Posted December 8, 2013 Have you considered the "visual" approach to seat booking? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.