Useful PDO snippets

When developing scripts, you'll often need to read, insert, update and delete data, or to check whether data exists before doing anything else. Below are simple, reusable examples that show how to handle these situations safely and efficiently using PDO.

Jump to a snippet:

• 1. Select all rows from a table
• 1.1 Sort results with ORDER BY
• 2. Insert data
• 2.1 Insert data with existence check
• 3. Update data
• 3.1 Update data with existence check
• 4. Delete data by ID
• 4.1 Delete data by ID with existence check
• 5. Get one specific value from the data
• 6. Get all details for one user
• 7. Count data
• 8. Get the ID of the last inserted record
• 9. Search by partial value
• 10. Paginate results (Basic example)

Make sure your database.php connection file is ready and working. We'll use it throughout. Read Connecting to a database to create one.

<?php
$pdo = include 'database.php';

1. Select all rows from a table

This is how you select data from your database, e.g. to list all users:

<?php
$pdo = include 'database.php';

$query = $pdo->query('SELECT * FROM users');
$users = $query->fetchAll(PDO::FETCH_ASSOC);

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

1.1 Sort results with ORDER BY

To sort your query results, you can add ORDER BY followed by the column name and the sorting direction: ASC for ascending (default) or DESC for descending order.

For example, to list users sorted by their username in ascending order:

<?php
$pdo = include 'database.php';

$query = $pdo->query('SELECT * FROM users ORDER BY username ASC');
$users = $query->fetchAll(PDO::FETCH_ASSOC);

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

Or, to list users sorted by username in descending order:

<?php
$pdo = include 'database.php';

$query = $pdo->query('SELECT * FROM users ORDER BY username DESC');
$users = $query->fetchAll(PDO::FETCH_ASSOC);

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

2. Insert data

This is how you insert data into your database, e.g. to add a new user (with a password hash for the password 'password' in this example):

<?php
$pdo = include 'database.php';

$username = 'newuser';
$email = 'newuser@example.com';
$password = '$2y$10$9xW.AkFrzxIdCNftdZctnucuXs952jyHZBJvFdW2MCyYzgLqbfUY.';

$query = $pdo->prepare('INSERT INTO users (username, email, password) VALUES (:username, :email, :password)');
$success = $query->execute([
    ':username' => $username,
    ':email' => $email,
    ':password' => $password
]);

if ($success) {
    echo 'User added successfully.';
} else {
    echo 'Failed to add user.';
}

2.1. Insert data with existence check

Before adding a new user, check if the email is already registered to avoid duplicates:

<?php
$pdo = include 'database.php';

$username = 'newuser';
$email = 'newuser@example.com';
$password = '$2y$10$9xW.AkFrzxIdCNftdZctnucuXs952jyHZBJvFdW2MCyYzgLqbfUY.';

// Check if email already exists
$check = $pdo->prepare('SELECT COUNT(*) FROM users WHERE email = :email');
$check->execute([':email' => $email]);

if ($check->fetchColumn() > 0) {
    echo 'This email is already registered.';
} else {
    // Email not found, proceed to insert
    $query = $pdo->prepare('INSERT INTO users (username, email, password) VALUES (:username, :email, :password)');
    $success = $query->execute([
        ':username' => $username,
        ':email' => $email,
        ':password' => $password
    ]);

    echo $success ? 'User added successfully.' : 'Failed to add user.';
}

3. Update data

This is how you edit data in your database, e.g. to change a user's email address:

<?php
$pdo = include 'database.php';

$id = 1;
$newEmail = 'updated@example.com';

$query = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');
$success = $query->execute([
    ':email' => $newEmail,
    ':id' => $id
]);

if ($success) {
    echo 'Email updated.';
} else {
    echo 'Update failed.';
}

3.1. Update data with existence check

Before updating, verify that the user exists to avoid errors or no changes:

<?php
$pdo = include 'database.php';

$id = 1;
$newEmail = 'updated@example.com';

// Check if user exists
$check = $pdo->prepare('SELECT COUNT(*) FROM users WHERE id = :id');
$check->execute([':id' => $id]);

if ($check->fetchColumn() == 0) {
    echo 'User not found, nothing updated.';
} else {
    // User exists, proceed with update
    $query = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');
    $success = $query->execute([
        ':email' => $newEmail,
        ':id' => $id
    ]);

    echo $success ? 'Email updated.' : 'Update failed.';
}

4. Delete data by ID

This is how you delete data in your database, e.g. to delete a user by their ID:

<?php
$pdo = include 'database.php';

$id = 2;

$query = $pdo->prepare('DELETE FROM users WHERE id = :id');
$success = $query->execute([':id' => $id]);

if ($success) {
    echo 'User deleted.';
} else {
    echo 'Deletion failed.';
}

4.1. Delete data by ID with existence check

Before deleting, check if the user exists to avoid errors or deleting nothing by mistake:

<?php
$pdo = include 'database.php';

$id = 2;

// Check if user exists
$check = $pdo->prepare('SELECT COUNT(*) FROM users WHERE id = :id');
$check->execute([':id' => $id]);

if ($check->fetchColumn() == 0) {
    echo 'User not found, nothing deleted.';
} else {
    // User exists, delete now
    $query = $pdo->prepare('DELETE FROM users WHERE id = :id');
    $success = $query->execute([':id' => $id]);

    echo $success ? 'User deleted.' : 'Deletion failed.';
}

5. Get one specific value from the data

You don't always need all the information from a result—sometimes you just want one piece of it, like a name or an email address. This shows how to get just that single value.

<?php
$pdo = include 'database.php';

$id = 1;

$query = $pdo->prepare('SELECT username FROM users WHERE id = :id');
$query->execute([':id' => $id]);
$username = $query->fetchColumn();

if ($username) {
    echo "Username: " . htmlspecialchars($username);
} else {
    echo "User not found.";
}

6. Get all details for one user

This is helpful when you want to load a user's information—like their name, email, or other details—into a form for editing or display.

<?php
$pdo = include 'database.php';

$id = 1;

$query = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$query->execute([':id' => $id]);
$user = $query->fetch(PDO::FETCH_ASSOC);

if ($user) {
    echo 'Username: ' . htmlspecialchars($user['username']);
}

7. Count data

This is how you can count data, e.g. all users. Helpful for a dashboard, pagination etc.:

<?php
$pdo = include 'database.php';

$query = $pdo->query('SELECT COUNT(*) FROM users');
$total = $query->fetchColumn();

echo "Total users: " . $total;

8. Get the ID of the last inserted record

After adding something to the database, you can get the ID that was automatically assigned to it. This is useful if you need to use that ID right away—for example, to load or link to the new data.

<?php
$pdo = include 'database.php';

$username = 'newuser';
$email = 'newuser@example.com';
$password = '$2y$10$9xW.AkFrzxIdCNftdZctnucuXs952jyHZBJvFdW2MCyYzgLqbfUY.';

$query = $pdo->prepare('INSERT INTO users (username, email, password) VALUES (:username, :email, :password)');
$query->execute([
    ':username' => $username,
    ':email' => $email,
    ':password' => $password
]);

$lastId = $pdo->lastInsertId();
echo "New user ID: " . $lastId;

9. Search by partial value

You can use a partial match to find records that contain a certain word or phrase. This is useful when you don't know the exact value, like when searching names, titles, or keywords.

<?php
$pdo = include 'database.php';

$search = 'john';

$query = $pdo->prepare("SELECT * FROM users WHERE username LIKE :search");
$query->execute([':search' => "%$search%"]);
$results = $query->fetchAll(PDO::FETCH_ASSOC);

10. Paginate results (Basic example)

When you have lots of data, you can split it into smaller pages and let users move between them using pagination links. This is a very basic example how to do it:

<?php
$pdo = include 'database.php';

// Set current page from URL or default to 1
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$limit = 10; // Items per page
$offset = ($page - 1) * $limit;

// Count total rows
$totalQuery = $pdo->query('SELECT COUNT(*) FROM users');
$totalRows = (int) $totalQuery->fetchColumn();
$totalPages = ceil($totalRows / $limit);

// Fetch rows for current page
$query = $pdo->prepare('SELECT * FROM users ORDER BY id DESC LIMIT :limit OFFSET :offset');
$query->bindValue(':limit', $limit, PDO::PARAM_INT);
$query->bindValue(':offset', $offset, PDO::PARAM_INT);
$query->execute();
$users = $query->fetchAll(PDO::FETCH_ASSOC);

// Display users
foreach ($users as $user) {
    echo htmlspecialchars($user['username']) . "<br>";
}

// Pagination links
echo "<div class='pagination'>";
if ($page > 1) {
    echo '<a href="?page=' . ($page - 1) . '">Previous</a> ';
}

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

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

Try the examples — create a test table (optional)

If you want to try the code examples in this tutorial, you can create a simple users table in your database. This step is optional if you already have a table to work with.

To create the users table, log into your hosting control panel (cPanel, Plesk, etc.), open phpMyAdmin, select your database, go to the SQL tab, and paste this command:

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This creates a basic table with an ID, username, email, password and a timestamp for when each user was added.

11. Best Practices

• - Always use placeholders and execute() to safely query data and avoid SQL injection.
• - Use htmlspecialchars() when displaying data on the page to prevent HTML issues and security problems.
• - Keep your database connection code separate (like in database.php) and include it wherever you need to query.
• - Use fetch() for a single row, fetchColumn() for a single value, and fetchAll() for multiple rows.
• - Never use raw user input directly in queries — always bind or sanitize properly.