MySQL

advanced

Part of PHP Data & APIs

Theory

PHP and MySQL are a powerful combination for building dynamic, database-driven websites. PHP provides two primary ways to interact with MySQL: mysqli (MySQL improved) and PDO (PHP Data Objects).

Connecting to MySQL

mysqli_connect (procedural style):

<?php
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'myapp';
 
// Create connection
$conn = mysqli_connect($host, $user, $password, $database);
 
// Check connection
if (!$conn) {
    die('Connection failed: ' . mysqli_connect_error());
}
 
echo 'Connected successfully';
 
// Close connection
mysqli_close($conn);
?>

PDO (object-oriented, supports multiple databases):

<?php
$host = 'localhost';
$dbname = 'myapp';
$username = 'root';
$password = '';
 
try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
    echo 'Connected successfully';
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}
?>

Creating Databases and Tables

<?php
$conn = mysqli_connect('localhost', 'root', '', 'myapp');
 
// Create a table
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
 
if (mysqli_query($conn, $sql)) {
    echo 'Table created successfully';
} else {
    echo 'Error: ' . mysqli_error($conn);
}
?>

INSERT

<?php
// mysqli
$username = 'alice';
$email = 'alice@example.com';
$password = password_hash('secret123', PASSWORD_DEFAULT);
 
$sql = "INSERT INTO users (username, email, password)
        VALUES ('$username', '$email', '$password')";
 
if (mysqli_query($conn, $sql)) {
    $lastId = mysqli_insert_id($conn);
    echo "New user created with ID: $lastId";
}
 
// PDO (with prepared statement — safe!)
$stmt = $pdo->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
$stmt->execute(['alice', 'alice@example.com', password_hash('secret123', PASSWORD_DEFAULT)]);
$lastId = $pdo->lastInsertId();
?>

SELECT

<?php
// mysqli
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
 
// Fetch as associative array
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['username'] . ' - ' . $row['email'] . '<br>';
}
 
// PDO
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll();
 
foreach ($users as $user) {
    echo $user['username'] . ' - ' . $user['email'] . '<br>';
}
 
// WHERE clause with prepared statement
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();
 
if ($user) {
    echo 'Found: ' . $user['username'];
}
?>

UPDATE

<?php
// mysqli
$id = 1;
$newEmail = 'alice_new@example.com';
$sql = "UPDATE users SET email = '$newEmail' WHERE id = $id";
 
if (mysqli_query($conn, $sql)) {
    echo mysqli_affected_rows($conn) . ' row(s) updated';
}
 
// PDO (prepared)
$stmt = $pdo->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->execute(['alice_new@example.com', 1]);
echo $stmt->rowCount() . ' row(s) updated';
?>

DELETE

<?php
// mysqli
$id = 3;
$sql = "DELETE FROM users WHERE id = $id";
 
if (mysqli_query($conn, $sql)) {
    echo mysqli_affected_rows($conn) . ' row(s) deleted';
}
 
// PDO (prepared)
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([$id]);
echo $stmt->rowCount() . ' row(s) deleted';
?>

Prepared Statements (Preventing SQL Injection)

SQL injection is one of the most dangerous web vulnerabilities. Attackers can inject malicious SQL code through unsanitized input:

// DANGEROUS — vulnerable to SQL injection
$sql = "SELECT * FROM users WHERE username = '$_POST[username]'";
// If username = ' OR '1'='1, this becomes:
// SELECT * FROM users WHERE username = '' OR '1'='1' (returns all users!)

Prepared statements separate SQL logic from data, making injection impossible:

mysqli prepared statements:

<?php
$stmt = mysqli_prepare($conn, "INSERT INTO users (username, email) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, 'ss', $username, $email);
 
$username = 'bob';
$email = 'bob@example.com';
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
?>

PDO prepared statements (cleaner):

<?php
// Named placeholders
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->execute([
    'username' => 'bob',
    'email' => 'bob@example.com',
]);
 
// OR positional placeholders
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(['bob', 'bob@example.com']);
?>

Always use prepared statements for any query that includes user input!

Fetching Data

<?php
// mysqli_fetch_assoc — returns associative array
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['username'];
}
 
// mysqli_fetch_row — returns indexed array
while ($row = mysqli_fetch_row($result)) {
    echo $row[0]; // First column
}
 
// mysqli_fetch_object — returns object
while ($row = mysqli_fetch_object($result)) {
    echo $row->username;
}
 
// PDO fetch modes
$stmt = $pdo->query("SELECT * FROM users");
 
// Single row
$user = $stmt->fetch(PDO::FETCH_ASSOC);   // Associative array
$user = $stmt->fetch(PDO::FETCH_OBJ);      // Object
$user = $stmt->fetch(PDO::FETCH_NUM);      // Indexed array
 
// All rows
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
    echo $user['username'] . ' - ' . $user['email'] . '<br>';
}
 
// Count rows
$count = $stmt->rowCount();
?>

Basic CRUD Application

A complete CRUD (Create, Read, Update, Delete) structure:

<?php
// config.php
$pdo = new PDO('mysql:host=localhost;dbname=myapp;charset=utf8mb4',
    'root', '', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
 
// create.php
$stmt = $pdo->prepare("INSERT INTO posts (title, body) VALUES (?, ?)");
$stmt->execute([$_POST['title'], $_POST['body']]);
 
// read.php
$stmt = $pdo->query("SELECT * FROM posts ORDER BY created_at DESC");
$posts = $stmt->fetchAll();
 
// update.php
$stmt = $pdo->prepare("UPDATE posts SET title = ?, body = ? WHERE id = ?");
$stmt->execute([$_POST['title'], $_POST['body'], $_GET['id']]);
 
// delete.php
$stmt = $pdo->prepare("DELETE FROM posts WHERE id = ?");
$stmt->execute([$_GET['id']]);
?>

PDO is generally preferred over mysqli because it supports multiple database types (MySQL, PostgreSQL, SQLite) and has a cleaner, more modern API with named parameters.

Practical Examples

Example 1: User Registration and Login with Database

php

Example 2: Blog Post Manager (CRUD)

php

Always use prepared statements with bound parameters when including user data in SQL queries. This is the single most important step in preventing SQL injection attacks.

Exercises

Product Database CRUD

easy

Create a products table (id, name, price, category, quantity) and build a complete CRUD interface. Use prepared statements for all database operations.

Expected Output:

A working product management page with the ability to add, list, edit, and delete products. All queries use prepared statements.

User Search and Filter

medium

Build a user search page that connects to a users table and supports: search by name or email, filter by registration date range, sort by different columns, and pagination (10 users per page).

Expected Output:

A searchable, filterable, sortable, paginated user table with results 10 per page and navigation.

Comment System with JOINs

hard

Create a blog comment system with two tables: posts and comments (with user_id foreign key). Display each post with its comments. Implement adding and deleting comments. Use JOIN queries.

Expected Output:

A blog page showing posts with their comments. Each post shows the number of comments. Users can add comments. JOIN queries efficiently fetch related data.

Mini Quiz

Mini Project

Mini Project: Task Management Application with Database

Build a full-featured task management app with a MySQL database. Users can create projects, add tasks to projects, assign priorities, set due dates, and mark tasks as complete.

Requirements:

Bonus Challenge

Add a dashboard with charts showing task completion rates. Implement drag-and-drop task reordering (store the order as an integer column). Add email notifications for upcoming due dates.