PHP MySQL CRUD Operations

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations performed on database records. Together, these operations cover virtually everything an application does with stored data: inserting new records, fetching existing ones, modifying them, and removing them. This topic demonstrates how to perform each CRUD operation using PDO with prepared statements.

All examples assume a database connection is already established in $pdo (see the PHP MySQL Database Connection topic) and a users table exists with the columns: id, username, email, created_at.

Create — INSERT

Inserting a new record uses an INSERT INTO SQL statement with placeholders for each value.

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

  function createUser(PDO $pdo, string $username, string $email, string $password): int {
    $sql = "INSERT INTO users (username, email, password) VALUES (:username, :email, :password)";
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
      ':username' => $username,
      ':email'    => $email,
      ':password' => password_hash($password, PASSWORD_DEFAULT),   // Always hash passwords
    ]);

    return (int)$pdo->lastInsertId();   // Returns the auto-generated ID
  }

  $newId = createUser($pdo, "alice", "alice@example.com", "securepassword");
  echo "New user created with ID: " . $newId;
?>

Read — SELECT

Fetch All Records

<?php
  function getAllUsers(PDO $pdo): array {
    $stmt = $pdo->query("SELECT id, username, email, created_at FROM users ORDER BY id");
    return $stmt->fetchAll();   // Returns array of associative arrays
  }

  $users = getAllUsers($pdo);

  foreach ($users as $user) {
    echo $user['id'] . " | " . $user['username'] . " | " . $user['email'] . "<br>";
  }
?>

Fetch a Single Record by ID

<?php
  function getUserById(PDO $pdo, int $id): ?array {
    $stmt = $pdo->prepare("SELECT id, username, email FROM users WHERE id = ?");
    $stmt->execute([$id]);
    $user = $stmt->fetch();   // Returns one row or false
    return $user ?: null;
  }

  $user = getUserById($pdo, 1);

  if ($user) {
    echo "Username: " . $user['username'] . ", Email: " . $user['email'];
  } else {
    echo "User not found.";
  }
?>

Search with Filtering

<?php
  function searchUsers(PDO $pdo, string $keyword): array {
    $sql = "SELECT id, username, email FROM users WHERE username LIKE :keyword OR email LIKE :keyword";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':keyword' => "%" . $keyword . "%"]);
    return $stmt->fetchAll();
  }

  $results = searchUsers($pdo, "alice");
  foreach ($results as $row) {
    echo $row['username'] . " - " . $row['email'] . "<br>";
  }
?>

Counting Records

<?php
  function countUsers(PDO $pdo): int {
    $stmt = $pdo->query("SELECT COUNT(*) FROM users");
    return (int)$stmt->fetchColumn();   // fetchColumn() gets the first column of first row
  }

  echo "Total users: " . countUsers($pdo);
?>

Update — UPDATE

<?php
  function updateUserEmail(PDO $pdo, int $userId, string $newEmail): int {
    $sql = "UPDATE users SET email = :email WHERE id = :id";
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
      ':email' => $newEmail,
      ':id'    => $userId,
    ]);

    return $stmt->rowCount();   // Number of rows affected
  }

  $rowsAffected = updateUserEmail($pdo, 1, "newalice@example.com");

  if ($rowsAffected > 0) {
    echo "Email updated successfully.";
  } else {
    echo "No rows were updated. User may not exist.";
  }
?>

Delete — DELETE

<?php
  function deleteUser(PDO $pdo, int $userId): int {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$userId]);
    return $stmt->rowCount();
  }

  $deleted = deleteUser($pdo, 5);

  if ($deleted) {
    echo "User deleted.";
  } else {
    echo "User not found.";
  }
?>

Transactions

Transactions group multiple operations into an all-or-nothing unit. If any operation fails, all changes are rolled back, keeping the database in a consistent state.

<?php
  function transferBalance(PDO $pdo, int $fromId, int $toId, float $amount): void {
    try {
      $pdo->beginTransaction();

      // Deduct from sender
      $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
      $stmt->execute([$amount, $fromId]);

      // Credit to receiver
      $stmt = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
      $stmt->execute([$amount, $toId]);

      $pdo->commit();   // Apply both changes
      echo "Transfer successful.";

    } catch (Exception $e) {
      $pdo->rollBack();   // Undo all changes if anything failed
      echo "Transfer failed: " . $e->getMessage();
    }
  }

  transferBalance($pdo, 1, 2, 100.00);
?>

Fetch Modes

PDO supports several modes for returning fetched data.

<?php
  // Fetch as associative array (default with FETCH_ASSOC setting)
  $row = $stmt->fetch(PDO::FETCH_ASSOC);
  // ['id' => 1, 'username' => 'alice']

  // Fetch as object
  $row = $stmt->fetch(PDO::FETCH_OBJ);
  echo $row->username;

  // Fetch all as array of objects
  $rows = $stmt->fetchAll(PDO::FETCH_OBJ);

  // Fetch into a specific class
  $users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
?>

Key Points

• CRUD operations map to SQL commands: Create = INSERT, Read = SELECT, Update = UPDATE, Delete = DELETE.
• Always use prepared statements with placeholders when any user data is involved in a query.
• lastInsertId() returns the auto-generated ID of the most recently inserted row.
• rowCount() returns the number of rows affected by an UPDATE or DELETE statement.
• fetch() retrieves one row; fetchAll() retrieves all rows; fetchColumn() retrieves a single value.
• Use transactions with beginTransaction(), commit(), and rollBack() when multiple operations must all succeed or all fail together.
• Always hash passwords with password_hash() before storing them — never store passwords as plain text.