Chapter 14 · Databases & Forms

Databases, Tables & SQL

So far, all our data vanishes the instant the page finishes loading — variables live only for one request. Real applications need memory that survives: a database. This chapter introduces MySQL, tables, and the language used to talk to them: SQL.

Meet phpMyAdmin

XAMPP ships with phpMyAdmin, a friendly web page for managing MySQL. With Apache and MySQL running, visit:

http://localhost/phpmyadmin

You'll see existing databases on the left. We'll create our own. You can do everything by clicking buttons — but we'll learn the SQL commands too, because buttons don't work from PHP; SQL does. In phpMyAdmin, click the SQL tab to type commands directly.

Creating a database and a table

CREATE DATABASE school_db;

USE school_db;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_number VARCHAR(20) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    program VARCHAR(100) NOT NULL,
    fee_balance DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let's decode every piece — this vocabulary appears in every project you will ever build:

INSERT — adding rows

INSERT INTO students (student_number, full_name, program, fee_balance)
VALUES ('LGU2026-001', 'Chanda Mwila', 'BSc Computing', 5000.00);

INSERT INTO students (student_number, full_name, program, fee_balance)
VALUES
('LGU2026-002', 'Mutinta Habeenzu', 'LLB', 7200.00),
('LGU2026-003', 'Bwalya Kunda', 'BBA', 4500.00),
('LGU2026-004', 'Thandiwe Phiri', 'BSc Computing', 6100.00);

Notice we never mention id or created_at — AUTO_INCREMENT and DEFAULT fill them automatically. Text values go in single quotes; numbers don't.

SELECT — reading rows (the command you'll use most)

-- Everything from the drawer ( * means "all columns")
SELECT * FROM students;

-- Only some columns
SELECT full_name, program FROM students;

-- Filtering with WHERE
SELECT * FROM students WHERE program = 'BSc Computing';

-- Comparison and combining conditions
SELECT * FROM students WHERE fee_balance > 5000 AND program = 'LLB';

-- Pattern search: names containing "anda" anywhere (% = "anything here")
SELECT * FROM students WHERE full_name LIKE '%anda%';

-- Sorting: highest balance first
SELECT * FROM students ORDER BY fee_balance DESC;

-- Only the first 2 rows
SELECT * FROM students ORDER BY created_at DESC LIMIT 2;

Result of the first query — this shape (rows × columns) is what PHP will receive in the next chapter:

UPDATE — changing rows

UPDATE students
SET fee_balance = 3500.00
WHERE id = 1;

DELETE — removing rows

DELETE FROM students WHERE id = 4;

Two tables and the idea of relationships

Real systems have many drawers that reference each other. A courses table, and a marks table that links students to courses:

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) NOT NULL UNIQUE,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE marks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

A foreign key is a column that holds another table's id — a "see drawer S, card 3" note. It links the drawers together and MySQL refuses marks pointing at students who don't exist. To read linked data back in one combined result, SQL offers JOIN:

SELECT s.full_name, c.code, m.score
FROM marks m
JOIN students s ON s.id = m.student_id
JOIN courses  c ON c.id = m.course_id;

Don't worry about mastering JOINs today — our project uses a single table, and you can return to JOINs once the basics feel comfortable. Just remember: ids link tables together.

SELECT * FROM students
WHERE program = 'BSc Computing'
ORDER BY fee_balance DESC;