PHP Intermediate

PHP and MySQL Database Integration

CodingerWeb
CodingerWeb
23 views 70 min read

PHP and MySQL Database Integration

Databases are essential for storing and retrieving data in web applications. MySQL is one of the most popular databases used with PHP.

Database Connection

Connect to MySQL using PDO (recommended) or MySQLi:

PDO Connection

<?php
// Database configuration
$host = "localhost";
$dbname = "myapp";
$username = "root";
$password = "";

try {
    // Create PDO connection
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", 
                   $username, $password);
    
    // Set error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Connected successfully";
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

MySQLi Connection

<?php
$host = "localhost";
$username = "root";
$password = "";
$dbname = "myapp";

// Object-oriented style
$mysqli = new mysqli($host, $username, $password, $dbname);

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

echo "Connected successfully";

// Procedural style
$connection = mysqli_connect($host, $username, $password, $dbname);
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

Creating Database and Tables

Set up your database structure:

-- Create database
CREATE DATABASE myapp;
USE myapp;

-- Create users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create posts table
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status ENUM('draft', 'published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create comments table
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CRUD Operations with PDO

Create (INSERT)

<?php
// Insert new user
function createUser($pdo, $username, $email, $password) {
    $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
    
    $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    
    try {
        $stmt->execute([$username, $email, $hashedPassword]);
        return $pdo->lastInsertId();
    } catch(PDOException $e) {
        throw new Exception("Error creating user: " . $e->getMessage());
    }
}

// Usage
try {
    $userId = createUser($pdo, "john_doe", "john@example.com", "mypassword");
    echo "User created with ID: " . $userId;
} catch(Exception $e) {
    echo $e->getMessage();
}
?>

Read (SELECT)

<?php
// Get single user
function getUserById($pdo, $id) {
    $sql = "SELECT id, username, email, created_at FROM users WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$id]);
    
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Get all users
function getAllUsers($pdo) {
    $sql = "SELECT id, username, email, created_at FROM users ORDER BY created_at DESC";
    $stmt = $pdo->query($sql);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Get users with pagination
function getUsersPaginated($pdo, $page = 1, $perPage = 10) {
    $offset = ($page - 1) * $perPage;
    
    $sql = "SELECT id, username, email, created_at 
            FROM users 
            ORDER BY created_at DESC 
            LIMIT ? OFFSET ?";
    
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(1, $perPage, PDO::PARAM_INT);
    $stmt->bindValue(2, $offset, PDO::PARAM_INT);
    $stmt->execute();
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Usage
$user = getUserById($pdo, 1);
if ($user) {
    echo "Username: " . $user['username'];
} else {
    echo "User not found";
}

$users = getAllUsers($pdo);
foreach ($users as $user) {
    echo $user['username'] . " - " . $user['email'] . "
";
}
?>

Update

<?php
// Update user
function updateUser($pdo, $id, $username, $email) {
    $sql = "UPDATE users SET username = ?, email = ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    
    try {
        $stmt->execute([$username, $email, $id]);
        return $stmt->rowCount(); // Returns number of affected rows
    } catch(PDOException $e) {
        throw new Exception("Error updating user: " . $e->getMessage());
    }
}

// Update password
function updatePassword($pdo, $id, $newPassword) {
    $hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT);
    
    $sql = "UPDATE users SET password = ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    
    return $stmt->execute([$hashedPassword, $id]);
}

// Usage
try {
    $affected = updateUser($pdo, 1, "john_updated", "john.new@example.com");
    echo "Updated $affected user(s)";
} catch(Exception $e) {
    echo $e->getMessage();
}
?>

Delete

<?php
// Delete user
function deleteUser($pdo, $id) {
    $sql = "DELETE FROM users WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    
    try {
        $stmt->execute([$id]);
        return $stmt->rowCount();
    } catch(PDOException $e) {
        throw new Exception("Error deleting user: " . $e->getMessage());
    }
}

// Soft delete (mark as deleted instead of removing)
function softDeleteUser($pdo, $id) {
    $sql = "UPDATE users SET deleted_at = NOW() WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    
    return $stmt->execute([$id]);
}

// Usage
try {
    $deleted = deleteUser($pdo, 1);
    if ($deleted) {
        echo "User deleted successfully";
    } else {
        echo "User not found";
    }
} catch(Exception $e) {
    echo $e->getMessage();
}
?>

Advanced Queries

JOIN Queries

<?php
// Get posts with user information
function getPostsWithUsers($pdo) {
    $sql = "SELECT p.id, p.title, p.content, p.created_at,
                   u.username, u.email
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE p.status = 'published'
            ORDER BY p.created_at DESC";
    
    $stmt = $pdo->query($sql);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Get posts with comment count
function getPostsWithCommentCount($pdo) {
    $sql = "SELECT p.id, p.title, p.created_at,
                   u.username,
                   COUNT(c.id) as comment_count
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            LEFT JOIN comments c ON p.id = c.post_id
            WHERE p.status = 'published'
            GROUP BY p.id, p.title, p.created_at, u.username
            ORDER BY p.created_at DESC";
    
    $stmt = $pdo->query($sql);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>

Search and Filtering

<?php
// Search posts
function searchPosts($pdo, $searchTerm, $userId = null) {
    $sql = "SELECT p.id, p.title, p.content, p.created_at,
                   u.username
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE p.status = 'published'
            AND (p.title LIKE ? OR p.content LIKE ?)";
    
    $params = ["%$searchTerm%", "%$searchTerm%"];
    
    if ($userId) {
        $sql .= " AND p.user_id = ?";
        $params[] = $userId;
    }
    
    $sql .= " ORDER BY p.created_at DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Filter posts by date range
function getPostsByDateRange($pdo, $startDate, $endDate) {
    $sql = "SELECT p.id, p.title, p.created_at, u.username
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE p.created_at BETWEEN ? AND ?
            AND p.status = 'published'
            ORDER BY p.created_at DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$startDate, $endDate]);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>

Transactions

Ensure data consistency with transactions:

<?php
// Transfer operation example
function transferMoney($pdo, $fromUserId, $toUserId, $amount) {
    try {
        // Start transaction
        $pdo->beginTransaction();
        
        // Check sender balance
        $stmt = $pdo->prepare("SELECT balance FROM accounts WHERE user_id = ?");
        $stmt->execute([$fromUserId]);
        $senderBalance = $stmt->fetchColumn();
        
        if ($senderBalance < $amount) {
            throw new Exception("Insufficient funds");
        }
        
        // Deduct from sender
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE user_id = ?");
        $stmt->execute([$amount, $fromUserId]);
        
        // Add to receiver
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE user_id = ?");
        $stmt->execute([$amount, $toUserId]);
        
        // Log transaction
        $stmt = $pdo->prepare("INSERT INTO transactions (from_user_id, to_user_id, amount, created_at) VALUES (?, ?, ?, NOW())");
        $stmt->execute([$fromUserId, $toUserId, $amount]);
        
        // Commit transaction
        $pdo->commit();
        
        return true;
    } catch (Exception $e) {
        // Rollback on error
        $pdo->rollback();
        throw $e;
    }
}
?>

Database Class Example

Create a reusable database class:

<?php
class Database {
    private $pdo;
    
    public function __construct($host, $dbname, $username, $password) {
        try {
            $this->pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", 
                                $username, $password);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            throw new Exception("Database connection failed: " . $e->getMessage());
        }
    }
    
    public function query($sql, $params = []) {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
    
    public function fetch($sql, $params = []) {
        return $this->query($sql, $params)->fetch(PDO::FETCH_ASSOC);
    }
    
    public function fetchAll($sql, $params = []) {
        return $this->query($sql, $params)->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function insert($table, $data) {
        $columns = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        
        $sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
        $this->query($sql, $data);
        
        return $this->pdo->lastInsertId();
    }
    
    public function update($table, $data, $where, $whereParams = []) {
        $set = [];
        foreach ($data as $key => $value) {
            $set[] = "$key = :$key";
        }
        $setClause = implode(', ', $set);
        
        $sql = "UPDATE $table SET $setClause WHERE $where";
        $params = array_merge($data, $whereParams);
        
        return $this->query($sql, $params)->rowCount();
    }
    
    public function delete($table, $where, $params = []) {
        $sql = "DELETE FROM $table WHERE $where";
        return $this->query($sql, $params)->rowCount();
    }
}

// Usage
$db = new Database("localhost", "myapp", "root", "");

// Insert user
$userId = $db->insert("users", [
    "username" => "jane_doe",
    "email" => "jane@example.com",
    "password" => password_hash("password", PASSWORD_DEFAULT)
]);

// Get user
$user = $db->fetch("SELECT * FROM users WHERE id = ?", [$userId]);

// Update user
$db->update("users", 
    ["email" => "jane.new@example.com"], 
    "id = :id", 
    ["id" => $userId]
);

// Delete user
$db->delete("users", "id = ?", [$userId]);
?>

Security Best Practices

Protect against SQL injection and other attacks:

<?php
// Always use prepared statements
// BAD - vulnerable to SQL injection
$username = $_POST["username"];
$sql = "SELECT * FROM users WHERE username = '$username'";

// GOOD - safe from SQL injection
$username = $_POST["username"];
$sql = "SELECT * FROM users WHERE username = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$username]);

// Validate and sanitize input
function validateEmail($email) {
    return filter_var($email, FILTER_VALIDATE_EMAIL);
}

function sanitizeString($string) {
    return htmlspecialchars(trim($string), ENT_QUOTES, 'UTF-8');
}

// Hash passwords properly
$password = $_POST["password"];
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);

// Verify passwords
if (password_verify($inputPassword, $hashedPassword)) {
    // Password is correct
}

// Limit database permissions
// Create separate database users with minimal required permissions
// Don't use root user for web applications
?>

Practice Exercise

Create a simple blog system with:

  • User registration and login
  • Create, read, update, delete posts
  • Comment system
  • Search functionality
  • Proper error handling and security

Key Takeaways

  • Use PDO for database connections (more secure and flexible)
  • Always use prepared statements to prevent SQL injection
  • Implement proper error handling
  • Use transactions for operations that must succeed or fail together
  • Hash passwords and validate all user input