PHP: PDO Database Operations

Q: What's the difference between fetch() and fetchAll() ?

fetch() retrieves one row at a time (each call returns one row)—good for large result sets. fetchAll() retrieves everything into an array at once—simpler, but can consume a lot of memory for large datasets.

You've learned MySQL commands, but operating the database from PHP is the real goal. PDO (PHP Data Objects) is PHP's recommended standard database abstraction layer—one set of code works across 12 database types. Remember: learn PDO, skip MySQLi.

1. Why PDO?

	PDO	MySQLi
Supported databases	12 (MySQL/PostgreSQL/SQLite...)	MySQL only
Named parameters	✅ `:name`	❌ only `?`
Fetch associative arrays	✅ `PDO::FETCH_ASSOC`	✅
Fetch objects	✅ `PDO::FETCH_OBJ`	❌ (requires extra setup)
Exception mode	✅ Configurable	✅ Configurable
Modernity	🏆 Community standard	Legacy

2. PDO Connection

PHP

<?php
$host = 'localhost';
$dbname = 'myblog';
$username = 'root';
$password = '';
$charset = 'utf8mb4';

$dsn = "mysql:host={$host};dbname={$dbname};charset={$charset}";

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    echo "✅ Database connected successfully!<br>";
} catch (PDOException $e) {
    die("❌ Connection failed: " . $e->getMessage());
}
?>

Connection Option	Meaning	Why
`ERRMODE_EXCEPTION`	Throw exceptions on errors	No silent failures
`FETCH_ASSOC`	Default to associative arrays	Most commonly used format
`EMULATE_PREPARES => false`	Use real prepared statements	Genuine security

💡 Tip: These three PDO options are the standard configuration—always include them when creating a PDO connection. EMULATE_PREPARES => false ensures MySQL's native prepared statements are used instead of PDO-emulated ones.

3. query() — Running SELECT Queries

PHP

<?php
// Simple query
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll();

// Iterate results
foreach ($users as $user) {
    echo "ID: {$user['id']}, Username: {$user['username']}<br>";
}

// Fetch a single row
$stmt = $pdo->query("SELECT * FROM users WHERE id = 1");
$user = $stmt->fetch();

echo $user['username'];  // John
?>

(1) Fetch Modes

PHP

<?php
$stmt = $pdo->query("SELECT id, username FROM users");

// Associative array (default)
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['username'];

// Numeric index
$row = $stmt->fetch(PDO::FETCH_NUM);
echo $row[1];

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

// Fetch a single column
$usernames = $stmt->fetchAll(PDO::FETCH_COLUMN, 1);
print_r($usernames);  // ['John', 'Jane', ...]
?>

4. Prepared Statements (Core!)

Prepared statements are the key to preventing SQL injection:

▶ Example: Safe User Query

Output:

TEXT 📖 Display only

Found user: {root['username']}
User not found

PHP

<?php
// ❌ Dangerous: direct SQL concatenation (SQL injection vulnerability)
// $stmt = $pdo->query("SELECT * FROM users WHERE username = '$input'");

// ✅ Safe: prepared statement
$username = $_GET['username'] ?? '';

$sql = "SELECT * FROM users WHERE username = :username";
$stmt = $pdo->prepare($sql);
$stmt->execute(['username' => $username]);
$user = $stmt->fetch();

if ($user) {
    echo "Found user: {$user['username']}";
} else {
    echo "User not found";
}
?>

Output:

TEXT 📖 Display only

New user ID: {1}

(1) INSERT

PHP

<?php
$sql = "INSERT INTO users (username, email, password, age) 
        VALUES (:username, :email, :password, :age)";

$stmt = $pdo->prepare($sql);
$stmt->execute([
    'username' => 'Alex',
    'email'    => 'alex@example.com',
    'password' => password_hash('123456', PASSWORD_DEFAULT),
    'age'      => 24,
]);

$newId = $pdo->lastInsertId();
echo "New user ID: {$newId}";
?>

(2) UPDATE

PHP

<?php
$sql = "UPDATE users SET age = :age WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['age' => 27, 'id' => 1]);

echo "Affected {$stmt->rowCount()} row(s)";
?>

(3) DELETE

PHP

<?php
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 5]);

echo "Deleted {$stmt->rowCount()} row(s)";
?>

5. Complete CRUD in Practice

▶ Example: User Management Page

Output:

TEXT 📖 Display only

<p style='color:green'>User added successfully!</p>
<p style='color:green'>User deleted!</p>

PHP

<?php
// Database connection (same as above, omitted for brevity)
$pdo = new PDO($dsn, $username, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// Handle form submissions
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = $_POST['action'] ?? '';
    
    if ($action === 'add') {
        $stmt = $pdo->prepare(
            "INSERT INTO users (username, email, password, age) 
             VALUES (:username, :email, :password, :age)"
        );
        $stmt->execute([
            'username' => $_POST['username'],
            'email'    => $_POST['email'],
            'password' => password_hash($_POST['password'], PASSWORD_DEFAULT),
            'age'      => (int)$_POST['age'],
        ]);
        echo "<p style='color:green'>User added successfully!</p>";
    } elseif ($action === 'delete') {
        $stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
        $stmt->execute(['id' => (int)$_POST['id']]);
        echo "<p style='color:green'>User deleted!</p>";
    }
}

// Query all users
$users = $pdo->query("SELECT * FROM users ORDER BY id DESC")->fetchAll();
?>

<h2>User Management</h2>

<table border="1" cellpadding="8">
    <tr><th>ID</th><th>Username</th><th>Email</th><th>Age</th><th>Registered</th><th>Actions</th></tr>
    <?php foreach ($users as $u): ?>
    <tr>
        <td><?= $u['id'] ?></td>
        <td><?= htmlspecialchars($u['username']) ?></td>
        <td><?= htmlspecialchars($u['email']) ?></td>
        <td><?= $u['age'] ?></td>
        <td><?= $u['created_at'] ?></td>
        <td>
            <form method="POST" style="display:inline">
                <input type="hidden" name="action" value="delete">
                <input type="hidden" name="id" value="<?= $u['id'] ?>">
                <button type="submit" onclick="return confirm('Confirm deletion?')">Delete</button>
            </form>
        </td>
    </tr>
    <?php endforeach; ?>
</table>

<h3>Add User</h3>
<form method="POST">
    <input type="hidden" name="action" value="add">
    <input type="text" name="username" placeholder="Username" required>
    <input type="email" name="email" placeholder="Email" required>
    <input type="password" name="password" placeholder="Password" required>
    <input type="number" name="age" placeholder="Age">
    <button type="submit">Add</button>
</form>

Output:

TEXT 📖 Display only

User Management table with ID, Username, Email, Age columns
Add User form below

▶ Example: Batch Insert with Prepared Statements

Output:

TEXT 📖 Display only

Output from echo/print statements

PHP

<?php
$users = [
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob',   'email' => 'bob@example.com'],
    ['name' => 'Carol', 'email' => 'carol@example.com'],
];

$stmt = $pdo->prepare(
    "INSERT INTO users (username, email, password) VALUES (:name, :email, :pass)"
);

foreach ($users as $u) {
    $stmt->execute([
        'name'  => $u['name'],
        'email' => $u['email'],
        'pass'  => password_hash('secret', PASSWORD_DEFAULT),
    ]);
}

echo "Inserted {$stmt->rowCount()} user(s) per iteration, last ID: " . $pdo->lastInsertId();
?>

Output:

TEXT 📖 Display only

Output displayed

❓ FAQ

Q PDO or MySQLi—which should I pick?

A PDO. It supports 12 database types (switch databases without rewriting code), has cleaner prepared statement syntax (:name named parameters), and is the PHP community's accepted standard. MySQLi is only suitable for maintaining legacy projects.

Q What's the difference between fetch() and fetchAll()?

A fetch() retrieves one row at a time (each call returns one row)—good for large result sets. fetchAll() retrieves everything into an array at once—simpler, but can consume a lot of memory for large datasets.

📖 Summary

PDO is PHP's officially recommended database abstraction layer, supporting 12 database types
The three standard connection options: ERRMODE_EXCEPTION / FETCH_ASSOC / EMULATE_PREPARES=false
query() runs SELECT, fetchAll() retrieves all results, fetch() retrieves one
Prepared statements (prepare/execute) prevent SQL injection
:name named placeholders are clearer than ?
lastInsertId() gets the last inserted ID, rowCount() gets the number of affected rows

📝 Exercises

Use PDO to connect to the myblog database, query all data from the users table, and display it in a table.
Write a registration page with a form for username/email/password. Use PDO prepared statements to insert the new user (don't forget password_hash).
Add a "Delete" button to each row in the table from exercise 1. Submitting it should delete the corresponding user record.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

PHP: PDO Database Operations

1. Why PDO?

2. PDO Connection

3. query() — Running SELECT Queries

(1) Fetch Modes

4. Prepared Statements (Core!)

▶ Example: Safe User Query

(1) INSERT

(2) UPDATE

(3) DELETE

5. Complete CRUD in Practice

▶ Example: User Management Page

▶ Example: Batch Insert with Prepared Statements

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好