Reference Guide

Database Integration: MySQL with PHP on Ubuntu 24 Server

A Comprehensive Content Guide for Web Application Development

---

Table of Contents

1. Introduction & Architecture Overview

2. Installing & Configuring MySQL on Ubuntu 24

3. MySQL Server Administration Essentials

4. PHP & MySQL Connection Methods

5. CRUD Operations with PHP & MySQL

6. Prepared Statements & Parameterized Queries

7. Database Design & Schema Management

8. Working with Joins & Complex Queries in PHP

9. Transactions & Data Integrity

10. User Authentication with MySQL

11. Pagination, Search & Filtering

12. File Uploads & Binary Data Storage

13. Error Handling & Logging

14. Security Best Practices

15. Performance Optimization

16. Building a Complete CRUD Application

17. Troubleshooting Common Issues

---

1. Introduction & Architecture Overview

What is Database Integration?

Database integration is the process of connecting a web application's front-end and back-end logic to a persistent data storage system. In a typical LAMP stack (Linux, Apache, MySQL, PHP), the architecture follows a clear flow:

┌─────────────┐      ┌──────────────┐      ┌─────────────┐      ┌─────────────┐
│   Browser   │ ──── │ Apache (Web  │ ──── │  PHP Engine  │ ──── │   MySQL     │
│  (Client)   │ HTTP │   Server)    │      │ (App Logic)  │ SQL  │  (Database) │
└─────────────┘      └──────────────┘      └─────────────┘      └─────────────┘

Request Lifecycle:

1. The user's browser sends an HTTP request to the Apache web server.

2. Apache identifies the request as a PHP file and hands it to the PHP interpreter.

3. PHP executes the script, which may include SQL queries sent to the MySQL server.

4. MySQL processes the queries and returns result sets to PHP.

5. PHP formats the data into HTML (or JSON) and sends it back through Apache.

6. The browser renders the final response for the user.

Why MySQL?

MySQL is one of the most widely deployed relational database management systems (RDBMS) in the world. It powers major platforms including WordPress, Facebook, Twitter, and YouTube. Key advantages include open-source licensing, excellent performance, broad hosting support, a mature ecosystem of tools, and strong community documentation.

Why Ubuntu 24?

Ubuntu 24.04 LTS (Noble Numbat) provides long-term support through 2029, making it an ideal production server environment. It ships with modern versions of PHP (8.3+), MySQL (8.0+), and Apache (2.4+), ensuring compatibility with current web development standards and security patches.

PHP's MySQL Extensions: A Brief History

PHP has offered several extensions for MySQL connectivity over the years:

Extension	Status	Notes
mysql_*	Removed (PHP 7.0+)	Original procedural API. No prepared statements. Completely insecure by modern standards.
mysqli_*	Active & Recommended	"MySQL Improved." Supports both procedural and object-oriented syntax. Full prepared statement support.
PDO (PHP Data Objects)	Active & Recommended	Database-agnostic abstraction layer. Works with MySQL, PostgreSQL, SQLite, and many others. Named and positional placeholders.

Course Standard: Throughout this guide, we will primarily use MySQLi (object-oriented) for direct MySQL work and introduce PDO for comparison. Both are industry-standard approaches.

---

2. Installing & Configuring MySQL on Ubuntu 24

Step 1: Update the System

Always begin by updating the package lists and upgrading installed packages:

sudo apt update && sudo apt upgrade -y

Step 2: Install MySQL Server

sudo apt install mysql-server -y

Verify the installation and check the running version:

mysql --version
# Expected output: mysql  Ver 8.0.x-0ubuntu0.24.04.x for Linux on x86_64

sudo systemctl status mysql
# Should show "active (running)"

Step 3: Secure the Installation

MySQL ships with a security script that walks you through hardening the default configuration:

sudo mysql_secure_installation

This interactive script will prompt you to:

• Set up the VALIDATE PASSWORD component — Choose the password validation policy level (LOW = 8+ chars, MEDIUM = mixed case + numbers + special, STRONG = dictionary checks).

• Set/change the root password — Create a strong root password.

• Remove anonymous users — Answer Y to prevent unauthenticated access.

• Disallow root login remotely — Answer Y for security (root should only connect from localhost).

• Remove the test database — Answer Y to delete the default test database.

• Reload privilege tables — Answer Y to apply changes immediately.

Step 4: Install PHP and the MySQL Extension

sudo apt install php php-mysql php-cli libapache2-mod-php -y

This installs:

• php — The PHP interpreter (version 8.3+ on Ubuntu 24).

• php-mysql — The MySQLi and PDO MySQL drivers.

• php-cli — Command-line PHP for testing scripts without Apache.

• libapache2-mod-php — The Apache module that allows PHP processing.

Verify PHP installation:

php -v
# Expected output: PHP 8.3.x (cli)

Verify the MySQL extensions are loaded:

php -m | grep -i mysql
# Expected output:
# mysqli
# mysqlnd
# pdo_mysql

Step 5: Restart Apache

sudo systemctl restart apache2

Step 6: Test PHP Processing

Create a test file to verify Apache is processing PHP correctly:

sudo nano /var/www/html/info.php

<?php
phpinfo();
?>

Navigate to http://your-server-ip/info.php in a browser. You should see the PHP information page. Look for sections labeled mysqli and PDO to confirm the database drivers are loaded.

Security Warning: Always delete info.php after testing. It exposes sensitive server configuration details.

sudo rm /var/www/html/info.php

Step 7: Configure MySQL for PHP Access

By default, the MySQL root user authenticates using auth_socket (OS-level authentication), which means PHP scripts cannot connect as root using a password. You need to either change root's authentication method or (recommended) create a dedicated application user.

Option A: Create a Dedicated Application User (Recommended)

sudo mysql

CREATE USER 'webappuser'@'localhost' IDENTIFIED BY 'YourStr0ng!Passw0rd';
GRANT ALL PRIVILEGES ON *.* TO 'webappuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

Best Practice: In production, never grant ALL PRIVILEGES on *.*. Instead, grant only the specific permissions needed on specific databases. The above is acceptable only in a development/learning environment.

Option B: Change Root Authentication (Development Only)

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourRootP@ssw0rd';
FLUSH PRIVILEGES;
EXIT;

Step 8: Verify the Connection from Command Line

mysql -u webappuser -p
# Enter password when prompted

SHOW DATABASES;
-- You should see the default system databases:
-- information_schema, mysql, performance_schema, sys
EXIT;

---

3. MySQL Server Administration Essentials

Managing the MySQL Service

# Start MySQL
sudo systemctl start mysql

# Stop MySQL
sudo systemctl stop mysql

# Restart MySQL
sudo systemctl restart mysql

# Enable MySQL to start on boot
sudo systemctl enable mysql

# Check MySQL status
sudo systemctl status mysql

The MySQL Configuration File

The primary configuration file is located at /etc/mysql/mysql.conf.d/mysqld.cnf. Key settings to be aware of:

[mysqld]
# Network Settings
bind-address = 127.0.0.1       # Only accept local connections (secure default)
port = 3306                     # Default MySQL port

# Character Set (important for internationalization)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Performance Settings
max_connections = 151           # Maximum simultaneous connections
innodb_buffer_pool_size = 128M  # Memory allocated to InnoDB cache

# Logging
log_error = /var/log/mysql/error.log
general_log = 0                 # Enable for debugging (1 = on, 0 = off)
general_log_file = /var/log/mysql/general.log
slow_query_log = 1              # Log slow queries
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2             # Queries longer than 2 seconds are "slow"

After modifying the configuration file, restart MySQL:

sudo systemctl restart mysql

Creating Databases and Users via Command Line

-- Create a new database
CREATE DATABASE school_portal
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Create a user with access ONLY to this database
CREATE USER 'portal_user'@'localhost' IDENTIFIED BY 'S3cure_P@ss!';

-- Grant specific privileges on the specific database
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
  ON school_portal.*
  TO 'portal_user'@'localhost';

-- Apply the privilege changes
FLUSH PRIVILEGES;

-- Verify grants
SHOW GRANTS FOR 'portal_user'@'localhost';

Useful Administrative Commands

-- Show all databases
SHOW DATABASES;

-- Select a database to work with
USE school_portal;

-- Show all tables in the current database
SHOW TABLES;

-- Show the structure of a table
DESCRIBE students;
-- or
SHOW COLUMNS FROM students;

-- Show the CREATE TABLE statement (useful for documentation)
SHOW CREATE TABLE students;

-- Show currently connected users/processes
SHOW PROCESSLIST;

-- Show server variables
SHOW VARIABLES LIKE 'max_connections';

-- Show server status
SHOW STATUS LIKE 'Threads_connected';

MySQL Log Files on Ubuntu 24

Log File	Location	Purpose
Error Log	/var/log/mysql/error.log	Startup errors, crashes, warnings
General Query Log	/var/log/mysql/general.log	Every query executed (verbose, disable in production)
Slow Query Log	/var/log/mysql/slow.log	Queries exceeding long_query_time threshold
Binary Log	/var/log/mysql/binlog.*	All data-modifying statements (used for replication and recovery)

---

4. PHP & MySQL Connection Methods

Method 1: MySQLi (Object-Oriented)

This is the most common method for MySQL-specific applications. The object-oriented syntax is cleaner and preferred over the procedural alternative.

<?php
// Database connection parameters
$host = "localhost";
$username = "webappuser";
$password = "YourStr0ng!Passw0rd";
$database = "school_portal";

// Create connection
$conn = new mysqli($host, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    // In production, log the error instead of displaying it
    error_log("Database connection failed: " . $conn->connect_error);
    die("Connection failed. Please try again later.");
}

// Set the character set to utf8mb4 (supports emojis and special characters)
$conn->set_charset("utf8mb4");

echo "Connected successfully!";

// Always close the connection when done
$conn->close();
?>

Method 2: MySQLi (Procedural)

The procedural approach uses functions instead of objects. It works identically but is considered less elegant:

<?php
$host = "localhost";
$username = "webappuser";
$password = "YourStr0ng!Passw0rd";
$database = "school_portal";

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

// Check connection
if (!$conn) {
    error_log("Connection failed: " . mysqli_connect_error());
    die("Connection failed. Please try again later.");
}

mysqli_set_charset($conn, "utf8mb4");

echo "Connected successfully!";

mysqli_close($conn);
?>

Method 3: PDO (PHP Data Objects)

PDO provides a database-agnostic abstraction layer. If you ever switch from MySQL to PostgreSQL or SQLite, your code changes are minimal.

<?php
$host = "localhost";
$dbname = "school_portal";
$username = "webappuser";
$password = "YourStr0ng!Passw0rd";

// Data Source Name (DSN)
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

