Build a small database-driven website with WAMP: a live menu read from MySQL, an online order form that validates input and saves real orders, and a staff page that reports on them.
In the Sweet Escape project you designed the bakery’s database and built a static HTML site. Its order form could only pretend to work. Now you’ll connect the two, so the website reads from and writes to the database.
| Time | Work | You will need |
|---|---|---|
| 3 sessions of about 80 minutes, plus write-up | Individual or pairs (each person submits their own write-up) | A Windows PC with WampServer, a code editor (Notepad++ or VS Code) and the starter kit |
sweet-escape folder (zipped), with all pages working.http://localhost in your browser. You should see the WampServer home page.C:\wamp64\www\, so that you have C:\wamp64\www\sweet-escape\index.php.http://localhost/sweet-escape/. You will see a connection error. That’s expected, because the database doesn’t exist yet.Why not just double-click the .php file? PHP runs on the server. If you open the file directly, the browser gets raw PHP code that nothing has run. Always go through http://localhost/… so that Apache hands the page to PHP first.
http://localhost/phpmyadmin. Log in as root with no password, and choose the MySQL server.sweet_escape.sql from the kit and click Import (or Go). A new database, sweet_escape, appears on the left.http://localhost/sweet-escape/. The home page should now load and say how many items are on the menu.sweet_escape and then Designer (under More). Compare the diagram with your ERD from Module 1. Find the composite key in order_details.INSERT INTO product (name, category, price) VALUES ('Doubles', 'Street Food', 6);. What happens, and which constraint stopped it?DELETE FROM customer WHERE customer_id = 1;. What happens, and why is that a good thing?After experimenting, you can re-import sweet_escape.sql at any time to reset the data.
| File | What it does |
|---|---|
includes/db.php | Connects to MySQL with PDO. It also defines e(), which makes text safe to print in HTML. If you use MariaDB, change the port to 3307. |
includes/header.php, footer.php | The shared top and bottom of every page, with the navigation bar. |
index.php | Home page (finished, so read it as an example). |
menu.php | Shows the products from the database. TODOs 1–2 |
order.php + js/validate.js | The order form: its HTML, browser checks, server checks and saving. TODOs 3–8 |
staff-orders.php | Orders report and best sellers. TODOs 9–10, plus extension 11 |
css/style.css | The styles. Change the colours to make the site your own. |
1. Reading rows. query() runs fixed SQL, and fetchAll() returns an array of rows that you loop through.
$rows = $pdo->query('SELECT name, price FROM product')->fetchAll();
foreach ($rows as $r) {
echo '<p>' . e($r['name']) . ' costs $' . number_format($r['price'], 2) . '</p>';
}
2. Using anything the user typed. Never glue user input into SQL. Use a prepared statement: put ? placeholders in the SQL and pass the values separately.
$stmt = $pdo->prepare('SELECT * FROM customer WHERE phone = ?');
$stmt->execute([$phone]); // $phone is sent separately, never run as SQL
$customer = $stmt->fetch(); // one row, or false if nothing matched
SQL injection: if you build SQL like "... WHERE phone = '$phone'", a visitor could type ' OR '1'='1 and change what your query does. Prepared statements stop this. Cross-site scripting: if you print a customer’s name without e(), someone could “name” themselves <script>…</script> and run code in the staff’s browser.
menu.phpHint: without a category, use $pdo->query(...). With a category, use a prepared statement with WHERE category = ?. Either way, finish with $products = $stmt->fetchAll();.
menu.phpCheck: menu.php shows 10 cards, and Mauby appears faded and marked “Sold out today”. menu.php?category=Drinks shows only 2 cards. Now try menu.php?category=Soup. Why does it show everything instead of an error? (Read the code above your TODO.)
The form is where Module 2’s “form-enabled web pages” come together: HTML form elements, CSS, client-side scripting for validation, and a server that stores the result.
order.phporder.phporder.phpHint: copy the pattern of the name field that is already in the form. Choose the input type carefully (tel, email, date), because on phones they bring up the right keyboard.
js/validate.jsHint: the name check just above the TODO is a working example to copy. Use showError('phone', message), which returns true when the message is empty. Test it by submitting with fields left blank. The form must not send until it is correct.
Why validate twice? JavaScript runs in the visitor’s browser, and they can switch it off or edit it. It gives quick feedback, but it can’t be trusted. The PHP checks in TODO 4 run on the server, where the visitor can’t change them.
order.phpHint: preg_match() for the phone number, filter_var($email, FILTER_VALIDATE_EMAIL) for the email, and DateTime objects for the dates. $date->format('N') gives 1 for Monday up to 7 for Sunday.
order.phpHint: the order must be saved in this sequence: customer, then order, then order details. The details need the new order_id, which $pdo->lastInsertId() gives you. Wrap the three steps in beginTransaction() … commit(), and call rollBack() in the catch. Then a failure halfway through leaves no half-saved order.
Check: place an order, then look in phpMyAdmin. There should be one new row in orders and one row per item in order_details. Place another order with the same phone number: no new customer row should appear.
staff-orders.phpstaff-orders.phpCheck: with the sample data, order #1 shows “4 x Currants Roll, 2 x Sorrel Drink” and a total of $52.00. Currants Roll is the best seller, with 7 sold.
Extension: When the "Mark as ..." form is posted, UPDATE the order's status (only to 'Ready' or 'Collected') using a prepared statement, then reload the page. (staff-orders.php, TODO 11).
Test every rule with normal, boundary and erroneous data. Do each test twice: once with JavaScript on, and once with it switched off. (In Chrome or Edge, open DevTools with F12, press Ctrl+Shift+P and type “Disable JavaScript”.) Take a screenshot of each result.
| # | Test | Data type | Input | Expected result | Actual result | ✓/✗ |
|---|---|---|---|---|---|---|
| 1 | Valid order | Normal | 2 × Pine Tart, pickup next Wednesday | Saved, order number shown | ||
| 2 | Quantity | Boundary | Quantity 50, then 51 | 50 accepted, 51 rejected | ||
| 3 | Pickup day | Erroneous | A Sunday | “We are closed on Sundays and Mondays.” | ||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 | ||||||
| 11 | ||||||
| 12 |
e() prevent.localhost. What would the bakery need to put it on the internet, and what new risks would that bring (Module 3)?| Area | Marks | For full marks |
|---|---|---|
| Database warm-up (Part B) | 5 | Five correct queries with results. The constraint errors are explained. |
| Live menu (TODOs 1–2) | 5 | Products come from the database. The category filter works. Sold-out items are shown. Output is escaped. |
| Form elements and CSS (TODOs 3, 6, 7) | 5 | Correct input types, labels, error spans and a drop-down built from the database. The values refill after an error. |
| Client-side validation (TODO 8) | 6 | Every rule is checked in JavaScript with clear messages. The form won’t send until it is valid. |
| Server-side validation and saving (TODOs 4–5) | 8 | Every rule is re-checked in PHP. Prepared statements and a transaction are used. Returning customers are reused. |
| Staff report (TODOs 9–10) | 4 | Correct JOINs, totals and best-seller ranking. |
| Test plan (Part G) | 3 | At least 12 tests, covering normal, boundary and erroneous data, with screenshots. |
| Write-up (Part H) | 4 | All four points answered with reasons, not just statements. |
Extensions (TODO 11, a staff login with password_hash(), searching orders by phone) can earn credit that makes up for lost marks elsewhere, up to 40.