Difference between revisions of "Transactions Airline"

From SQLZOO
Jump to: navigation, search
(Airline seat booking)
(Using PHP to find and book a seat)
Line 67: Line 67:
 
echo "$who gets seat $a\n";
 
echo "$who gets seat $a\n";
 
</source>
 
</source>
 +
 +
==Running the program book_seat.php==
 +
You can run the program '''book_seat.php''' from the command line like this:
 +
[[File:book_seat.png]]

Revision as of 22:33, 2 November 2016

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: Book seat.png