# Difference between revisions of "Transactions Airline"

## Airline seat booking

• Our airline has one aircraft with 20 seats numbered 1 to 20. Each seat has a row in the table seat
• When a customer wants a seat we find the lowest numbered seat and put the customer's name in that location.
• We record how much money our customers owe us in the table charges. Every time a debt is incurred we add a row to that table.

Here is a typical situation. Alice has booked seats 1 and 2. Bob has booked seats 3, 4 and 5

```MariaDB [scott]> select * from seat;
+----+-------+
| id | cust  |
+----+-------+
|  1 | alice |
|  2 | alice |
|  3 | bob   |
|  4 | bob   |
|  5 | bob   |
|  6 | NULL  |
|  7 | NULL  |
|  8 | NULL  |
|  9 | NULL  |
| 10 | NULL  |
| 11 | NULL  |
| 12 | NULL  |
| 13 | NULL  |
| 14 | NULL  |
| 15 | NULL  |
| 16 | NULL  |
| 17 | NULL  |
| 18 | NULL  |
| 19 | NULL  |
| 20 | NULL  |
+----+-------+
```

Each seat costs £100 and the charges table records each sale.

```MariaDB [scott]> select * from charge;
+-----+-------+--------+
| tid | cust  | amount |
+-----+-------+--------+
|   1 | alice |    100 |
|   2 | alice |    100 |
|   3 | bob   |    100 |
|   4 | bob   |    100 |
|   5 | bob   |    100 |
+-----+-------+--------+
```

## Using PHP to find and book a seat

The php program book_seat.php can be run form the command line.

• It takes the name of the customer as a parameter
• It finds the first free seat
• If no seat is available it halts with an error message
• Otherwise it
• Assigns the seat to the customer
• Adds the charge of £100 for that seat
```<?php
\$who = \$argv[1];
\$dbh = new PDO('mysql:host=localhost;dbname=scott','scott','tiger');
#Find the first free seat
\$sth = \$dbh->prepare("SELECT MIN(id) FROM seat WHERE cust IS NULL");
\$sth->execute();
\$a = \$sth->fetchAll()[0][0];
if (\$a==""){
die("No seats available, sorry.\n");
}
\$sth = \$dbh->prepare("UPDATE seat SET cust=? WHERE id=?");
\$sth->execute(array(\$who,\$a));
\$sth = \$dbh->prepare("INSERT INTO charge(cust,amount) VALUES (?,?)");
\$sth->execute(array(\$who,100));
echo "\$who gets seat \$a\n";
```

## Running the program book_seat.php

You can run the program book_seat.php from the command line like this: