A dark, readable study page for the supplied guide: PHP forms, request handling, cookies, sessions, file operations, SQL, MySQL connections, and local XAMPP execution.
Question-by-question guidance for the supplied assignment brief, linked to the relevant study-guide material below.
Question 1: Practical Project - Online Event Management System 100 marks
Design and develop a fully functional online event management system using PHP and MySQL for the backend, with HTML5, CSS3, JavaScript, or jQuery for the frontend.
PHPMySQLHTML5CSS3JavaScript / jQueryBuild from scratch
Assignment restrictions: Do not use CSS frameworks such as Bootstrap, Tailwind CSS, or Materialize, and do not use a content management system such as WordPress. All components must be developed manually.
1. User Registration & Authentication 15 marks
Implement registration with full name, email, password, and profile picture; enforce unique email validation; hash passwords with password_hash(); provide secure login, logout, forgot-password functionality, and session handling with $_SESSION.
Security guidance still applies when displaying values returned by JavaScript or search requests.
The study guide does not directly teach JavaScript validation, image previews, live search, or CSS UI design; investigate those implementation details separately while keeping the PHP validation and security rules.
7. Code Quality & Structure 10 marks
Organise folders such as includes, css, js, and uploads. Include clean, commented code, a SQL file, and a README or user guide.
Use Times New Roman, 12 pt, with 1.5 line spacing throughout the written assignment.
Use Harvard referencing for citations and references.
For the essay-style documentation, include a table of contents, introduction, main body with relevant subheadings, conclusion, and references.
Submit the assignment in PDF format on Moodle using the specified cover page and include a signed declaration of originality.
Check the weighting: The brief labels Question 1 as 100 marks, while the rubric also shows a separate system-functionality/screenshots row. Confirm the final weighting with the lecturer if the rubric totals appear inconsistent.
Test Focus Areas
A future home for confirmed test coverage and targeted revision guidance.
Coming soon
Test Focus Areas are coming soon
This section will be updated when the relevant guidance is available.
Exam Focus Areas
Confirmed exam focus areas will be added here once the assessment coverage is available.
Coming soon
Exam focus areas are coming soon
The current exam-focus content has been cleared and will be updated when the confirmed focus areas are provided.
Topic 1: PHP and HTML Forms
Forms are the bridge between browser input and PHP processing. The guide moves from basic form structure to request methods, superglobals, security, empty field handling, dynamic forms, and validation.
form actionGET and POSTsuperglobalsempty controlsXSS preventionvalidation helpers
1.1 How HTML Forms Work
A web form is a collection of controls inside a <form> element. Typical controls include text fields, passwords, checkboxes, radio buttons, file selectors, hidden fields, select lists, multi-select boxes, text areas, submit buttons, and reset buttons.
The form action says where the data goes. The form method says how it is sent. The guide presents GET as suitable for small amounts of data because values appear in the URL. POST sends data in the request body and is better when more data or more sensitive data is involved.
Method
Where Data Appears
Best Fit
Watch Out
GET
URL query string
Small requests, links, searches, bookmarkable pages
Visible in the address bar and limited by URL length
POST
Request body
Larger submissions and sensitive form content
Refreshing may ask the user to resubmit form data
1.3 Capturing Form Data With PHP
PHP uses superglobal arrays to expose request data in any scope. The field name becomes the array key, and the submitted value becomes the array value.
$_GET stores form values sent with method="get".
$_POST stores form values sent with method="post".
$_REQUEST combines GET, POST, and COOKIE values, so it can be ambiguous.
Correct pattern: If you know the form uses POST, read from $_POST instead of $_REQUEST.
1.4 PHP Form Data and Security
The guide warns that submitted values can include malicious HTML or JavaScript. Printing raw input can cause cross-site scripting. Escape output with htmlspecialchars() and validate values before using them.
Never do this: Do not display a submitted password back to the page, and do not use raw request values in HTML output.
1.5 Handling Empty Form Fields
Some controls send an empty value when left blank, while others send no key at all. If the key does not exist, reading it directly can produce an undefined index notice.
Control
If Empty or Unselected
PHP Check
Text, password, hidden, textarea, file input
Field name is sent with an empty value
empty($_POST["field"])
Checkbox, radio, list box, multi-select
Nothing may be sent
isset($_POST["field"])
Reset and push buttons
Nothing is sent
Do not rely on them as submitted values
if (isset($_POST["gender"])) {
echo htmlspecialchars($_POST["gender"]);
}
1.6 Dynamic Forms and Validation
The guide describes a PHP-generated form that checks missing required fields, highlights errors, keeps previous values, and shows a thank-you message only after successful validation.
processForm() checks submitted required fields.
displayForm($missingFields) redisplays the form with feedback.
setValue(), setChecked(), and setSelected() preserve user input.
validateField() marks missing fields for styling.
$missing = [];
foreach (["name", "email", "age"] as $field) {
if (empty($_POST[$field])) {
$missing[] = $field;
}
}
Revision task: Practise validating name, email, and age, then extend the same idea to login forms, multi-select lists, and file uploads.
Topic 1 Extended Code Example: Widget Club Form Handler
This example follows the guide's registration.html and process_registration.php idea, but it applies the later security and empty-field rules so the revision version is safer than the first teaching example.
<?php
// process_registration.php
// This script receives the Widget Club form through method="post".
// Every key below must match a form control's name attribute.
$requiredFields = ["firstName", "lastName", "password1", "password2", "gender"];
$missingFields = [];
foreach ($requiredFields as $fieldName) {
// empty() catches fields that exist but contain an empty string.
// It is useful for required text and password fields.
if (empty($_POST[$fieldName])) {
$missingFields[] = $fieldName;
}
}
// The newsletter checkbox is optional, so it may not exist in $_POST.
// isset() avoids an undefined index notice when the box was not ticked.
$newsletter = isset($_POST["newsletter"]) ? "yes" : "no";
// Escape output before displaying submitted text in HTML.
$firstName = htmlspecialchars($_POST["firstName"] ?? "");
$lastName = htmlspecialchars($_POST["lastName"] ?? "");
$favoriteWidget = htmlspecialchars($_POST["favoriteWidget"] ?? "");
$comments = htmlspecialchars($_POST["comments"] ?? "");
if ($missingFields) {
echo "<h1>Please complete the required fields</h1>";
echo "<p>Missing: " . htmlspecialchars(implode(", ", $missingFields)) . "</p>";
exit;
}
echo "<h1>Thank You</h1>";
echo "<dl>";
echo "<dt>First name</dt><dd>$firstName</dd>";
echo "<dt>Last name</dt><dd>$lastName</dd>";
echo "<dt>Gender</dt><dd>" . htmlspecialchars($_POST["gender"]) . "</dd>";
echo "<dt>Favorite widget</dt><dd>$favoriteWidget</dd>";
echo "<dt>Newsletter</dt><dd>$newsletter</dd>";
echo "<dt>Comments</dt><dd>$comments</dd>";
echo "</dl>";
// Exam trap: the guide's first handler printed the submitted password.
// A secure revision answer should never display password values back to the user.
?>
Topic 1 Extended Code Example: Redisplay A Form With Validation
The guide's generated-form section names helpers such as processForm(), displayForm(), setValue(), setChecked(), setSelected(), and validateField(). This compact version shows how those functions fit together.
<?php
// Single-page PHP form: first display, failed redisplay, and success display.
$requiredFields = ["name", "email", "age", "gender"];
function safe($value) {
return htmlspecialchars($value ?? "", ENT_QUOTES);
}
function setValue($fieldName) {
// Keeps typed values when the form is redisplayed after an error.
echo safe($_POST[$fieldName] ?? "");
}
function setChecked($fieldName, $fieldValue) {
// Keeps radio buttons or checkboxes selected after redisplay.
if (isset($_POST[$fieldName]) && $_POST[$fieldName] === $fieldValue) {
echo " checked";
}
}
function validateField($fieldName, $missingFields) {
// Adds an error class to a label when the field is missing.
echo in_array($fieldName, $missingFields, true) ? " class=\"error\"" : "";
}
function processForm($requiredFields) {
$missingFields = [];
foreach ($requiredFields as $fieldName) {
if (empty($_POST[$fieldName])) {
$missingFields[] = $fieldName;
}
}
if (!empty($_POST["email"]) && !filter_var($_POST["email"], FILTER_VALIDATE_EMAIL)) {
$missingFields[] = "email format";
}
if (!empty($_POST["age"]) && ($_POST["age"] < 18 || $_POST["age"] > 60)) {
$missingFields[] = "age range";
}
return $missingFields;
}
$missingFields = $_SERVER["REQUEST_METHOD"] === "POST" ? processForm($requiredFields) : [];
if ($_SERVER["REQUEST_METHOD"] === "POST" && !$missingFields) {
echo "<h1>Thanks, " . safe($_POST["name"]) . "</h1>";
echo "<p>Your submitted details passed validation.</p>";
} else {
?>
<form action="<?= safe($_SERVER["PHP_SELF"]) ?>" method="post">
<label<?php validateField("name", $missingFields); ?>>Name</label>
<input type="text" name="name" value="<?php setValue("name"); ?>">
<label<?php validateField("email", $missingFields); ?>>Email</label>
<input type="email" name="email" value="<?php setValue("email"); ?>">
<label<?php validateField("age", $missingFields); ?>>Age</label>
<input type="number" name="age" min="18" max="60" value="<?php setValue("age"); ?>">
<label><input type="radio" name="gender" value="F"<?php setChecked("gender", "F"); ?>> Female</label>
<label><input type="radio" name="gender" value="M"<?php setChecked("gender", "M"); ?>> Male</label>
<button type="submit">Submit</button>
</form>
<?php } ?>
Topic 1 Glossary: Forms, Request Data And Validation
<form>
The HTML element that groups form controls and defines how submitted data is sent.
action
The form attribute that names the page or script that receives the submitted data.
method
The form attribute that chooses the HTTP submission method, usually get or post.
GET
A request method that sends field names and values in the URL query string.
POST
A request method that sends form data in the request body and supports larger submissions.
name
The form-control attribute PHP uses as the key in $_GET or $_POST.
value
The submitted value paired with a form control's name.
$_GET
The PHP superglobal array containing values submitted with the GET method.
$_POST
The PHP superglobal array containing values submitted with the POST method.
$_REQUEST
A combined request array that can include $_GET, $_POST, and $_COOKIE; useful but less precise.
superglobal
A built-in PHP variable available in any scope, including inside functions.
htmlspecialchars()
Converts special HTML characters to entities so submitted text is safer to display.
filter_var()
A PHP validation/filtering function used in the guide with FILTER_VALIDATE_EMAIL.
isset()
Checks whether a submitted key exists before the script reads it.
empty()
Checks whether a submitted value is absent, blank, or otherwise empty.
array_key_exists()
Alternative key-existence check mentioned for avoiding undefined index notices.
Undefined index
A PHP notice caused by trying to read a field that was not submitted.
XSS
Cross-site scripting; a risk when user-submitted HTML or JavaScript is displayed as executable page content.
setValue()
A helper pattern that repopulates a text field after validation fails.
setChecked()
A helper pattern that keeps a checkbox or radio option selected after form redisplay.
setSelected()
A helper pattern that keeps the correct <option> selected in a dropdown.
validateField()
A helper pattern that marks missing fields with an error style.
password_hash()
Review-question function for storing passwords as hashes instead of plain text.
password_verify()
Review-question function for checking a typed password against a stored hash.
prepared statements
Review-question security technique for reducing SQL injection risk in login code.
$_FILES
The PHP superglobal used for uploaded file information in the file-upload exercise.
move_uploaded_file()
The PHP function used to move an uploaded file into a chosen folder such as uploads/.
This topic explains how PHP remembers information between requests. The key idea is state: HTTP forgets, while query strings, cookies, and sessions help applications remember.
Each browser request is independent. PHP starts with a clean slate, runs the script, then removes variables from memory. Applications such as shopping carts, login systems, forums, and multi-page forms need continuity between requests.
Query stringCookieSession IDServer state
Core idea: State means the application can connect a later request to earlier activity from the same user.
2.2 Cookies
A cookie is a small piece of data sent by the server and stored by the browser. On later requests, the browser sends it back, letting PHP identify preferences or repeat visits.
path, domain, and secure control where and how it is sent.
Headers first: Call setcookie() before any HTML output.
2.3 Sessions
Sessions store persistent variables on the server and link them to a user through a session ID. PHP commonly stores that ID in a browser cookie called PHPSESSID.
session_start();
$_SESSION["hits"] = ($_SESSION["hits"] ?? 0) + 1;
echo "This page has been viewed " . $_SESSION["hits"] . " times.";
session_start() loads or creates the session.
$_SESSION holds session values.
session_id() gets the current session ID.
session_destroy() destroys session data.
2.4 Cookies vs Sessions
Feature
Cookies
Sessions
Storage location
Browser/client
Server, linked by session ID
Size
Small; guide notes about 4 KB as a practical limit
Better for larger temporary state
Persistence
Can last beyond browser close if expiration is set
Usually expires when the browser closes unless configured
Best use
Preferences and identifiers
Login state, carts, multi-step forms
Do not expose session IDs: URL-based session IDs are convenient but can be copied, logged, or intercepted.
2.5 Combining Cookies and Sessions
The guide explains that temporary state can live in a PHP session, while longer-term identity or preference state can be stored in a cookie and then used to retrieve persistent data.
This example follows the guide's prefs.php, prefs_demo.php, and save_state.php colour-preference pattern. The important revision point is that setcookie() sends an HTTP header, so it must run before normal page output.
<?php
// save_state.php
// Cookies preserve small browser-side values between visits.
// The guide uses background colour as a simple preference example.
$validColours = [
"gray" => "#808080",
"white" => "#ffffff",
"black" => "#000000",
"blue" => "#0000ff",
"green" => "#008000",
];
if (isset($_POST["bgcolor"]) && array_key_exists($_POST["bgcolor"], $validColours)) {
// Store the chosen colour for one week.
// time() returns the current UNIX timestamp.
setcookie("bgcolor", $_POST["bgcolor"], time() + (60 * 60 * 24 * 7));
$backgroundName = $_POST["bgcolor"];
} elseif (isset($_COOKIE["bgcolor"]) && array_key_exists($_COOKIE["bgcolor"], $validColours)) {
// On a later visit, the browser sends the cookie back to PHP.
$backgroundName = $_COOKIE["bgcolor"];
} else {
$backgroundName = "gray";
}
$backgroundHex = $validColours[$backgroundName];
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Save It</title>
</head>
<body style="background: <?= htmlspecialchars($backgroundHex) ?>;">
<form action="<?= htmlspecialchars($_SERVER["PHP_SELF"]) ?>" method="post">
<label>Background colour</label>
<select name="bgcolor">
<option value="gray">Gray</option>
<option value="white">White</option>
<option value="black">Black</option>
<option value="blue">Blue</option>
<option value="green">Green</option>
</select>
<button type="submit">Save preference</button>
</form>
</body>
</html>
Topic 2 Extended Code Example: Session Login, Welcome And Logout
The review section asks for a hardcoded login, a welcome page, and logout. These three files show the session lifecycle: start the session, store the username, read it on another page, then destroy the session.
<?php
// login.php
session_start();
$validUsername = "student";
$validPassword = "richfield";
$error = "";
if ($_SERVER["REQUEST_METHOD"] === "POST") {
if ($_POST["username"] === $validUsername && $_POST["password"] === $validPassword) {
// Session data is stored on the server and linked to the browser by PHPSESSID.
$_SESSION["username"] = $_POST["username"];
header("Location: welcome.php");
exit;
}
$error = "Invalid username or password.";
}
?>
<form action="login.php" method="post">
<p><?= htmlspecialchars($error) ?></p>
<input type="text" name="username" placeholder="Username">
<input type="password" name="password" placeholder="Password">
<button type="submit">Log in</button>
</form>
<?php
// welcome.php
session_start();
if (!isset($_SESSION["username"])) {
header("Location: login.php");
exit;
}
echo "<h1>Welcome, " . htmlspecialchars($_SESSION["username"]) . "</h1>";
echo "<p><a href=\"logout.php\">Log out</a></p>";
// logout.php
session_start();
$_SESSION = [];
session_destroy();
header("Location: login.php");
exit;
?>
Topic 2 Glossary: State, Cookies And Sessions
state
Stored information that connects one browser request to another.
stateless protocol
A protocol where each request is independent; the guide identifies HTTP this way.
query string
Small state data placed in the URL after the question mark.
cookie
A small string of data sent by the server, stored by the browser, and returned on later requests.
setcookie()
The PHP function that sends a cookie header to the browser.
$_COOKIE
The PHP superglobal containing cookies sent back by the browser.
expire
The cookie parameter that controls when a cookie should stop being stored.
time()
Returns the current UNIX timestamp; used in cookie expiry calculations such as time() + 60 * 60 * 2.
path
The cookie parameter that limits which URLs on the server receive the cookie.
domain
The cookie parameter that limits which hostnames receive the cookie.
secure
The cookie parameter that tells the browser to send the cookie only over HTTPS.
4 KB
The guide's practical cookie-size limit from the original Netscape specification.
session
Server-side persistent variables linked to a user's browser by a session ID.
session_start()
Begins or resumes a PHP session and loads values into $_SESSION.
$_SESSION
The PHP superglobal array used to store session variables.
session_id()
Returns the current session identifier.
session_destroy()
Destroys the session data store; useful during logout or after final form submission.
PHPSESSID
The default cookie name PHP commonly uses to carry the session ID.
--enable-trans-id
The PHP compile option mentioned for automatically rewriting links with session IDs.
URL session ID
An alternative to cookie-based session IDs, but it can expose the ID in links and logs.
user_preference
The review-question cookie name used to store a user's favourite colour.
header()
A PHP function often used in login examples to redirect after state changes.
PHP can work with files and directories on the server. This topic covers file metadata, paths, open modes, reading, writing, pointer movement, copying, deleting, directory traversal, and file-type checks.
file metadatafopen modesfile handlesEOF and pointersdirectory handlesCSV and counters
3.1 Files, Directories, and Metadata
A file is an ordered sequence of bytes. A directory is a special file that stores file and subdirectory names plus pointers to their locations. PHP provides functions to inspect both.
file_exists() Checks whether a path exists.
filesize() Returns size in bytes.
fileatime() Last access timestamp.
filectime() Last change timestamp.
filemtime() Last modification timestamp.
basename() Extracts the filename from a path.
if (file_exists("example.txt")) {
echo filesize("example.txt");
echo date("Y-m-d", filemtime("example.txt"));
}
3.2 Opening Files With fopen()
fopen() returns a file handle used by later read, write, and close functions. The mode matters because some modes erase existing content.
Mode
Meaning
Main Risk
r
Read only; file must exist
Fails if missing
w
Write only; creates file or erases existing file
Destroys old content
a
Append only; writes at the end
Cannot read without another mode
r+, w+, a+
Read/write variants
w+ also erases old content
Portability note: The guide recommends binary mode such as rb when you want to avoid operating-system line-ending conversions.
3.3 Reading and Writing
Use the function that matches the job: whole-file reads for small files, line-by-line reads for logs or CSV data, and pointer-based reads when you need exact positions.
fread() Reads a specified number of bytes or characters.
fgetc() Reads one character at a time.
fgets() Reads one line at a time.
fgetcsv() Reads CSV rows into arrays.
file() Reads a file into an array of lines.
file_get_contents() Reads the whole file into a string.
fwrite() Writes through an open handle.
file_put_contents() Writes a full string without manually opening a handle.
$handle = fopen("data.txt", "a");
if ($handle) {
fwrite($handle, "New line" . PHP_EOL);
fclose($handle);
}
3.4 EOF and Random Access
PHP maintains an internal pointer for open files. Some functions move it forward as they read. You can also move it manually.
feof() checks whether the end of the file has been reached.
Directory handles work like file handles. Open the directory, read each entry, skip . and .., then close the handle.
$handle = opendir("files_dir");
if ($handle) {
while (($file = readdir($handle)) !== false) {
if ($file !== "." && $file !== "..") {
echo htmlspecialchars($file) . "<br>";
}
}
closedir($handle);
}
opendir() Opens a directory handle.
readdir() Reads the next entry.
closedir() Closes the directory handle.
rewinddir() Moves the pointer back to the first entry.
chdir() Changes the current working directory.
getcwd() Returns the current working directory.
mkdir() Creates a directory.
rmdir() Removes an empty directory.
3.6 Copy, Rename, Delete, and Test Types
File management functions can change data permanently, so check paths before using them.
copy($source, $destination) copies a file.
rename($old, $new) renames or moves a file.
unlink($path) deletes a file.
is_dir($path) checks for directories.
is_file($path) checks for regular files.
if (file_exists("report.txt")) {
copy("report.txt", "report_backup.txt");
}
Destructive action:unlink() deletes the file. Make sure the target path is correct before calling it.
Topic 3 Extended Code Example: Metadata Check And Hit Counter
This combines the guide's practical tasks for example.txt and the hit-counter example using count.dat. Notice how the script checks existence before reading and closes each file handle after use.
<?php
// file_status_and_counter.php
// Demonstrates file_exists(), filesize(), filemtime(), fopen(), fread(), fwrite(), and fclose().
$exampleFile = "example.txt";
if (file_exists($exampleFile)) {
echo "<p>File size: " . filesize($exampleFile) . " bytes</p>";
echo "<p>Last modified: " . date("Y-m-d H:i:s", filemtime($exampleFile)) . "</p>";
} else {
// file_put_contents() creates or overwrites the whole file in one call.
file_put_contents($exampleFile, "Hello, this is a new file!");
echo "<p>example.txt was created.</p>";
}
$counterFile = "counter.txt";
if (!file_exists($counterFile)) {
// Create the counter file with an initial zero.
file_put_contents($counterFile, "0", LOCK_EX);
}
$handle = fopen($counterFile, "r");
if (!$handle) {
die("Cannot read the counter file.");
}
// Read up to 20 characters and cast the result to an integer.
$counter = (int) fread($handle, 20);
fclose($handle);
$counter++;
$handle = fopen($counterFile, "w");
if (!$handle) {
die("Cannot open the counter file for writing.");
}
// Opening with "w" erases old content, then writes the new visit count.
fwrite($handle, (string) $counter);
fclose($handle);
echo "<p>You are visitor #$counter.</p>";
?>
Topic 3 Extended Code Example: Directory Listing And CSV Table
The guide treats directories like file-like structures with handles, then asks for a CSV table in the review. This example shows both: create/list files_dir, skip . and .., then read CSV rows with fgetcsv().
This topic moves from file storage limitations to relational databases, SQL statements, and PHP-to-MySQL connection code.
RDBMSembedded vs client-serverfruit tableCRUD SQLWHERE clausemysqli
4.1 Database Architecture and RDBMS Concepts
An embedded database runs inside the application and is usually local to one machine. A client-server database can serve multiple applications over a network and centralize administration and backups.
Concept
Meaning
Table
Structured storage made of rows and columns.
Row / record
One complete set of related values.
Column / field
One data attribute with the same meaning for every row.
Primary key
A unique identifier for each record.
Index
A sorted structure that speeds lookup but slows some writes.
4.2 Creating Databases and Tables
Use MySQL commands to create a database, select it, and define tables. End each SQL statement with a semicolon.
CREATE DATABASE mydatabase;
USE mydatabase;
CREATE TABLE fruit (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
color VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);
Key detail:AUTO_INCREMENT lets MySQL generate the primary key value automatically.
4.3 CRUD SQL
CRUD means create, read, update, and delete. In this guide, the fruit table is used to demonstrate the core SQL statements.
INSERT INTO fruit (name, color) VALUES ('banana', 'yellow');
SELECT * FROM fruit;
SELECT name, color FROM fruit WHERE id >= 2;
UPDATE fruit
SET name = 'grapefruit', color = 'yellow'
WHERE id = 2;
DELETE FROM fruit WHERE id = 2;
High-risk statements:DELETE FROM fruit; removes all rows. DROP TABLE fruit; removes the table itself.
4.4 Connecting PHP to MySQL
The guide explains mysqli and PDO, then shows procedural mysqli connection and query examples. Always check the connection and query result before processing rows.
The review work asks you to use an e-commerce schema with Customers and Orders. Practise joins, aggregation, table alteration, and deletion order.
SELECT c.Name, o.OrderID, o.TotalAmount
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
SELECT c.Name, SUM(o.TotalAmount) AS TotalSpent
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name;
Foreign-key thinking: Delete dependent orders before deleting the customer they reference.
4.6 SQL Vocabulary
SELECT Retrieves rows and returns a result set.
INSERT Adds rows to a table.
UPDATE Modifies existing rows.
DELETE Deletes rows but leaves the table.
CREATE Creates databases, tables, or indexes.
ALTER Changes table structure.
DROP Deletes a database or table structure.
WHERE Filters affected or returned rows.
Topic 4 Extended Code Example: Build And Query The Fruit Database
This complete SQL script follows the guide's mydatabase and fruit example. It shows the exam sequence: create the database, select it, create a table, insert rows, read rows, update one row, delete one row, and verify the result.
-- Run these statements at the mysql > prompt after logging in with:
-- mysql -u root -p
CREATE DATABASE mydatabase;
USE mydatabase;
-- The id column is the primary key.
-- AUTO_INCREMENT lets MySQL generate id values automatically.
CREATE TABLE fruit (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
color VARCHAR(30) NOT NULL,
PRIMARY KEY (id),
KEY (name)
);
-- Because id is AUTO_INCREMENT, only name and color are supplied.
INSERT INTO fruit (name, color) VALUES ('banana', 'yellow');
INSERT INTO fruit (name, color) VALUES ('tangerine', 'orange');
INSERT INTO fruit (name, color) VALUES ('plum', 'purple');
-- SELECT * retrieves every column from every row.
SELECT * FROM fruit;
-- Selecting named columns is clearer when you do not need the whole row.
SELECT name, color FROM fruit;
-- WHERE filters the rows returned by the query.
SELECT * FROM fruit WHERE name = 'banana';
SELECT * FROM fruit WHERE id >= 2;
-- Exam trap: always use WHERE when only one row should change.
UPDATE fruit
SET name = 'grapefruit', color = 'yellow'
WHERE id = 2;
-- DELETE removes rows but leaves the table structure in place.
DELETE FROM fruit WHERE id = 2;
-- DROP TABLE removes the whole table and all its data permanently.
-- DROP TABLE fruit;
Topic 4 Extended Code Example: Read Fruit Data From PHP
This follows the guide's get_fruit.php pattern. The PHP script connects with procedural mysqli, checks the connection, runs SELECT * FROM fruit, checks the query result, escapes output, and closes the connection.
<?php
// get_fruit.php
// Assumes mydatabase and fruit were already created in MySQL.
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";
// Create the connection to the MySQL server and selected database.
$conn = mysqli_connect($servername, $username, $password, $database);
// Connection errors should stop the script because queries cannot work without a connection.
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT * FROM fruit";
$result = mysqli_query($conn, $sql);
// Query errors are different from connection errors.
// mysqli_error($conn) reports what went wrong with the SQL query.
if (!$result) {
die("Query failed: " . mysqli_error($conn));
}
echo "<h1>Fruit</h1>";
echo "<ul>";
while ($row = mysqli_fetch_assoc($result)) {
// The guide escapes name and color before putting them into HTML output.
echo "<li>A "
. htmlspecialchars($row["name"])
. " is "
. htmlspecialchars($row["color"])
. "</li>";
}
echo "</ul>";
mysqli_close($conn);
?>
Topic 4 Exam-Style SQL Example: Customers And Orders
The review questions introduce Customers and Orders. Use this as a compact worked answer for joins, grouping, table alteration, and delete order.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE,
Country VARCHAR(50)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(10,2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
-- Customers who have placed at least one order.
SELECT DISTINCT c.*
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
-- Orders with the customer's name.
SELECT o.OrderID, o.OrderDate, o.TotalAmount, c.Name
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
-- Total amount spent by each customer.
SELECT c.CustomerID, c.Name, SUM(o.TotalAmount) AS TotalSpent
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name;
-- Manipulating the Customers table.
ALTER TABLE Customers ADD PhoneNumber VARCHAR(15);
ALTER TABLE Customers MODIFY Country VARCHAR(100);
-- Insert, update, and delete John Doe.
INSERT INTO Customers (Name, Email, Country)
VALUES ('John Doe', 'johndoe@example.com', 'USA');
UPDATE Customers
SET Email = 'john.doe@newmail.com'
WHERE Name = 'John Doe';
-- Delete dependent orders before deleting the referenced customer.
DELETE FROM Orders
WHERE CustomerID = (SELECT CustomerID FROM Customers WHERE Name = 'John Doe');
DELETE FROM Customers
WHERE Name = 'John Doe';
Topic 4 Glossary: Databases, SQL And MySQL From PHP
database
Structured storage designed to organize, search, filter, update, and manage large amounts of data efficiently.
embedded database
A database engine that runs inside the application and stores data on the same machine.
client-server database
A database architecture where a database server responds to client applications, often over a network.
database model
The way data is stored, organized, and accessed.
simple database
A key-based database model similar to an associative array, with no relationships between items.
relational database
A database that organizes data in tables and connects related data through common attributes.
RDBMS
Relational Database Management System; examples in the guide include Oracle, DB2, SQL Server, and MySQL.
table
A relational structure made of rows and columns.
row
One record in a table.
record
A complete set of related values in one row.
column
A field with the same meaning for every record in a table.
field
A named item of data such as name, color, or Email.
primary key
A unique identifier for each row, such as id or CustomerID.
index
A separate sorted list of column values that can make searches faster.
KEY
MySQL keyword for creating an index, used in the guide as KEY (name).
SQL
Structured Query Language, used to create, read, update, and delete database data and structures.
query
An SQL operation, especially one that retrieves records.
SELECT
Retrieves rows from one or more tables.
*
Wildcard in SELECT * meaning all columns.
FROM
The clause that names the table used by a query.
WHERE
The clause that filters rows returned, updated, or deleted.
INSERT
Adds a new row to a table.
REPLACE
Replaces a row when the same record already exists.
UPDATE
Changes existing row values; dangerous without WHERE.
DELETE
Removes rows from a table while leaving the table structure intact.
CREATE DATABASE
Creates a new MySQL database such as mydatabase.
CREATE TABLE
Creates a table and defines its fields, data types, keys, and constraints.
ALTER TABLE
Changes the structure of an existing table.
DROP TABLE
Permanently removes a table and all data inside it.
DROP DATABASE
Permanently removes a database and all its tables.
AUTO_INCREMENT
MySQL property that automatically generates the next numeric value for a key field.
NOT NULL
Constraint that prevents a field from being left empty at database level.
UNIQUE
Constraint that prevents duplicate values in a field such as Email.
FOREIGN KEY
A field that references a key in another table, such as Orders.CustomerID referencing Customers.CustomerID.
INNER JOIN
Combines matching rows from related tables.
GROUP BY
Groups rows for aggregate calculations such as total spending per customer.
SUM()
SQL aggregate function used to add numeric values such as TotalAmount.
mysqli
PHP's MySQL improved extension, available in procedural and object-oriented styles.
PDO
A PHP database option mentioned as another way to connect to databases.
mysqli_connect()
Creates a procedural MySQL connection from PHP.
mysqli_connect_error()
Returns the connection error message when mysqli_connect() fails.
mysqli_query()
Sends an SQL statement to MySQL through an open connection.
mysqli_error()
Returns the latest MySQL query error for a connection.
mysqli_fetch_assoc()
Fetches a result row as an associative array keyed by column name.
The addendum explains XAMPP as the local development package used to run PHP and MySQL on your computer before publishing a site.
ApacheMySQLPHPhtdocslocalhost.php files
5.1 What XAMPP Provides
XAMPP stands for cross-platform, Apache, MySQL, PHP, and Perl. It gives you a local web server and database setup for development and testing.
Apache serves web pages locally.
MySQL stores relational database data.
PHP executes server-side scripts.
htdocs is the folder where local projects are usually placed.
5.2 Run PHP
Create a folder inside htdocs, open that folder in your editor, save files with a .php extension, start Apache in XAMPP, then access the page through the browser.
<?php
echo "<h1>My Name is Derek</h1>";
?>
Syntax check: Make sure HTML tags are closed correctly inside echoed strings.
5.3 Localhost Path
If your folder is named Demo and your file is test.php, open it as:
http://localhost/Demo/test.php
Correct pattern: PHP must be served by Apache or another PHP-capable server. Opening a PHP file directly from Finder or File Explorer will not execute the PHP code.
Addendum Extended Code Example: First Local PHP Test Page
This example cleans up the addendum's first test.php idea. Save it inside your XAMPP project folder, start Apache, then open it through localhost.
<?php
// htdocs/Demo/test.php
// The file extension must be .php so Apache sends the page through PHP.
$studentName = "Derek";
$today = date("Y-m-d");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Test Page</title>
</head>
<body>
<h1>My Name is <?= htmlspecialchars($studentName) ?></h1>
<p>This page was processed by PHP on <?= htmlspecialchars($today) ?>.</p>
<p>If you can see today's date, Apache and PHP are working through XAMPP.</p>
</body>
</html>
Addendum Extended Code Example: XAMPP Run Checks
Use this as a short troubleshooting script when a practical example behaves oddly. It reinforces the addendum's main rule: PHP should be run through the local server, not opened directly as a file.
<?php
// diagnostics.php
// Place this in htdocs/Demo and open http://localhost/Demo/diagnostics.php
echo "<h1>XAMPP Diagnostics</h1>";
// Shows which PHP version Apache is using.
echo "<p>PHP version: " . htmlspecialchars(PHP_VERSION) . "</p>";
// Confirms the server-side folder PHP is running from.
echo "<p>Current folder: " . htmlspecialchars(getcwd()) . "</p>";
// Checks whether the request really came through localhost.
echo "<p>Server name: " . htmlspecialchars($_SERVER["SERVER_NAME"] ?? "unknown") . "</p>";
// A tiny write test for file-system examples.
$testFile = "xampp_write_test.txt";
if (file_put_contents($testFile, "Apache and PHP can write here.", LOCK_EX) !== false) {
echo "<p>Write test passed: $testFile was created or updated.</p>";
} else {
echo "<p>Write test failed. Check folder permissions.</p>";
}
echo "<p>Open this page with http://localhost/Demo/diagnostics.php, not file://...</p>";
?>
Addendum Glossary: XAMPP, Localhost And Running PHP
XAMPP
A free local development package for running web-server and database tools on a personal computer.
X
Cross-platform; the guide notes XAMPP is available for systems such as Windows, Linux, and OS X.
Apache
The web server component that serves local PHP pages through a browser.
MySQL
The database component used for relational database examples in the module.
PHP
The server-side scripting language used throughout the forms, sessions, files, and database topics.
Perl
The final component represented by the second P in XAMPP.
htdocs
The local web root folder where XAMPP projects are commonly placed.
Demo
The guide's example project folder created inside htdocs.
.php
The file extension that tells the server the file may contain PHP code.
<?php ?>
The opening and closing PHP tags used to place PHP code inside a file.
echo
The PHP keyword used in the addendum example to output HTML text to the browser.
localhost
The browser address that points to the local computer running Apache.
http://localhost/Demo/test.php
The guide's example URL for running test.php inside the Demo folder.
file://
A direct file-opening path; it should not be used to execute PHP because it bypasses Apache/PHP processing.
XAMPP Control Panel
The tool used to start Apache and MySQL modules for local testing.
phpMyAdmin
A browser-based tool commonly used with XAMPP to manage MySQL databases.
PHP_VERSION
A PHP constant that displays the PHP version being used by the local server.
getcwd()
Useful local diagnostic function that shows the server-side current working directory.
$_SERVER
A PHP superglobal containing server and request information such as SERVER_NAME.
Use these interactive study tools to prepare for assessment. They run entirely in this page and do not change real files, sessions, or databases.
Quiz Builder
Build a multiple-choice run from the local question bank. Your current quiz, submitted answers, marks, and attempt history are saved in this browser only.
Choose Questions Per Topic
Each topic has a bank of 40 questions. Set any topic to 0 if you want to skip it for this run.
Flashcards
Review key terms, then rate how confidently you recalled the answer.
Fill in the Code
Complete the missing line, then check your answer. Whitespace and optional semicolons are ignored.
Code Tracer
Follow a small PHP program one line at a time and watch the values change.
Concept Checker
Choose from 20 concept checks per topic, then use the explanation to strengthen your reasoning.
HTML Form and PHP Request Simulator
Enter values and compare the data a PHP script would receive through $_GET or $_POST.
Cookies and Sessions Visualizer
See why a cookie is browser-side, while session values stay server-side for the current visit.
Start a session to begin the simulation.
SQL Query Builder and Sandbox
Build a CRUD statement and safely run it against a small pretend fruit table.
Learning Lab
Use these interactive tools to practise each Internet Programming topic. Your quiz attempts are saved only in this browser.
Quiz Builder
Choose how many questions to draw from each topic, then answer and submit when ready.