// PDO options for better security and error handling
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,   // Throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,         // Return associative arrays by default
    PDO::ATTR_EMULATE_PREPARES   => false,                    // Use real prepared statements
    PDO::ATTR_PERSISTENT         => false,                    // Don't use persistent connections
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    echo "Connected successfully with PDO!";
} catch (PDOException $e) {
    error_log("PDO Connection failed: " . $e->getMessage());
    die("Connection failed. Please try again later.");
}
?>

Creating a Reusable Connection File

In real applications, you never duplicate connection code. Create a single configuration file that all scripts include:

config/database.php

<?php
/**
 * Database Configuration
 * Include this file in any script that needs database access.
 * Usage: require_once 'config/database.php';
 */

// Database credentials
define('DB_HOST', 'localhost');
define('DB_NAME', 'school_portal');
define('DB_USER', 'webappuser');
define('DB_PASS', 'YourStr0ng!Passw0rd');
define('DB_CHARSET', 'utf8mb4');

/**
 * Get a MySQLi database connection
 * @return mysqli
 */
function getDbConnection(): mysqli {
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    
    if ($conn->connect_error) {
        error_log("DB Connection Error: " . $conn->connect_error);
        die("A database error occurred. Please try again later.");
    }
    
    $conn->set_charset(DB_CHARSET);
    return $conn;
}

/**
 * Get a PDO database connection
 * @return PDO
 */
function getPdoConnection(): PDO {
    $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
    
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];
    
    try {
        return new PDO($dsn, DB_USER, DB_PASS, $options);
    } catch (PDOException $e) {
        error_log("PDO Connection Error: " . $e->getMessage());
        die("A database error occurred. Please try again later.");
    }
}
?>

Usage in another script:

<?php
require_once __DIR__ . '/config/database.php';

$conn = getDbConnection();
// ... perform database operations ...
$conn->close();
?>

MySQLi vs. PDO: When to Use Which

Feature	MySQLi	PDO
Database Support	MySQL/MariaDB only	12+ databases (MySQL, PostgreSQL, SQLite, etc.)
API Style	Procedural + OOP	OOP only
Named Parameters	No (positional ? only)	Yes (:name and ?)
Prepared Statements	Yes	Yes
Fetch Modes	Limited	Extensive (FETCH_ASSOC, FETCH_OBJ, FETCH_CLASS, etc.)
Multiple Statements	multi_query() support	Not natively supported (safer)
Performance	Slightly faster for MySQL	Negligible difference
Portability	Not portable	Highly portable
Recommendation	MySQL-only projects, legacy codebases	New projects, multi-database environments

---

5. CRUD Operations with PHP & MySQL

CRUD stands for Create, Read, Update, Delete — the four fundamental operations for any database-driven application.

Setting Up a Sample Database

First, let's create the database schema we'll use throughout these examples:

-- Create the database
CREATE DATABASE IF NOT EXISTS school_portal
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE school_portal;

-- Students table
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    grade_level INT NOT NULL CHECK (grade_level BETWEEN 9 AND 12),
    gpa DECIMAL(3,2) DEFAULT 0.00,
    enrollment_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Courses table
CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_code VARCHAR(10) NOT NULL UNIQUE,
    course_name VARCHAR(100) NOT NULL,
    description TEXT,
    credits INT NOT NULL DEFAULT 3,
    max_enrollment INT NOT NULL DEFAULT 30,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Enrollments table (junction/bridge table for many-to-many relationship)
CREATE TABLE enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    grade VARCHAR(2) DEFAULT NULL,
    UNIQUE KEY unique_enrollment (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

-- Insert sample data
INSERT INTO students (first_name, last_name, email, grade_level, gpa) VALUES
('Alice', 'Johnson', '[email protected]', 11, 3.85),
('Bob', 'Smith', '[email protected]', 10, 3.42),
('Carol', 'Williams', '[email protected]', 12, 3.91),
('David', 'Brown', '[email protected]', 9, 3.15),
('Eva', 'Martinez', '[email protected]', 11, 3.67);

INSERT INTO courses (course_code, course_name, description, credits) VALUES
('CS101', 'Intro to Computer Science', 'Fundamentals of programming using Python.', 3),
('CS201', 'Data Structures', 'Arrays, linked lists, trees, and graphs.', 4),
('CS301', 'Web Development', 'Building database-driven web applications.', 4),
('MATH201', 'Calculus I', 'Limits, derivatives, and integrals.', 4),
('ENG101', 'English Composition', 'College-level writing and rhetoric.', 3);

INSERT INTO enrollments (student_id, course_id, grade) VALUES
(1, 1, 'A'),
(1, 3, 'A-'),
(2, 1, 'B+'),
(2, 4, 'B'),
(3, 2, 'A'),
(3, 3, 'A'),
(4, 1, NULL),
(5, 3, 'B+'),
(5, 5, 'A-');

---

CREATE — Inserting Data

Simple Insert (MySQLi)

<?php
require_once 'config/database.php';
$conn = getDbConnection();

$sql = "INSERT INTO students (first_name, last_name, email, grade_level, gpa)
        VALUES ('Frank', 'Garcia', '[email protected]', 10, 3.55)";

if ($conn->query($sql) === TRUE) {
    echo "New student added! ID: " . $conn->insert_id;
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

$conn->insert_id returns the auto-generated ID from the most recent INSERT operation. This is essential when you need the new record's ID immediately after creation.

Insert with Prepared Statement (MySQLi) — The Correct Way

<?php
require_once 'config/database.php';
$conn = getDbConnection();

// Data from a form submission (simulated)
$firstName = "Grace";
$lastName = "Lee";
$email = "[email protected]";
$gradeLevel = 11;
$gpa = 3.78;

// Prepare the statement with placeholders
$stmt = $conn->prepare(
    "INSERT INTO students (first_name, last_name, email, grade_level, gpa)
     VALUES (?, ?, ?, ?, ?)"
);

// Bind parameters: s = string, i = integer, d = double/decimal, b = blob
$stmt->bind_param("sssid", $firstName, $lastName, $email, $gradeLevel, $gpa);

// Execute
if ($stmt->execute()) {
    echo "Student added successfully! ID: " . $stmt->insert_id;
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

Insert with PDO

<?php
require_once 'config/database.php';
$pdo = getPdoConnection();

$firstName = "Grace";
$lastName = "Lee";
$email = "[email protected]";
$gradeLevel = 11;
$gpa = 3.78;

$sql = "INSERT INTO students (first_name, last_name, email, grade_level, gpa)
        VALUES (:first_name, :last_name, :email, :grade_level, :gpa)";

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':first_name'  => $firstName,
    ':last_name'   => $lastName,
    ':email'       => $email,
    ':grade_level' => $gradeLevel,
    ':gpa'         => $gpa,
]);

echo "Student added! ID: " . $pdo->lastInsertId();
?>

Notice: PDO supports named placeholders (:first_name) which makes the code more readable than positional ? marks, especially for queries with many parameters.

---

READ — Retrieving Data

Fetch All Rows

<?php
require_once 'config/database.php';
$conn = getDbConnection();

$sql = "SELECT id, first_name, last_name, email, grade_level, gpa
        FROM students
        WHERE is_active = 1
        ORDER BY last_name ASC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h2>Student Roster ({$result->num_rows} students)</h2>";
    echo "<table border='1' cellpadding='8'>";
    echo "<tr>
            <th>ID</th><th>Name</th><th>Email</th>
            <th>Grade</th><th>GPA</th>
          </tr>";
    
    while ($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row['id']) . "</td>";
        echo "<td>" . htmlspecialchars($row['first_name'] . " " . $row['last_name']) . "</td>";
        echo "<td>" . htmlspecialchars($row['email']) . "</td>";
        echo "<td>" . htmlspecialchars($row['grade_level']) . "</td>";
        echo "<td>" . htmlspecialchars(number_format($row['gpa'], 2)) . "</td>";
        echo "</tr>";
    }
    
    echo "</table>";
} else {
    echo "No students found.";
}

$conn->close();
?>

Critical: Always use htmlspecialchars() when outputting database values into HTML. This prevents Cross-Site Scripting (XSS) attacks by converting special characters like <, >, ", and & into their HTML entity equivalents.

Fetch Methods Comparison

<?php
// fetch_assoc() — Returns an associative array (most common)
$row = $result->fetch_assoc();
// $row['first_name'], $row['last_name'], etc.

// fetch_row() — Returns a numeric/indexed array
$row = $result->fetch_row();
// $row[0], $row[1], etc.

// fetch_object() — Returns an object
$row = $result->fetch_object();
// $row->first_name, $row->last_name, etc.

// fetch_all() — Returns ALL rows at once as an array (MySQLi 5.3+)
$allRows = $result->fetch_all(MYSQLI_ASSOC);
// Use with caution on large result sets (memory intensive)
?>

Fetch a Single Record by ID

<?php
require_once 'config/database.php';
$conn = getDbConnection();

$studentId = 3; // In practice, this comes from $_GET or a route parameter

$stmt = $conn->prepare("SELECT * FROM students WHERE id = ?");
$stmt->bind_param("i", $studentId);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows === 1) {
    $student = $result->fetch_assoc();
    echo "Name: " . htmlspecialchars($student['first_name'] . " " . $student['last_name']);
    echo "<br>Email: " . htmlspecialchars($student['email']);
    echo "<br>GPA: " . htmlspecialchars($student['gpa']);
} else {
    echo "Student not found.";
}

$stmt->close();
$conn->close();
?>

Fetch with PDO

<?php
require_once 'config/database.php';
$pdo = getPdoConnection();

// Fetch all rows
$stmt = $pdo->query("SELECT * FROM students WHERE is_active = 1 ORDER BY last_name");
$students = $stmt->fetchAll(); // Returns all rows as associative arrays (per our PDO config)

foreach ($students as $student) {
    echo htmlspecialchars($student['first_name'] . " " . $student['last_name']) . "<br>";
}

// Fetch single row by ID
$stmt = $pdo->prepare("SELECT * FROM students WHERE id = :id");
$stmt->execute([':id' => 3]);
$student = $stmt->fetch(); // Returns one row or false

if ($student) {
    echo "Found: " . htmlspecialchars($student['first_name']);
} else {
    echo "Not found.";
}
?>

---

UPDATE — Modifying Data

<?php
require_once 'config/database.php';
$conn = getDbConnection();

// Data to update (typically from a form)
$studentId = 2;
$newGpa = 3.65;
$newGradeLevel = 11;

$stmt = $conn->prepare(
    "UPDATE students SET gpa = ?, grade_level = ? WHERE id = ?"
);
$stmt->bind_param("dii", $newGpa, $newGradeLevel, $studentId);

if ($stmt->execute()) {
    if ($stmt->affected_rows > 0) {
        echo "Student updated successfully!";
    } else {
        echo "No changes were made (data may already be current, or student not found).";
    }
} else {
    echo "Update failed: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

Important: $stmt->affected_rows returns the number of rows actually changed. If you run an UPDATE with the same values that already exist, affected_rows will be 0 even though the query executed successfully. This is expected behavior.

Update with PDO

<?php
require_once 'config/database.php';
$pdo = getPdoConnection();

$stmt = $pdo->prepare(
    "UPDATE students SET gpa = :gpa, grade_level = :grade WHERE id = :id"
);
$stmt->execute([
    ':gpa'   => 3.65,
    ':grade' => 11,
    ':id'    => 2,
]);

echo $stmt->rowCount() . " row(s) updated.";
?>

---

DELETE — Removing Data

<?php
require_once 'config/database.php';
$conn = getDbConnection();

$studentId = 6; // ID of the student to delete

// Always use prepared statements for DELETE operations
$stmt = $conn->prepare("DELETE FROM students WHERE id = ?");
$stmt->bind_param("i", $studentId);

if ($stmt->execute()) {
    if ($stmt->affected_rows > 0) {
        echo "Student deleted successfully.";
    } else {
        echo "No student found with that ID.";
    }
} else {
    echo "Delete failed: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

Soft Delete (Best Practice)

In production applications, records are rarely physically deleted. Instead, use a "soft delete" by setting a flag:

<?php
require_once 'config/database.php';
$conn = getDbConnection();

$studentId = 6;

// Soft delete: mark as inactive instead of deleting
$stmt = $conn->prepare("UPDATE students SET is_active = 0 WHERE id = ?");
$stmt->bind_param("i", $studentId);

if ($stmt->execute() && $stmt->affected_rows > 0) {
    echo "Student deactivated successfully.";
} else {
    echo "Student not found or already deactivated.";
}

$stmt->close();
$conn->close();
?>

Then modify all SELECT queries to filter:

SELECT * FROM students WHERE is_active = 1;

---

6. Prepared Statements & Parameterized Queries

Why Prepared Statements Are Non-Negotiable

Prepared statements are the single most important security measure for database-driven applications. They completely prevent SQL injection attacks by separating SQL logic from user-supplied data.

The SQL Injection Problem

Consider this dangerously vulnerable code:

<?php
// ⚠️ NEVER DO THIS — VULNERABLE TO SQL INJECTION
$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $conn->query($sql);
?>

If a malicious user submits the username as admin' --, the query becomes:

SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'

The -- is a SQL comment that disables the password check, giving the attacker access to any account.

Even worse, submitting '; DROP TABLE users; -- could destroy your entire table:

SELECT * FROM users WHERE username = ''; DROP TABLE users; --' AND password = ''

The Prepared Statement Solution

<?php
// ✅ SAFE — Uses prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$result = $stmt->get_result();
?>

With prepared statements, the database engine treats the ? placeholders as data positions, not SQL code. No matter what the user submits, it will always be treated as a literal string value, never as executable SQL.

MySQLi Prepared Statement Lifecycle

<?php
// Step 1: PREPARE — Parse the SQL template
$stmt = $conn->prepare("INSERT INTO students (first_name, last_name, email) VALUES (?, ?, ?)");

// Step 2: BIND — Associate PHP variables with the placeholders
// Type string: s = string, i = integer, d = double, b = blob
$stmt->bind_param("sss", $firstName, $lastName, $email);

// Step 3: SET — Assign values to the bound variables
$firstName = "Hannah";
$lastName = "Kim";
$email = "[email protected]";

// Step 4: EXECUTE — Run the query with the bound values
$stmt->execute();

// You can reuse the prepared statement with new values:
$firstName = "Ian";
$lastName = "Wright";
$email = "[email protected]";
$stmt->execute(); // Executes again with the new values

// Step 5: CLOSE — Free the prepared statement resources
$stmt->close();
?>

Bind Parameter Types Reference

Type Code	PHP Type	MySQL Type	Example
s	string	VARCHAR, TEXT, CHAR, DATE, DATETIME	"hello", "2024-01-15"
i	integer	INT, TINYINT, SMALLINT, BIGINT	42, -7
d	double	DECIMAL, FLOAT, DOUBLE	3.14, 99.99
b	blob	BLOB, LONGBLOB	Binary data (file contents)

PDO Prepared Statement with Named Parameters

<?php
$pdo = getPdoConnection();

// Using named placeholders (more readable)
$sql = "SELECT * FROM students
        WHERE grade_level = :grade
        AND gpa >= :min_gpa
        AND is_active = :active
        ORDER BY gpa DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':grade'   => 11,
    ':min_gpa' => 3.5,
    ':active'  => 1,
]);

$results = $stmt->fetchAll();

foreach ($results as $row) {
    echo htmlspecialchars($row['first_name'] . ": GPA " . $row['gpa']) . "<br>";
}
?>

PDO with Explicit Type Binding

<?php
$pdo = getPdoConnection();

$stmt = $pdo->prepare("SELECT * FROM students WHERE id = :id AND is_active = :active");

// Explicit type binding (more control than passing an array)
$stmt->bindValue(':id', 3, PDO::PARAM_INT);
$stmt->bindValue(':active', true, PDO::PARAM_BOOL);
$stmt->execute();

$student = $stmt->fetch();
?>

Handling Dynamic WHERE Clauses Safely

A common challenge is building queries where the WHERE conditions depend on user input (e.g., a search form with optional filters):

<?php
require_once 'config/database.php';
$conn = getDbConnection();

// Simulated filter values from a form
$filterGrade = isset($_GET['grade']) ? (int)$_GET['grade'] : null;
$filterMinGpa = isset($_GET['min_gpa']) ? (float)$_GET['min_gpa'] : null;
$filterName = isset($_GET['name']) ? trim($_GET['name']) : null;

// Build query dynamically
$sql = "SELECT * FROM students WHERE is_active = 1";
$types = "";
$params = [];

if ($filterGrade !== null) {
    $sql .= " AND grade_level = ?";
    $types .= "i";
    $params[] = $filterGrade;
}

if ($filterMinGpa !== null) {
    $sql .= " AND gpa >= ?";
    $types .= "d";
    $params[] = $filterMinGpa;
}

if ($filterName !== null && $filterName !== '') {
    $sql .= " AND (first_name LIKE ? OR last_name LIKE ?)";
    $types .= "ss";
    $searchTerm = "%" . $filterName . "%";
    $params[] = $searchTerm;
    $params[] = $searchTerm;
}

$sql .= " ORDER BY last_name ASC";

$stmt = $conn->prepare($sql);

// Dynamically bind parameters (only if there are any)
if (!empty($params)) {
    $stmt->bind_param($types, ...$params); // Spread operator for variable-length args
}

$stmt->execute();
$result = $stmt->get_result();

echo "<p>Found {$result->num_rows} student(s):</p>";
while ($row = $result->fetch_assoc()) {
    echo htmlspecialchars($row['first_name'] . " " . $row['last_name'] . " — GPA: " . $row['gpa']) . "<br>";
}

$stmt->close();
$conn->close();
?>

---

7. Database Design & Schema Management

Data Types Quick Reference

Numeric Types

Type	Storage	Range (Signed)	Use Case
TINYINT	1 byte	-128 to 127	Boolean flags, small counters
SMALLINT	2 bytes	-32,768 to 32,767	Zip codes, small IDs
INT	4 bytes	-2.1B to 2.1B	Primary keys, counts
BIGINT	8 bytes	±9.2 quintillion	Very large IDs (Twitter, etc.)
DECIMAL(p,s)	Variable	Exact precision	Currency, GPA, prices
FLOAT	4 bytes	~7 decimal digits	Scientific measurements
DOUBLE	8 bytes	~15 decimal digits	Higher precision decimals

Rule of Thumb: Use DECIMAL for money and grades (exact precision required). Use FLOAT/DOUBLE only when approximate values are acceptable.

String Types

Type	Max Length	Use Case
CHAR(n)	255 chars (fixed)	State codes, zip codes, country codes
VARCHAR(n)	65,535 chars (variable)	Names, emails, URLs
TEXT	65,535 chars	Descriptions, comments
MEDIUMTEXT	16 MB	Blog posts, articles
LONGTEXT	4 GB	Full documents (rarely needed)
ENUM('a','b','c')	One of listed values	Status fields, fixed categories

Date/Time Types

Type	Format	Example	Use Case
DATE	YYYY-MM-DD	2025-03-15	Birthdays, enrollment dates
TIME	HH:MM:SS	14:30:00	Event times
DATETIME	YYYY-MM-DD HH:MM:SS	2025-03-15 14:30:00	Scheduled events
TIMESTAMP	YYYY-MM-DD HH:MM:SS	2025-03-15 14:30:00	Auto-tracking created_at/updated_at

DATETIME vs. TIMESTAMP: TIMESTAMP converts to UTC for storage and back to the server's timezone on retrieval, and supports DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. DATETIME stores the literal value without timezone conversion.

Indexes

Indexes dramatically speed up SELECT queries on large tables, but they add overhead to INSERT/UPDATE/DELETE operations (the index must be maintained).

-- Single column index
CREATE INDEX idx_students_email ON students(email);

-- Composite index (order matters — leftmost column is searched first)
CREATE INDEX idx_students_grade_gpa ON students(grade_level, gpa);

-- Unique index (also enforces data integrity)
CREATE UNIQUE INDEX idx_students_email_unique ON students(email);

-- Full-text index (for MATCH ... AGAINST searches)
ALTER TABLE courses ADD FULLTEXT INDEX ft_course_desc (course_name, description);

-- View existing indexes on a table
SHOW INDEX FROM students;

When to add an index:

• Columns frequently used in WHERE clauses

• Columns used in JOIN conditions

• Columns used in ORDER BY

• Columns used in GROUP BY

When NOT to index:

• Small tables (under a few hundred rows)

• Columns with very low cardinality (e.g., a boolean is_active column with only 2 values)

• Tables with heavy INSERT/UPDATE/DELETE and rare SELECT

Foreign Keys & Referential Integrity

Foreign keys enforce relationships between tables, preventing orphaned records:

-- Creating a table with foreign key constraints
CREATE TABLE enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    grade VARCHAR(2) DEFAULT NULL,
    
    -- Composite unique constraint: a student can't enroll in the same course twice
    UNIQUE KEY unique_enrollment (student_id, course_id),
    
    -- Foreign keys with cascading rules
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (student_id)
        REFERENCES students(id)
        ON DELETE CASCADE      -- If student is deleted, remove their enrollments
        ON UPDATE CASCADE,     -- If student's ID changes, update here too
    
    CONSTRAINT fk_enrollment_course
        FOREIGN KEY (course_id)
        REFERENCES courses(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

ON DELETE / ON UPDATE Options:

Option	Behavior
CASCADE	Delete/update child rows automatically
SET NULL	Set the foreign key column to NULL
SET DEFAULT	Set to the column's default value
RESTRICT	Prevent the parent row from being deleted/updated (default)
NO ACTION	Same as RESTRICT in MySQL

Altering Tables

-- Add a column
ALTER TABLE students ADD COLUMN phone VARCHAR(15) AFTER email;

-- Modify a column's type or constraints
ALTER TABLE students MODIFY COLUMN phone VARCHAR(20);

-- Rename a column
ALTER TABLE students RENAME COLUMN phone TO phone_number;

-- Drop a column
ALTER TABLE students DROP COLUMN phone_number;

-- Add a foreign key to an existing table
ALTER TABLE enrollments
    ADD CONSTRAINT fk_enrollment_student
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE;

-- Drop a foreign key
ALTER TABLE enrollments DROP FOREIGN KEY fk_enrollment_student;

---

8. Working with Joins & Complex Queries in PHP

Understanding JOINs Visually

Students Table                Courses Table
+----+-----------+           +----+--------+-----------+
| id | first_name|           | id | code   | name      |
+----+-----------+           +----+--------+-----------+
| 1  | Alice     |           | 1  | CS101  | Intro CS  |
| 2  | Bob       |           | 2  | CS201  | Data Str  |
| 3  | Carol     |           | 3  | CS301  | Web Dev   |
+----+-----------+           +----+--------+-----------+
         \                         /
          \   Enrollments Table   /
           \ +----+-----+-----+ /
            \| id | s_id| c_id|/
             +----+-----+-----+
             | 1  |  1  |  1  |  Alice → Intro CS
             | 2  |  1  |  3  |  Alice → Web Dev
             | 3  |  2  |  1  |  Bob → Intro CS
             | 4  |  3  |  2  |  Carol → Data Str
             +----+-----+-----+

INNER JOIN — Only Matching Rows

Returns only students who are enrolled in at least one course:

<?php
require_once 'config/database.php';
$conn = getDbConnection();

$sql = "SELECT 
            s.first_name,
            s.last_name,
            c.course_code,
            c.course_name,
            e.grade,
            e.enrollment_date
        FROM enrollments e
        INNER JOIN students s ON e.student_id = s.id
        INNER JOIN courses c ON e.course_id = c.id
        WHERE s.is_active = 1
        ORDER BY s.last_name, c.course_code";

$result = $conn->query($sql);

echo "<table border='1' cellpadding='8'>";
echo "<tr><th>Student</th><th>Course Code</th><th>Course Name</th><th>Grade</th><th>Enrolled</th></tr>";

while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . htmlspecialchars($row['first_name'] . " " . $row['last_name']) . "</td>";
    echo "<td>" . htmlspecialchars($row['course_code']) . "</td>";
    echo "<td>" . htmlspecialchars($row['course_name']) . "</td>";
    echo "<td>" . htmlspecialchars($row['grade'] ?? 'In Progress') . "</td>";
    echo "<td>" . htmlspecialchars($row['enrollment_date']) . "</td>";
    echo "</tr>";
}

echo "</table>";
$conn->close();
?>

Null Coalescing Operator ??: The expression $row['grade'] ?? 'In Progress' returns 'In Progress' if $row['grade'] is NULL. This is a clean PHP 7+ alternative to isset() checks.

LEFT JOIN — All Left Table Rows, Even Without Matches

Returns ALL students, including those not enrolled in any courses:

<?php
$sql = "SELECT
            s.id,
            s.first_name,
            s.last_name,
            COUNT(e.id) AS course_count,
            GROUP_CONCAT(c.course_code ORDER BY c.course_code SEPARATOR ', ') AS courses
        FROM students s
        LEFT JOIN enrollments e ON s.id = e.student_id
        LEFT JOIN courses c ON e.course_id = c.id
        WHERE s.is_active = 1
        GROUP BY s.id, s.first_name, s.last_name
        ORDER BY s.last_name";

$result = $conn->query($sql);

while ($row = $result->fetch_assoc()) {
    $name = htmlspecialchars($row['first_name'] . " " . $row['last_name']);
    $count = $row['course_count'];
    $courses = $count > 0 ? htmlspecialchars($row['courses']) : 'Not enrolled in any courses';
    echo "$name — $count course(s): $courses<br>";
}
?>

GROUP_CONCAT() is a powerful MySQL aggregate function that concatenates values from multiple rows into a single string. In this example, it produces output like "CS101, CS301" for a student enrolled in two courses.

Aggregate Queries

<?php
// Average GPA by grade level
$sql = "SELECT
            grade_level,
            COUNT(*) AS student_count,
            ROUND(AVG(gpa), 2) AS avg_gpa,
            ROUND(MIN(gpa), 2) AS min_gpa,
            ROUND(MAX(gpa), 2) AS max_gpa
        FROM students
        WHERE is_active = 1
        GROUP BY grade_level
        HAVING COUNT(*) >= 1
        ORDER BY grade_level";

$result = $conn->query($sql);

echo "<table border='1' cellpadding='8'>";
echo "<tr><th>Grade</th><th>Students</th><th>Avg GPA</th><th>Min GPA</th><th>Max GPA</th></tr>";

while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . htmlspecialchars($row['grade_level']) . "</td>";
    echo "<td>" . htmlspecialchars($row['student_count']) . "</td>";
    echo "<td>" . htmlspecialchars($row['avg_gpa']) . "</td>";
    echo "<td>" . htmlspecialchars($row['min_gpa']) . "</td>";
    echo "<td>" . htmlspecialchars($row['max_gpa']) . "</td>";
    echo "</tr>";
}

echo "</table>";
?>

Subqueries

<?php
// Find students with above-average GPA
$sql = "SELECT first_name, last_name, gpa
        FROM students
        WHERE gpa > (SELECT AVG(gpa) FROM students WHERE is_active = 1)
        AND is_active = 1
        ORDER BY gpa DESC";

$result = $conn->query($sql);

echo "<h3>Students with Above-Average GPA</h3>";
while ($row = $result->fetch_assoc()) {
    echo htmlspecialchars($row['first_name'] . " " . $row['last_name'] . ": " . $row['gpa']) . "<br>";
}
?>

<?php
// Find courses with no enrollments
$sql = "SELECT course_code, course_name
        FROM courses
        WHERE id NOT IN (SELECT DISTINCT course_id FROM enrollments)";

$result = $conn->query($sql);

echo "<h3>Courses with No Students</h3>";
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo htmlspecialchars($row['course_code'] . " — " . $row['course_name']) . "<br>";
    }
} else {
    echo "All courses have enrollments.";
}
?>

---

9. Transactions & Data Integrity

What Are Transactions?

A transaction groups multiple SQL operations into a single atomic unit. Either ALL operations succeed (COMMIT), or ALL are rolled back (ROLLBACK). This ensures data consistency even if an error occurs mid-operation.

ACID Properties

Property	Meaning
Atomicity	All operations in the transaction succeed or none do
Consistency	The database moves from one valid state to another
Isolation	Concurrent transactions don't interfere with each other
Durability	Once committed, changes survive system crashes

Transaction Example: Enrolling a Student

Enrolling a student requires multiple operations: checking prerequisites, checking seat availability, inserting the enrollment record, and updating the enrollment count. If any step fails, we need to undo everything.

<?php
require_once 'config/database.php';
$conn = getDbConnection();

$studentId = 4;
$courseId = 2;

// Begin transaction
$conn->begin_transaction();

try {
    // Step 1: Check if the student exists and is active
    $stmt = $conn->prepare("SELECT id, first_name, last_name FROM students WHERE id = ? AND is_active = 1");
    $stmt->bind_param("i", $studentId);
    $stmt->execute();
    $student = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    if (!$student) {
        throw new Exception("Student not found or inactive.");
    }
    
    // Step 2: Check if the course exists and has available seats
    $stmt = $conn->prepare(
        "SELECT c.id, c.course_name, c.max_enrollment,
                COUNT(e.id) AS current_enrollment
         FROM courses c
         LEFT JOIN enrollments e ON c.id = e.course_id
         WHERE c.id = ?
         GROUP BY c.id"
    );
    $stmt->bind_param("i", $courseId);
    $stmt->execute();
    $course = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    if (!$course) {
        throw new Exception("Course not found.");
    }
    
    if ($course['current_enrollment'] >= $course['max_enrollment']) {
        throw new Exception("Course '{$course['course_name']}' is full ({$course['max_enrollment']} seats).");
    }
    
    // Step 3: Check if the student is already enrolled
    $stmt = $conn->prepare(
        "SELECT id FROM enrollments WHERE student_id = ? AND course_id = ?"
    );
    $stmt->bind_param("ii", $studentId, $courseId);
    $stmt->execute();
    
    if ($stmt->get_result()->num_rows > 0) {
        $stmt->close();
        throw new Exception("Student is already enrolled in this course.");
    }
    $stmt->close();
    
    // Step 4: Insert the enrollment record
    $stmt = $conn->prepare(
        "INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES (?, ?, CURDATE())"
    );
    $stmt->bind_param("ii", $studentId, $courseId);
    $stmt->execute();
    $stmt->close();
    
    // All steps succeeded — commit the transaction
    $conn->commit();
    
    echo "Success! {$student['first_name']} {$student['last_name']} enrolled in {$course['course_name']}.";
    
} catch (Exception $e) {
    // Something went wrong — roll back ALL changes
    $conn->rollback();
    echo "Enrollment failed: " . htmlspecialchars($e->getMessage());
}

$conn->close();
?>

Transaction with PDO

<?php
require_once 'config/database.php';
$pdo = getPdoConnection();

try {
    $pdo->beginTransaction();
    
    // Operation 1: Deactivate a student
    $stmt = $pdo->prepare("UPDATE students SET is_active = 0 WHERE id = :id");
    $stmt->execute([':id' => 5]);
    
    // Operation 2: Remove their enrollments
    $stmt = $pdo->prepare("DELETE FROM enrollments WHERE student_id = :id");
    $stmt->execute([':id' => 5]);
    
    // All good — commit
    $pdo->commit();
    echo "Student withdrawn successfully.";
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Withdrawal failed: " . htmlspecialchars($e->getMessage());
}
?>

---

10. User Authentication with MySQL

Database Schema for Authentication

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,  -- bcrypt hashes are 60 chars, but allow 255
    role ENUM('student', 'teacher', 'admin') NOT NULL DEFAULT 'student',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    login_attempts INT NOT NULL DEFAULT 0,
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Registration: Hashing Passwords

Never store plain-text passwords. PHP's password_hash() function uses the bcrypt algorithm by default:

<?php
require_once 'config/database.php';

// Process registration form
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $conn = getDbConnection();
    
    // Sanitize and validate inputs
    $username = trim($_POST['username'] ?? '');
    $email = trim($_POST['email'] ?? '');
    $password = $_POST['password'] ?? '';
    $confirmPassword = $_POST['confirm_password'] ?? '';
    
    $errors = [];
    
    // Validate username
    if (strlen($username) < 3 || strlen($username) > 50) {
        $errors[] = "Username must be 3-50 characters.";
    }
    if (!preg_match('/^[a-zA-Z0-9_]+$/', $username)) {
        $errors[] = "Username can only contain letters, numbers, and underscores.";
    }
    
    // Validate email
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $errors[] = "Invalid email address.";
    }
    
    // Validate password
    if (strlen($password) < 8) {
        $errors[] = "Password must be at least 8 characters.";
    }
    if ($password !== $confirmPassword) {
        $errors[] = "Passwords do not match.";
    }
    
    // Check for existing user
    if (empty($errors)) {
        $stmt = $conn->prepare("SELECT id FROM users WHERE username = ? OR email = ?");
        $stmt->bind_param("ss", $username, $email);
        $stmt->execute();
        
        if ($stmt->get_result()->num_rows > 0) {
            $errors[] = "Username or email already exists.";
        }
        $stmt->close();
    }
    
    // Create the user if no errors
    if (empty($errors)) {
        // Hash the password (bcrypt with automatic salt)
        $passwordHash = password_hash($password, PASSWORD_DEFAULT);
        
        $stmt = $conn->prepare(
            "INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)"
        );
        $stmt->bind_param("sss", $username, $email, $passwordHash);
        
        if ($stmt->execute()) {
            echo "Registration successful! You can now log in.";
        } else {
            echo "Registration failed. Please try again.";
        }
        $stmt->close();
    } else {
        foreach ($errors as $error) {
            echo "<p style='color:red;'>" . htmlspecialchars($error) . "</p>";
        }
    }
    
    $conn->close();
}
?>

<!-- Registration Form -->
<form method="POST" action="">
    <div>
        <label for="username">Username:</label><br>
        <input type="text" name="username" id="username" required>
    </div>
    <div>
        <label for="email">Email:</label><br>
        <input type="email" name="email" id="email" required>
    </div>
    <div>
        <label for="password">Password:</label><br>
        <input type="password" name="password" id="password" required minlength="8">
    </div>
    <div>
        <label for="confirm_password">Confirm Password:</label><br>
        <input type="password" name="confirm_password" id="confirm_password" required>
    </div>
    <button type="submit">Register</button>
</form>

Login: Verifying Passwords

<?php
session_start();
require_once 'config/database.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $conn = getDbConnection();
    
    $username = trim($_POST['username'] ?? '');
    $password = $_POST['password'] ?? '';
    
    // Look up the user by username
    $stmt = $conn->prepare(
        "SELECT id, username, email, password_hash, role, is_active, login_attempts
         FROM users WHERE username = ?"
    );
    $stmt->bind_param("s", $username);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows === 1) {
        $user = $result->fetch_assoc();
        
        // Check if account is active
        if (!$user['is_active']) {
            echo "This account has been deactivated. Contact an administrator.";
        }
        // Check for too many login attempts (basic brute-force protection)
        elseif ($user['login_attempts'] >= 5) {
            echo "Account locked due to too many failed attempts. Contact an administrator.";
        }
        // Verify the password against the stored hash
        elseif (password_verify($password, $user['password_hash'])) {
            // Password is correct! Reset login attempts and update last login
            $updateStmt = $conn->prepare(
                "UPDATE users SET login_attempts = 0, last_login = NOW() WHERE id = ?"
            );
            $updateStmt->bind_param("i", $user['id']);
            $updateStmt->execute();
            $updateStmt->close();
            
            // Regenerate session ID to prevent session fixation attacks
            session_regenerate_id(true);
            
            // Store user info in the session
            $_SESSION['user_id'] = $user['id'];
            $_SESSION['username'] = $user['username'];
            $_SESSION['role'] = $user['role'];
            $_SESSION['login_time'] = time();
            
            // Check if password hash needs rehashing (PHP may upgrade algorithm)
            if (password_needs_rehash($user['password_hash'], PASSWORD_DEFAULT)) {
                $newHash = password_hash($password, PASSWORD_DEFAULT);
                $rehashStmt = $conn->prepare("UPDATE users SET password_hash = ? WHERE id = ?");
                $rehashStmt->bind_param("si", $newHash, $user['id']);
                $rehashStmt->execute();
                $rehashStmt->close();
            }
            
            header("Location: dashboard.php");
            exit;
        } else {
            // Wrong password — increment login attempts
            $updateStmt = $conn->prepare(
                "UPDATE users SET login_attempts = login_attempts + 1 WHERE id = ?"
            );
            $updateStmt->bind_param("i", $user['id']);
            $updateStmt->execute();
            $updateStmt->close();
            
            echo "Invalid username or password.";
        }
    } else {
        // User not found — use the same generic message (don't reveal user existence)
        echo "Invalid username or password.";
    }
    
    $stmt->close();
    $conn->close();
}
?>

<!-- Login Form -->
<form method="POST" action="">
    <div>
        <label for="username">Username:</label><br>
        <input type="text" name="username" id="username" required>
    </div>
    <div>
        <label for="password">Password:</label><br>
        <input type="password" name="password" id="password" required>
    </div>
    <button type="submit">Log In</button>
</form>

Session-Based Access Control

auth_check.php — Include this at the top of every protected page:

<?php
session_start();

/**
 * Check if the user is logged in
 */
function requireLogin(): void {
    if (!isset($_SESSION['user_id'])) {
        header("Location: login.php");
        exit;
    }
    
    // Optional: Session timeout after 30 minutes of inactivity
    $timeout = 30 * 60; // 30 minutes in seconds
    if (isset($_SESSION['login_time']) && (time() - $_SESSION['login_time'] > $timeout)) {
        session_unset();
        session_destroy();
        header("Location: login.php?timeout=1");
        exit;
    }
    
    // Refresh the session timer on activity
    $_SESSION['login_time'] = time();
}

/**
 * Check if the user has a specific role
 */
function requireRole(string $requiredRole): void {
    requireLogin();
    
    if ($_SESSION['role'] !== $requiredRole && $_SESSION['role'] !== 'admin') {
        http_response_code(403);
        die("Access denied. You do not have permission to view this page.");
    }
}

/**
 * Logout the user
 */
function logout(): void {
    session_unset();
    session_destroy();
    header("Location: login.php");
    exit;
}
?>

Usage on a protected page:

<?php
require_once 'auth_check.php';
requireLogin(); // Redirects to login if not authenticated

echo "Welcome, " . htmlspecialchars($_SESSION['username']);
echo "<br>Your role: " . htmlspecialchars($_SESSION['role']);
?>

Admin-only page:

<?php
require_once 'auth_check.php';
requireRole('admin'); // Only admins and above can access

echo "Admin Dashboard";
?>

---

11. Pagination, Search & Filtering

Basic Pagination

Pagination displays a subset of rows per page, preventing the browser from loading thousands of records at once.

<?php
require_once 'config/database.php';
$conn = getDbConnection();

// Configuration
$recordsPerPage = 10;

// Get the current page from the URL query string (default to 1)
$currentPage = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;

// Calculate the SQL OFFSET
$offset = ($currentPage - 1) * $recordsPerPage;

// Get total record count for pagination controls
$countResult = $conn->query("SELECT COUNT(*) AS total FROM students WHERE is_active = 1");
$totalRecords = $countResult->fetch_assoc()['total'];
$totalPages = ceil($totalRecords / $recordsPerPage);

// Fetch the current page of records
$stmt = $conn->prepare(
    "SELECT id, first_name, last_name, email, grade_level, gpa
     FROM students
     WHERE is_active = 1
     ORDER BY last_name ASC
     LIMIT ? OFFSET ?"
);
$stmt->bind_param("ii", $recordsPerPage, $offset);
$stmt->execute();
$result = $stmt->get_result();

// Display records
echo "<h2>Students (Page $currentPage of $totalPages)</h2>";
echo "<p>Showing records " . ($offset + 1) . "–" . min($offset + $recordsPerPage, $totalRecords) . " of $totalRecords</p>";

echo "<table border='1' cellpadding='8'>";
echo "<tr><th>Name</th><th>Email</th><th>Grade</th><th>GPA</th></tr>";

while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . htmlspecialchars($row['first_name'] . " " . $row['last_name']) . "</td>";
    echo "<td>" . htmlspecialchars($row['email']) . "</td>";
    echo "<td>" . htmlspecialchars($row['grade_level']) . "</td>";
    echo "<td>" . htmlspecialchars(number_format($row['gpa'], 2)) . "</td>";
    echo "</tr>";
}

echo "</table>";

// Pagination controls
echo "<div style='margin-top: 15px;'>";
if ($currentPage > 1) {
    echo "<a href='?page=" . ($currentPage - 1) . "'>&laquo; Previous</a> ";
}

for ($i = 1; $i <= $totalPages; $i++) {
    if ($i === $currentPage) {
        echo "<strong>[$i]</strong> ";
    } else {
        echo "<a href='?page=$i'>$i</a> ";
    }
}

if ($currentPage < $totalPages) {
    echo "<a href='?page=" . ($currentPage + 1) . "'>Next &raquo;</a>";
}
echo "</div>";

$stmt->close();
$conn->close();
?>

Search with Pagination

Combining a search feature with pagination requires passing the search term through pagination links:

<?php
require_once 'config/database.php';
$conn = getDbConnection();

$recordsPerPage = 10;
$currentPage = max(1, (int)($_GET['page'] ?? 1));
$searchTerm = trim($_GET['search'] ?? '');
$offset = ($currentPage - 1) * $recordsPerPage;

// Build query with optional search filter
$whereClause = "WHERE is_active = 1";
$types = "";
$params = [];

if ($searchTerm !== '') {
    $whereClause .= " AND (first_name LIKE ? OR last_name LIKE ? OR email LIKE ?)";
    $types = "sss";
    $searchWild = "%" . $searchTerm . "%";
    $params = [$searchWild, $searchWild, $searchWild];
}

// Count total matching records
$countSql = "SELECT COUNT(*) AS total FROM students $whereClause";
$countStmt = $conn->prepare($countSql);
if (!empty($params)) {
    $countStmt->bind_param($types, ...$params);
}
$countStmt->execute();
$totalRecords = $countStmt->get_result()->fetch_assoc()['total'];
$totalPages = max(1, ceil($totalRecords / $recordsPerPage));
$countStmt->close();

// Fetch the current page
$dataSql = "SELECT id, first_name, last_name, email, grade_level, gpa
            FROM students $whereClause
            ORDER BY last_name ASC LIMIT ? OFFSET ?";
$dataStmt = $conn->prepare($dataSql);

$dataTypes = $types . "ii";
$dataParams = array_merge($params, [$recordsPerPage, $offset]);
$dataStmt->bind_param($dataTypes, ...$dataParams);
$dataStmt->execute();
$result = $dataStmt->get_result();

// Build query string for pagination links (preserves search term)
$queryString = $searchTerm !== '' ? "&search=" . urlencode($searchTerm) : "";
?>

<!-- Search Form -->
<form method="GET" action="">
    <input type="text" name="search" placeholder="Search students..."
           value="<?= htmlspecialchars($searchTerm) ?>">
    <button type="submit">Search</button>
    <?php if ($searchTerm !== ''): ?>
        <a href="?">Clear</a>
    <?php endif; ?>
</form>

<p>Found <?= $totalRecords ?> result(s).</p>

<!-- Results Table -->
<table border="1" cellpadding="8">
    <tr><th>Name</th><th>Email</th><th>Grade</th><th>GPA</th></tr>
    <?php while ($row = $result->fetch_assoc()): ?>
        <tr>
            <td><?= htmlspecialchars($row['first_name'] . " " . $row['last_name']) ?></td>
            <td><?= htmlspecialchars($row['email']) ?></td>
            <td><?= htmlspecialchars($row['grade_level']) ?></td>
            <td><?= htmlspecialchars(number_format($row['gpa'], 2)) ?></td>
        </tr>
    <?php endwhile; ?>
</table>

<!-- Pagination Links -->
<div style="margin-top: 15px;">
    <?php if ($currentPage > 1): ?>
        <a href="?page=<?= $currentPage - 1 ?><?= $queryString ?>">&laquo; Previous</a>
    <?php endif; ?>
    
    <?php for ($i = 1; $i <= $totalPages; $i++): ?>
        <?php if ($i === $currentPage): ?>
            <strong>[<?= $i ?>]</strong>
        <?php else: ?>
            <a href="?page=<?= $i ?><?= $queryString ?>"><?= $i ?></a>
        <?php endif; ?>
    <?php endfor; ?>
    
    <?php if ($currentPage < $totalPages): ?>
        <a href="?page=<?= $currentPage + 1 ?><?= $queryString ?>">Next &raquo;</a>
    <?php endif; ?>
</div>

<?php
$dataStmt->close();
$conn->close();
?>

---

12. File Uploads & Binary Data Storage

Approach 1: Store File Path in Database (Recommended)

Store uploaded files on the filesystem and save only the file path in the database. This is more efficient and scalable than storing binary data directly.

-- Add a profile photo column
ALTER TABLE students ADD COLUMN profile_photo VARCHAR(255) DEFAULT NULL;

<?php
require_once 'config/database.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['photo'])) {
    $conn = getDbConnection();
    $studentId = (int)$_POST['student_id'];
    
    // Configuration
    $uploadDir = '/var/www/html/uploads/photos/';
    $maxFileSize = 2 * 1024 * 1024; // 2 MB
    $allowedTypes = ['image/jpeg', 'image/png', 'image/gif', 'image/webp'];
    $allowedExtensions = ['jpg', 'jpeg', 'png', 'gif', 'webp'];
    
    $file = $_FILES['photo'];
    $errors = [];
    
    // Validate upload errors
    if ($file['error'] !== UPLOAD_ERR_OK) {
        $errors[] = "Upload error code: " . $file['error'];
    }
    
    // Validate file size
    if ($file['size'] > $maxFileSize) {
        $errors[] = "File too large. Maximum size is 2 MB.";
    }
    
    // Validate MIME type (check actual file content, not just extension)
    $finfo = new finfo(FILEINFO_MIME_TYPE);
    $actualMimeType = $finfo->file($file['tmp_name']);
    if (!in_array($actualMimeType, $allowedTypes)) {
        $errors[] = "Invalid file type. Allowed: JPG, PNG, GIF, WEBP.";
    }
    
    // Validate extension
    $extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    if (!in_array($extension, $allowedExtensions)) {
        $errors[] = "Invalid file extension.";
    }
    
    if (empty($errors)) {
        // Generate a unique, unpredictable filename
        $newFilename = bin2hex(random_bytes(16)) . '.' . $extension;
        $destination = $uploadDir . $newFilename;
        
        // Create directory if it doesn't exist
        if (!is_dir($uploadDir)) {
            mkdir($uploadDir, 0755, true);
        }
        
        // Move the file from the temporary location
        if (move_uploaded_file($file['tmp_name'], $destination)) {
            // Save the relative path in the database
            $relativePath = 'uploads/photos/' . $newFilename;
            
            $stmt = $conn->prepare("UPDATE students SET profile_photo = ? WHERE id = ?");
            $stmt->bind_param("si", $relativePath, $studentId);
            $stmt->execute();
            $stmt->close();
            
            echo "Photo uploaded successfully!";
            echo "<br><img src='/$relativePath' alt='Profile Photo' style='max-width: 200px;'>";
        } else {
            echo "Failed to save the file.";
        }
    } else {
        foreach ($errors as $error) {
            echo "<p style='color:red;'>" . htmlspecialchars($error) . "</p>";
        }
    }
    
    $conn->close();
}
?>

<form method="POST" enctype="multipart/form-data">
    <input type="hidden" name="student_id" value="1">
    <label>Profile Photo: <input type="file" name="photo" accept="image/*"></label>
    <button type="submit">Upload</button>
</form>

Approach 2: Store Binary Data in Database (BLOB)

For small files or when database portability is important:

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size INT NOT NULL,
    file_data LONGBLOB NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

<?php
// Upload to database
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['document'])) {
    $conn = getDbConnection();
    $studentId = (int)$_POST['student_id'];
    $file = $_FILES['document'];
    
    $stmt = $conn->prepare(
        "INSERT INTO documents (student_id, filename, mime_type, file_size, file_data)
         VALUES (?, ?, ?, ?, ?)"
    );
    
    $filename = basename($file['name']);
    $mimeType = mime_content_type($file['tmp_name']);
    $fileSize = $file['size'];
    
    $stmt->bind_param("issib", $studentId, $filename, $mimeType, $fileSize, $null);
    
    // Send file data using send_long_data for large files
    $fp = fopen($file['tmp_name'], 'rb');
    while (!feof($fp)) {
        $stmt->send_long_data(4, fread($fp, 8192)); // Parameter index 4 = file_data
    }
    fclose($fp);
    
    $stmt->execute();
    echo "Document stored in database! ID: " . $stmt->insert_id;
    
    $stmt->close();
    $conn->close();
}

// Download from database
if (isset($_GET['download_id'])) {
    $conn = getDbConnection();
    $docId = (int)$_GET['download_id'];
    
    $stmt = $conn->prepare("SELECT filename, mime_type, file_data FROM documents WHERE id = ?");
    $stmt->bind_param("i", $docId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($row = $result->fetch_assoc()) {
        header("Content-Type: " . $row['mime_type']);
        header("Content-Disposition: attachment; filename=\"" . $row['filename'] . "\"");
        echo $row['file_data'];
        exit;
    }
    
    $stmt->close();
    $conn->close();
}
?>

---

13. Error Handling & Logging

PHP Error Handling Levels

Configure error reporting in php.ini or at runtime:

<?php
// Development (show all errors)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Production (hide errors from users, log them to a file)
ini_set('display_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', '/var/log/php/app_errors.log');
error_reporting(E_ALL);
?>

MySQLi Error Handling

<?php
// Enable exception-based error handling for MySQLi
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli("localhost", "webappuser", "password", "school_portal");
    $conn->set_charset("utf8mb4");
    
    // This will throw a mysqli_sql_exception if the table doesn't exist
    $result = $conn->query("SELECT * FROM nonexistent_table");
    
} catch (mysqli_sql_exception $e) {
    // Log the full technical error
    error_log("MySQL Error [{$e->getCode()}]: {$e->getMessage()} in {$e->getFile()}:{$e->getLine()}");
    
    // Show a user-friendly message
    http_response_code(500);
    echo "A database error occurred. Our team has been notified.";
}
?>

Custom Error Logger

<?php
/**
 * Custom database error logger
 * Writes structured error information to a log file
 */
function logDatabaseError(string $message, string $query = '', array $context = []): void {
    $logFile = '/var/log/php/database_errors.log';
    
    $logEntry = [
        'timestamp' => date('Y-m-d H:i:s'),
        'message'   => $message,
        'query'     => $query,
        'ip'        => $_SERVER['REMOTE_ADDR'] ?? 'CLI',
        'uri'       => $_SERVER['REQUEST_URI'] ?? 'N/A',
        'context'   => $context,
    ];
    
    $logLine = json_encode($logEntry, JSON_UNESCAPED_SLASHES) . PHP_EOL;
    file_put_contents($logFile, $logLine, FILE_APPEND | LOCK_EX);
}

// Usage example
try {
    $stmt = $conn->prepare("SELECT * FROM students WHERE id = ?");
    $stmt->bind_param("i", $studentId);
    $stmt->execute();
} catch (mysqli_sql_exception $e) {
    logDatabaseError(
        $e->getMessage(),
        "SELECT * FROM students WHERE id = ?",
        ['student_id' => $studentId, 'error_code' => $e->getCode()]
    );
    die("An error occurred. Reference: " . date('YmdHis'));
}
?>

Try-Catch Pattern for Database Operations

<?php
require_once 'config/database.php';
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

function addStudent(array $data): int|false {
    try {
        $conn = getDbConnection();
        
        $stmt = $conn->prepare(
            "INSERT INTO students (first_name, last_name, email, grade_level, gpa)
             VALUES (?, ?, ?, ?, ?)"
        );
        $stmt->bind_param(
            "sssid",
            $data['first_name'],
            $data['last_name'],
            $data['email'],
            $data['grade_level'],
            $data['gpa']
        );
        $stmt->execute();
        
        $newId = $stmt->insert_id;
        $stmt->close();
        $conn->close();
        
        return $newId;
        
    } catch (mysqli_sql_exception $e) {
        error_log("addStudent failed: " . $e->getMessage());
        
        // Handle specific error codes
        if ($e->getCode() === 1062) {
            // Duplicate entry (e.g., email already exists)
            throw new RuntimeException("A student with this email already exists.");
        }
        
        throw new RuntimeException("Failed to add student. Please try again.");
    }
}

// Usage
try {
    $newId = addStudent([
        'first_name'  => 'Test',
        'last_name'   => 'Student',
        'email'       => '[email protected]',
        'grade_level' => 10,
        'gpa'         => 3.50,
    ]);
    echo "Student created with ID: $newId";
} catch (RuntimeException $e) {
    echo "Error: " . htmlspecialchars($e->getMessage());
}
?>

---

14. Security Best Practices

The Security Checklist

Every database-driven PHP application should implement the following protections:

1. SQL Injection Prevention

Always use prepared statements with parameterized queries. No exceptions.

// ❌ NEVER: String concatenation/interpolation
$sql = "SELECT * FROM users WHERE id = $id";
$sql = "SELECT * FROM users WHERE id = " . $_GET['id'];
$sql = "SELECT * FROM users WHERE name = '{$_POST['name']}'";

// ✅ ALWAYS: Prepared statements
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);

2. Cross-Site Scripting (XSS) Prevention

Always escape output with htmlspecialchars():

// ❌ NEVER: Raw database output in HTML
echo $row['username'];
echo "<input value='" . $row['email'] . "'>";

// ✅ ALWAYS: Escaped output
echo htmlspecialchars($row['username'], ENT_QUOTES, 'UTF-8');
echo "<input value='" . htmlspecialchars($row['email'], ENT_QUOTES, 'UTF-8') . "'>";

3. Cross-Site Request Forgery (CSRF) Protection

Add CSRF tokens to all forms that modify data:

<?php
session_start();

// Generate a CSRF token
function generateCsrfToken(): string {
    if (empty($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    }
    return $_SESSION['csrf_token'];
}

// Validate a CSRF token
function validateCsrfToken(string $token): bool {
    return isset($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token);
}
?>

<!-- In your form -->
<form method="POST" action="delete_student.php">
    <input type="hidden" name="csrf_token" value="<?= generateCsrfToken() ?>">
    <input type="hidden" name="student_id" value="5">
    <button type="submit">Delete Student</button>
</form>

<?php
// In delete_student.php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (!validateCsrfToken($_POST['csrf_token'] ?? '')) {
        die("Invalid request. CSRF token validation failed.");
    }
    // Process the deletion...
}
?>

4. Password Security

// ❌ NEVER
$hash = md5($password);              // Broken, rainbow table vulnerable
$hash = sha1($password);             // Broken, too fast
$hash = sha256($password . $salt);   // Manual salting is error-prone

// ✅ ALWAYS
$hash = password_hash($password, PASSWORD_DEFAULT); // bcrypt, auto-salted
$isValid = password_verify($password, $hash);       // Timing-safe comparison

5. Database Connection Security

// Store credentials outside the web root
// /etc/myapp/db_config.php (NOT in /var/www/html/)

// Use environment variables in production
$dbHost = getenv('DB_HOST') ?: 'localhost';
$dbUser = getenv('DB_USER') ?: 'default_user';
$dbPass = getenv('DB_PASS') ?: 'default_pass';
$dbName = getenv('DB_NAME') ?: 'default_db';

6. Principle of Least Privilege

-- Create a read-only user for public-facing pages
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'ReadOnlyP@ss';
GRANT SELECT ON school_portal.* TO 'readonly_user'@'localhost';

-- Create a read-write user for authenticated operations
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppUserP@ss';
GRANT SELECT, INSERT, UPDATE, DELETE ON school_portal.* TO 'app_user'@'localhost';

-- Only the admin user gets schema modification privileges
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'AdminP@ss';
GRANT ALL PRIVILEGES ON school_portal.* TO 'admin_user'@'localhost';

7. Input Validation & Sanitization

<?php
// Type casting
$id = (int)$_GET['id'];          // Forces integer (non-numeric becomes 0)
$price = (float)$_POST['price']; // Forces float

// Filter functions
$email = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
$url = filter_input(INPUT_POST, 'website', FILTER_VALIDATE_URL);
$age = filter_input(INPUT_POST, 'age', FILTER_VALIDATE_INT, [
    'options' => ['min_range' => 1, 'max_range' => 120]
]);

// String sanitization
$name = trim($_POST['name']);                    // Remove whitespace
$name = strip_tags($name);                       // Remove HTML/PHP tags
$name = htmlspecialchars($name, ENT_QUOTES, 'UTF-8'); // Escape special chars

// Whitelist validation (only allow expected values)
$validSortColumns = ['name', 'email', 'gpa', 'grade_level'];
$sortBy = in_array($_GET['sort'] ?? '', $validSortColumns) ? $_GET['sort'] : 'name';

$validDirections = ['ASC', 'DESC'];
$sortDir = in_array(strtoupper($_GET['dir'] ?? ''), $validDirections) ? strtoupper($_GET['dir']) : 'ASC';

// Safe to use in query since it's whitelist-validated (not user input)
$sql = "SELECT * FROM students ORDER BY $sortBy $sortDir";
?>

---

15. Performance Optimization

Query Optimization

Use EXPLAIN to Analyze Queries

EXPLAIN SELECT s.first_name, s.last_name, c.course_name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id
WHERE s.grade_level = 11;

Key columns in EXPLAIN output:

Column	Good Values	Warning Signs
type	const, eq_ref, ref	ALL (full table scan)
key	An index name	NULL (no index used)
rows	Low number	Very high number
Extra	Using index	Using filesort, Using temporary

Index Optimization

-- Add indexes for frequently queried columns
CREATE INDEX idx_students_grade ON students(grade_level);
CREATE INDEX idx_students_active ON students(is_active);

-- Composite index for common combined queries
CREATE INDEX idx_students_grade_active_gpa ON students(grade_level, is_active, gpa);

-- The composite index above covers these queries efficiently:
-- WHERE grade_level = 11
-- WHERE grade_level = 11 AND is_active = 1
-- WHERE grade_level = 11 AND is_active = 1 AND gpa >= 3.5
-- But NOT: WHERE gpa >= 3.5 (leftmost column not used)

PHP Optimization

Connection Pooling with Persistent Connections

<?php
// Persistent connection reuses existing connections instead of creating new ones
$conn = new mysqli('p:localhost', 'webappuser', 'password', 'school_portal');
// The 'p:' prefix enables persistent connections
?>

Fetch Only What You Need

// ❌ Wasteful: fetching all columns when you only need two
$result = $conn->query("SELECT * FROM students");

// ✅ Efficient: specify only needed columns
$result = $conn->query("SELECT first_name, last_name FROM students");

Use Buffered vs. Unbuffered Queries

<?php
// Default (buffered): All results loaded into PHP memory at once
// Good for small result sets that need num_rows or seek
$result = $conn->query("SELECT * FROM students");
echo $result->num_rows; // Available immediately

// Unbuffered: Results streamed row-by-row from MySQL
// Good for very large result sets (saves PHP memory)
$conn->real_query("SELECT * FROM huge_table");
$result = $conn->use_result();

while ($row = $result->fetch_assoc()) {
    // Process one row at a time
    // num_rows is NOT available with unbuffered queries
}

$result->free();
?>

Batch Inserts

<?php
// ❌ Slow: Individual INSERT statements (1000 round trips to MySQL)
foreach ($students as $s) {
    $stmt->bind_param("sss", $s['first'], $s['last'], $s['email']);
    $stmt->execute();
}

// ✅ Fast: Single multi-value INSERT (1 round trip)
$values = [];
$types = "";
$params = [];

foreach ($students as $s) {
    $values[] = "(?, ?, ?)";
    $types .= "sss";
    $params[] = $s['first'];
    $params[] = $s['last'];
    $params[] = $s['email'];
}

$sql = "INSERT INTO students (first_name, last_name, email) VALUES " . implode(', ', $values);
$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
?>

Caching Strategies

<?php
/**
 * Simple file-based cache for expensive database queries
 */
function getCachedQuery(mysqli $conn, string $cacheKey, string $sql, int $ttl = 300): array {
    $cacheDir = '/tmp/app_cache/';
    $cacheFile = $cacheDir . md5($cacheKey) . '.json';
    
    // Check if cache exists and is still valid
    if (file_exists($cacheFile) && (time() - filemtime($cacheFile)) < $ttl) {
        return json_decode(file_get_contents($cacheFile), true);
    }
    
    // Cache miss — execute query
    $result = $conn->query($sql);
    $data = $result->fetch_all(MYSQLI_ASSOC);
    
    // Store in cache
    if (!is_dir($cacheDir)) {
        mkdir($cacheDir, 0755, true);
    }
    file_put_contents($cacheFile, json_encode($data), LOCK_EX);
    
    return $data;
}

// Usage: Cache the course list for 5 minutes (300 seconds)
$courses = getCachedQuery($conn, 'all_courses', "SELECT * FROM courses ORDER BY course_name", 300);
?>

---

16. Building a Complete CRUD Application

Project Structure

/var/www/html/student_portal/
├── config/
│   └── database.php          # Database connection configuration
├── includes/
│   ├── auth_check.php         # Authentication middleware
│   ├── header.php             # HTML header template
│   ├── footer.php             # HTML footer template
│   └── functions.php          # Helper functions
├── uploads/
│   └── photos/                # Student profile photos
├── index.php                  # Student listing with pagination
├── student_add.php            # Add new student form + processing
├── student_edit.php           # Edit student form + processing
├── student_delete.php         # Delete student handler
├── student_view.php           # Single student detail view
├── login.php                  # Login page
├── logout.php                 # Logout handler
└── register.php               # Registration page

includes/functions.php

<?php
/**
 * Helper Functions
 */

/**
 * Redirect to a URL and stop execution
 */
function redirect(string $url): void {
    header("Location: $url");
    exit;
}

/**
 * Escape output for HTML display
 */
function e(string $value): string {
    return htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
}

/**
 * Set a flash message (displayed once on next page load)
 */
function setFlashMessage(string $type, string $message): void {
    $_SESSION['flash'] = ['type' => $type, 'message' => $message];
}

/**
 * Get and clear the flash message
 */
function getFlashMessage(): ?array {
    $flash = $_SESSION['flash'] ?? null;
    unset($_SESSION['flash']);
    return $flash;
}

/**
 * Display the flash message as HTML
 */
function displayFlashMessage(): void {
    $flash = getFlashMessage();
    if ($flash) {
        $color = $flash['type'] === 'success' ? 'green' : 'red';
        echo "<div style='padding: 10px; margin: 10px 0; background-color: {$color}; color: white; border-radius: 5px;'>";
        echo e($flash['message']);
        echo "</div>";
    }
}
?>

includes/header.php

