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:

▶ サンプル: Safe User Query

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";
}
?>

▶ 試してみよう

(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

▶ サンプル: User Management Page

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>

論理コード 56 行（40 行制限超過、参照専用）

❓ よくある質問

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.

📖 まとめ

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

📝 練習問題

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 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて