Database Operations

Learn to connect to databases and perform CRUD operations with MySQL and PDO.

Database Integration

Connect your PHP applications to databases for persistent data storage. Learn MySQLi and PDO approaches.

Database Connection

MySQLi (Object-Oriented)

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

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

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

echo "Connected successfully!";

// Close connection
$conn->close();
?>

PDO (Recommended)

<?php
try {
    $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
        ]
    );
    echo "Connected via PDO!";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

Pro Tip: PDO is recommended because it supports multiple databases and has better security features. Always use prepared statements to prevent SQL injection.

CRUD Operations

CREATE - Insert Data

SQL Insert

<?php
// Prepared statement (safe from SQL injection)
$sql = "INSERT INTO users (name, email, password) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);

// Hash password before saving!
$hashedPassword = password_hash($_POST["password"], PASSWORD_DEFAULT);

$stmt->execute([
    $_POST["name"],
    $_POST["email"],
    $hashedPassword
]);

echo "User created with ID: " . $pdo->lastInsertId();
?>

READ - Fetch Data

SQL Select

<?php
// Fetch single row
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$id]);
$user = $stmt->fetch();

// Fetch all rows
$sql = "SELECT * FROM users WHERE status = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(["active"]);
$users = $stmt->fetchAll();

// Loop through results
foreach ($users as $row) {
    echo $row["name"] . "<br>";
}
?>

UPDATE - Modify Data

SQL Update

<?php
$sql = "UPDATE users SET email = ?, updated_at = NOW() WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$newEmail, $userId]);

echo "Rows updated: " . $stmt->rowCount();
?>

DELETE - Remove Data

SQL Delete

<?php
// Delete single record
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$id]);

// Delete with condition
$sql = "DELETE FROM users WHERE status = ? AND created_at < ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(["inactive", $cutoffDate]);
?>

Working with MySQL Tables

SQL Create Table

-- Create users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create posts table with foreign key
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

✎ Hands-on Exercises

Exercise 1: Connect to a MySQL database using PDO
Exercise 2: Create a "students" table with name, age, and grade columns
Exercise 3: Insert 3 student records using prepared statements
Exercise 4: Display all students in an HTML table
Exercise 5: Add update and delete functionality with confirmation