Unit 11.3B · Term 3

PHP & Database

Most web applications need to store and retrieve data. PHP connects to MySQL — a relational database management system (RDBMS) — to perform CRUD operations: Create, Read, Update, Delete. XAMPP includes both Apache (for PHP) and MySQL, so you have a complete local development stack. This lesson covers SQL fundamentals, connecting PHP to MySQL, and building data-driven web pages.

Learning Objectives

11.5.3.4 Use server-side scripting to interact with a database (CRUD operations)

Lesson Presentation

11.3B-php-database.pdf · Slides for classroom use

View Download

Conceptual Anchor

The Library Analogy

A database is like a library. Tables are bookshelves (each holds a category). Rows are individual books (records). Columns are the book's properties (title, author, year). SQL is the librarian — you tell the librarian what you want, and they find, add, update, or remove books for you.

SQL Fundamentals

Setting Up the Database (phpMyAdmin)

Start Apache and MySQL in XAMPP Control Panel
Open http://localhost/phpmyadmin in your browser
Create a new database (e.g., school_db)
Create tables using the GUI or SQL commands

Creating a Table

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    age INT,
    grade CHAR(1),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Data Type	Description	Example
`INT`	Integer number	Age, ID
`VARCHAR(n)`	Variable-length string (max n chars)	Name, email
`CHAR(n)`	Fixed-length string	Grade (A, B, C)
`TEXT`	Long text	Comments, descriptions
`FLOAT / DOUBLE`	Decimal numbers	GPA, price
`DATE`	Date (YYYY-MM-DD)	Birth date
`TIMESTAMP`	Date and time	Created at
`AUTO_INCREMENT`	Automatically increases by 1	Primary key IDs

CRUD Operations — SQL

CREATE (Insert):

INSERT INTO students (name, email, age, grade)
VALUES ('Alice', 'alice@school.kz', 16, 'A');

INSERT INTO students (name, email, age, grade)
VALUES ('Bob', 'bob@school.kz', 17, 'B');

READ (Select):

-- All students
SELECT * FROM students;

-- Specific columns
SELECT name, grade FROM students;

-- With condition
SELECT * FROM students WHERE grade = 'A';

-- Sorted
SELECT * FROM students ORDER BY name ASC;

-- Limited
SELECT * FROM students LIMIT 5;

UPDATE:

UPDATE students SET grade = 'A' WHERE id = 2;

UPDATE students SET age = 17, email = 'newemail@school.kz' WHERE name = 'Alice';

DELETE:

DELETE FROM students WHERE id = 3;

-- WARNING: Without WHERE, deletes ALL rows!
DELETE FROM students;  -- Dangerous!

Connecting PHP to MySQL

1 Database Connection (db_connect.php)

<?php
    // Database connection settings
    $host = "localhost";
    $username = "root";         // Default XAMPP username
    $password = "";             // Default XAMPP password (empty)
    $database = "school_db";

    // Create connection using MySQLi
    $conn = mysqli_connect($host, $username, $password, $database);

    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
    // If no error, $conn is ready to use
?>

2 INSERT — Add a Student from a Form

add_student.php:

<!DOCTYPE html>
<html>
<head><title>Add Student</title></head>
<body>
    <h1>Add New Student</h1>
    <form method="POST" action="">
        <label>Name:</label>
        <input type="text" name="name" required><br>
        <label>Email:</label>
        <input type="email" name="email"><br>
        <label>Age:</label>
        <input type="number" name="age"><br>
        <label>Grade:</label>
        <select name="grade">
            <option value="A">A</option>
            <option value="B">B</option>
            <option value="C">C</option>
        </select><br>
        <button type="submit" name="submit">Add Student</button>
    </form>

    <?php
    if (isset($_POST["submit"])) {
        require_once "db_connect.php";

        $name  = mysqli_real_escape_string($conn, $_POST["name"]);
        $email = mysqli_real_escape_string($conn, $_POST["email"]);
        $age   = (int)$_POST["age"];
        $grade = mysqli_real_escape_string($conn, $_POST["grade"]);

        $sql = "INSERT INTO students (name, email, age, grade)
                VALUES ('$name', '$email', $age, '$grade')";

        if (mysqli_query($conn, $sql)) {
            echo "<p style='color:green'>Student added!</p>";
        } else {
            echo "<p style='color:red'>Error: " . mysqli_error($conn) . "</p>";
        }
        mysqli_close($conn);
    }
    ?>
</body>
</html>

3 SELECT — Display All Students

view_students.php:

<?php require_once "db_connect.php"; ?>
<!DOCTYPE html>
<html>
<head><title>Student List</title></head>
<body>
    <h1>All Students</h1>
    <table border="1">
        <tr>
            <th>ID</th><th>Name</th><th>Email</th>
            <th>Age</th><th>Grade</th><th>Actions</th>
        </tr>
        <?php
        $sql = "SELECT * FROM students ORDER BY name";
        $result = mysqli_query($conn, $sql);

        while ($row = mysqli_fetch_assoc($result)) {
            echo "<tr>";
            echo "<td>" . $row["id"] . "</td>";
            echo "<td>" . $row["name"] . "</td>";
            echo "<td>" . $row["email"] . "</td>";
            echo "<td>" . $row["age"] . "</td>";
            echo "<td>" . $row["grade"] . "</td>";
            echo "<td>";
            echo "<a href='delete_student.php?id=" . $row["id"] . "'>Delete</a>";
            echo "</td>";
            echo "</tr>";
        }
        mysqli_close($conn);
        ?>
    </table>
    <a href="add_student.php">Add New Student</a>
</body>
</html>

4 DELETE — Remove a Student

delete_student.php:

<?php
    require_once "db_connect.php";

    if (isset($_GET["id"])) {
        $id = (int)$_GET["id"];
        $sql = "DELETE FROM students WHERE id = $id";

        if (mysqli_query($conn, $sql)) {
            header("Location: view_students.php");  // Redirect back
        } else {
            echo "Error: " . mysqli_error($conn);
        }
    }
    mysqli_close($conn);
?>

Key MySQLi Functions

Function	Purpose	Returns
`mysqli_connect()`	Connect to database	Connection object or false
`mysqli_query($conn, $sql)`	Execute SQL query	Result set or true/false
`mysqli_fetch_assoc($result)`	Get next row as associative array	Array or null
`mysqli_num_rows($result)`	Count result rows	Integer
`mysqli_real_escape_string()`	Sanitize input against SQL injection	Escaped string
`mysqli_close($conn)`	Close the connection	true/false
`mysqli_error($conn)`	Get last error message	Error string

Pitfalls & Common Errors

SQL Injection

Never put user input directly into SQL! Use mysqli_real_escape_string() to sanitize all input. Without it, a user could enter ' OR 1=1 -- and access all data.

DELETE Without WHERE

DELETE FROM students; deletes ALL rows! Always include a WHERE clause. This is the most dangerous SQL mistake.

MySQL Not Running

If you get "Connection refused," check XAMPP Control Panel — MySQL must be started alongside Apache.

String Values Need Quotes in SQL

Strings must be in single quotes: WHERE name = 'Alice'. Numbers don't need quotes: WHERE age = 16.

Pro-Tips for Exams

CRUD Summary for Quick Recall

Create → INSERT INTO table (cols) VALUES (vals)
Read → SELECT cols FROM table WHERE condition
Update → UPDATE table SET col=val WHERE condition
Delete → DELETE FROM table WHERE condition

Always Mention Security

In exam answers about PHP + database, always mention input sanitization (preventing SQL injection) and input validation (checking data types and ranges). This shows deeper understanding and earns extra marks.

Graded Tasks

Remember

Define CRUD. Write the SQL to create a table called books with columns: id (auto-increment), title (varchar), author (varchar), year (int), price (float).

Understand

Explain the complete flow from form submission to database insertion. What role do HTML, PHP, SQL, and MySQL each play?

Apply

Create a complete "Book Store" web app with: (1) a form to add books, (2) a page to view all books in a table, (3) the ability to delete a book. Use XAMPP, PHP, and MySQL.

Analyze

Explain what SQL injection is. Show an example of malicious input and how mysqli_real_escape_string() prevents it.

Create

Build a student marks system: teachers can add students and their marks for 3 subjects. The system calculates averages and displays a class leaderboard sorted by average.

Self-Check Quiz

1. What does CRUD stand for?

Click to reveal: Create, Read, Update, Delete

2. What is the default XAMPP MySQL username and password?

Click to reveal: Username: "root", Password: "" (empty)

3. Which PHP function fetches one row from a query result?

Click to reveal: mysqli_fetch_assoc($result)

4. Why should you always use WHERE in DELETE/UPDATE statements?

Click to reveal: Without WHERE, the operation affects ALL rows in the table.

5. What is SQL injection and how do you prevent it?

Click to reveal: SQL injection is when malicious SQL is inserted via user input. Prevent it with mysqli_real_escape_string() or prepared statements.