<?php
session_start();
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/functions.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?= e($pageTitle ?? 'Student Portal') ?></title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 960px; margin: 0 auto; padding: 20px; }
        nav { background: #333; padding: 10px 20px; margin-bottom: 20px; border-radius: 5px; }
        nav a { color: white; text-decoration: none; margin-right: 15px; }
        nav a:hover { text-decoration: underline; }
        table { width: 100%; border-collapse: collapse; margin: 15px 0; }
        th, td { padding: 10px; border: 1px solid #ddd; text-align: left; }
        th { background: #f5f5f5; }
        tr:hover { background: #f9f9f9; }
        .btn { display: inline-block; padding: 6px 12px; border-radius: 4px; text-decoration: none; color: white; }
        .btn-primary { background: #007bff; }
        .btn-danger { background: #dc3545; }
        .btn-success { background: #28a745; }
        input, select, textarea { padding: 8px; margin: 5px 0; border: 1px solid #ccc; border-radius: 4px; }
        label { display: block; margin-top: 10px; font-weight: bold; }
    </style>
</head>
<body>
    <nav>
        <a href="index.php">Students</a>
        <a href="student_add.php">Add Student</a>
        <?php if (isset($_SESSION['user_id'])): ?>
            <a href="logout.php" style="float:right;">Logout (<?= e($_SESSION['username']) ?>)</a>
        <?php else: ?>
            <a href="login.php" style="float:right;">Login</a>
        <?php endif; ?>
    </nav>
    
    <?php displayFlashMessage(); ?>

includes/footer.php

    <hr>
    <footer style="text-align: center; color: #888; padding: 10px;">
        <p>Student Portal &copy; <?= date('Y') ?></p>
    </footer>
</body>
</html>

index.php — Student Listing

<?php
$pageTitle = "Student Roster";
require_once 'includes/header.php';

$conn = getDbConnection();

// Pagination config
$perPage = 10;
$page = max(1, (int)($_GET['page'] ?? 1));
$offset = ($page - 1) * $perPage;
$search = trim($_GET['search'] ?? '');

// Build query
$where = "WHERE is_active = 1";
$types = "";
$params = [];

if ($search !== '') {
    $where .= " AND (first_name LIKE ? OR last_name LIKE ? OR email LIKE ?)";
    $types = "sss";
    $wild = "%$search%";
    $params = [$wild, $wild, $wild];
}

// Count
$countStmt = $conn->prepare("SELECT COUNT(*) AS total FROM students $where");
if (!empty($params)) $countStmt->bind_param($types, ...$params);
$countStmt->execute();
$total = $countStmt->get_result()->fetch_assoc()['total'];
$totalPages = max(1, ceil($total / $perPage));
$countStmt->close();

// Fetch
$dataStmt = $conn->prepare(
    "SELECT id, first_name, last_name, email, grade_level, gpa
     FROM students $where ORDER BY last_name LIMIT ? OFFSET ?"
);
$allTypes = $types . "ii";
$allParams = array_merge($params, [$perPage, $offset]);
$dataStmt->bind_param($allTypes, ...$allParams);
$dataStmt->execute();
$result = $dataStmt->get_result();
$qs = $search !== '' ? "&search=" . urlencode($search) : "";
?>

<h1>Student Roster</h1>

<form method="GET" style="margin-bottom: 15px;">
    <input type="text" name="search" placeholder="Search by name or email..."
           value="<?= e($search) ?>" style="width: 300px;">
    <button type="submit" class="btn btn-primary">Search</button>
    <?php if ($search): ?><a href="index.php">Clear</a><?php endif; ?>
</form>

<p>Showing <?= $total ?> student(s) — Page <?= $page ?> of <?= $totalPages ?></p>

<table>
    <tr>
        <th>ID</th><th>Name</th><th>Email</th>
        <th>Grade</th><th>GPA</th><th>Actions</th>
    </tr>
    <?php while ($row = $result->fetch_assoc()): ?>
    <tr>
        <td><?= e($row['id']) ?></td>
        <td><?= e($row['first_name'] . " " . $row['last_name']) ?></td>
        <td><?= e($row['email']) ?></td>
        <td><?= e($row['grade_level']) ?></td>
        <td><?= e(number_format($row['gpa'], 2)) ?></td>
        <td>
            <a href="student_view.php?id=<?= $row['id'] ?>" class="btn btn-primary">View</a>
            <a href="student_edit.php?id=<?= $row['id'] ?>" class="btn btn-success">Edit</a>
            <a href="student_delete.php?id=<?= $row['id'] ?>"
               class="btn btn-danger"
               onclick="return confirm('Are you sure?')">Delete</a>
        </td>
    </tr>
    <?php endwhile; ?>
</table>

<!-- Pagination -->
<div>
    <?php if ($page > 1): ?>
        <a href="?page=<?= $page - 1 ?><?= $qs ?>">&laquo; Prev</a>
    <?php endif; ?>
    
    <?php for ($i = 1; $i <= $totalPages; $i++): ?>
        <?= $i === $page ? "<strong>[$i]</strong>" : "<a href='?page=$i$qs'>$i</a>" ?>
    <?php endfor; ?>
    
    <?php if ($page < $totalPages): ?>
        <a href="?page=<?= $page + 1 ?><?= $qs ?>">Next &raquo;</a>
    <?php endif; ?>
</div>

<?php
$dataStmt->close();
$conn->close();
require_once 'includes/footer.php';
?>

student_add.php — Add Student

<?php
$pageTitle = "Add Student";
require_once 'includes/header.php';

$errors = [];
$formData = ['first_name' => '', 'last_name' => '', 'email' => '', 'grade_level' => '', 'gpa' => ''];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $conn = getDbConnection();
    
    $formData = [
        'first_name'  => trim($_POST['first_name'] ?? ''),
        'last_name'   => trim($_POST['last_name'] ?? ''),
        'email'       => trim($_POST['email'] ?? ''),
        'grade_level' => (int)($_POST['grade_level'] ?? 0),
        'gpa'         => (float)($_POST['gpa'] ?? 0),
    ];
    
    // Validation
    if (strlen($formData['first_name']) < 1) $errors[] = "First name is required.";
    if (strlen($formData['last_name']) < 1)  $errors[] = "Last name is required.";
    if (!filter_var($formData['email'], FILTER_VALIDATE_EMAIL)) $errors[] = "Valid email is required.";
    if ($formData['grade_level'] < 9 || $formData['grade_level'] > 12) $errors[] = "Grade must be 9-12.";
    if ($formData['gpa'] < 0 || $formData['gpa'] > 4.0) $errors[] = "GPA must be between 0.00 and 4.00.";
    
    if (empty($errors)) {
        $stmt = $conn->prepare(
            "INSERT INTO students (first_name, last_name, email, grade_level, gpa)
             VALUES (?, ?, ?, ?, ?)"
        );
        $stmt->bind_param("sssid",
            $formData['first_name'],
            $formData['last_name'],
            $formData['email'],
            $formData['grade_level'],
            $formData['gpa']
        );
        
        try {
            $stmt->execute();
            setFlashMessage('success', 'Student added successfully!');
            redirect('index.php');
        } catch (mysqli_sql_exception $e) {
            if ($e->getCode() === 1062) {
                $errors[] = "A student with this email already exists.";
            } else {
                error_log("Insert error: " . $e->getMessage());
                $errors[] = "An unexpected error occurred.";
            }
        }
        
        $stmt->close();
    }
    
    $conn->close();
}
?>

<h1>Add New Student</h1>

<?php foreach ($errors as $err): ?>
    <p style="color: red;"><?= e($err) ?></p>
<?php endforeach; ?>

<form method="POST">
    <label for="first_name">First Name:</label>
    <input type="text" name="first_name" id="first_name"
           value="<?= e($formData['first_name']) ?>" required>
    
    <label for="last_name">Last Name:</label>
    <input type="text" name="last_name" id="last_name"
           value="<?= e($formData['last_name']) ?>" required>
    
    <label for="email">Email:</label>
    <input type="email" name="email" id="email"
           value="<?= e($formData['email']) ?>" required>
    
    <label for="grade_level">Grade Level:</label>
    <select name="grade_level" id="grade_level" required>
        <option value="">-- Select --</option>
        <?php for ($g = 9; $g <= 12; $g++): ?>
            <option value="<?= $g ?>" <?= $formData['grade_level'] == $g ? 'selected' : '' ?>>
                <?= $g ?>
            </option>
        <?php endfor; ?>
    </select>
    
    <label for="gpa">GPA (0.00 - 4.00):</label>
    <input type="number" name="gpa" id="gpa" step="0.01" min="0" max="4.00"
           value="<?= e($formData['gpa']) ?>" required>
    
    <br><br>
    <button type="submit" class="btn btn-success">Add Student</button>
    <a href="index.php">Cancel</a>
</form>

<?php require_once 'includes/footer.php'; ?>

---

17. Troubleshooting Common Issues

Connection Errors

Error	Cause	Solution
Access denied for user 'root'@'localhost'	Wrong password or auth method	Check credentials; use ALTER USER to set password
Can't connect to local MySQL server through socket	MySQL service not running	sudo systemctl start mysql
Connection refused	MySQL not listening or wrong port	Check bind-address and port in mysqld.cnf
Unknown database 'mydb'	Database doesn't exist	Create it: CREATE DATABASE mydb;
No such file or directory	Socket path mismatch	Use 127.0.0.1 instead of localhost

PHP Errors

Error	Cause	Solution
Call to undefined function mysqli_connect()	php-mysql extension not installed	sudo apt install php-mysql && sudo systemctl restart apache2
Fatal error: Uncaught Error: Class "mysqli" not found	Same as above	Install php-mysql and restart Apache
Headers already sent	Output before header() or session_start()	Ensure no HTML/whitespace before <?php
Maximum execution time exceeded	Query too slow or infinite loop	Optimize query; add indexes; increase max_execution_time

MySQL Query Errors

Error	Cause	Solution
1062: Duplicate entry	UNIQUE constraint violation	Check for existing records before inserting
1451: Cannot delete or update a parent row	Foreign key constraint	Delete child records first, or use ON DELETE CASCADE
1054: Unknown column	Typo in column name	Check spelling; run DESCRIBE table_name
1064: SQL syntax error	Malformed SQL	Check quotes, commas, parentheses
1146: Table doesn't exist	Table not created or wrong database	Run SHOW TABLES; to verify

Debugging Checklist

1. Enable error display (development only):

   ini_set('display_errors', 1);
   error_reporting(E_ALL);

2. Print the actual SQL being executed:

   echo $sql; // Temporarily display the query for debugging

3. Check MySQL error output:

   if (!$result) {
       echo "MySQL Error: " . $conn->error;
       echo "<br>Error Code: " . $conn->errno;
   }

4. Check the MySQL error log:

   sudo tail -50 /var/log/mysql/error.log

5. Check the PHP error log:

   sudo tail -50 /var/log/php/app_errors.log
   # or the default location:
   sudo tail -50 /var/log/apache2/error.log

6. Test the query directly in MySQL CLI:

   mysql -u webappuser -p school_portal

   Then paste and run the SQL directly to isolate PHP vs. MySQL issues.

7. Check PHP extension status:

   php -m | grep mysql

8. Verify Apache is loading PHP:

   sudo apachectl -M | grep php

---

Quick Reference Card

MySQLi Connection Template

$conn = new mysqli("localhost", "user", "pass", "dbname");
if ($conn->connect_error) die("Connection failed");
$conn->set_charset("utf8mb4");

Prepared Statement Template

$stmt = $conn->prepare("SELECT * FROM table WHERE col = ?");
$stmt->bind_param("s", $value);  // s=string, i=int, d=double, b=blob
$stmt->execute();
$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();

PDO Connection Template

$pdo = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8mb4", "user", "pass", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

Essential Security Functions

htmlspecialchars($value, ENT_QUOTES, 'UTF-8')  // XSS prevention
password_hash($password, PASSWORD_DEFAULT)       // Hash password
password_verify($input, $hash)                   // Check password
session_regenerate_id(true)                      // Prevent session fixation
bin2hex(random_bytes(32))                        // CSRF tokens
filter_var($email, FILTER_VALIDATE_EMAIL)        // Email validation

---