Difference between revisions of "Transactions Airline"

From SQLZOO
Jump to: navigation, search
(Airline seat booking)
(Airline seat booking)
Line 41: Line 41:
 
  |  5 | 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
 +
<source lang=php>
 +
<?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";
 +
</source>

Revision as of 22:27, 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";