ロジコヤ

3. SQLクエリー

SQLクエリーの基本

query()

実行するSQLをクエリー(Query) といい、PDOオブジェクトのquery() メソッドを実行します。

$pdo->query(SQL);

$pdo は PDOオブジェクト

PDO Statementオブジェクト

query() を実行すると PDO Statementオブジェクトが取得できます。

$stmt = $pdo->query(SQL);

SQLクエリーの実行

ファイル構成

basic/
├── env.php
├── DB.php
└── query_test.php

SQLクエリー実行

usersテーブルから10件選択するSQLクエリーを実行します。

query_test.php

<?php
require_once 'env.php';
require_once 'DB.php';

$db = new DB();

$sql = "SELECT * FROM users LIMIT 10;";
$stmt = $db->pdo->query($sql);
?>

オブジェクト確認

PDO StatementオブジェクトのqueryStringを確認します。

query_test.php

...

<!DOCTYPE html>
<html lang="ja">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL Query</title>
</head>

<body>
    <h3>SQL</h3>
    <p>
        <?= $stmt->queryString ?>
    </p>
</body>

</html>

ブラウザにSQLが表示されるか確認します。

結果

SQLエラーの場合

SQL構文やプログラムエラーが発生すると、エラーメッセージ表示されます。

ターミナル

Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: ...

フェッチ

フェッチとは

データベースからデータ取得する処理をフェッチ（Fetch）　といいます。PDOのフェッチ処理はメソッドがいくつかあり、PDO Statementから PHPで扱える配列やオブジェクトに変換します。

fetch()

fetch() はSQL結果を1件取得します。

PDOStatementオブジェクト->fetch();

fetchAll()

fetchAll() はSQL結果をすべて取得します。

PDOStatementオブジェクト->fetchAll();

フェッチモード

フェッチモードはデータをフェッチしたときの配列の構造を指定します。

モード	データ形式
PDO::FETCH_BOTH	カラム名と連番の配列
PDO::FETCH_ASSOC	カラム名の配列
PDO::FETCH_COLUMN	1カラム指定時に、連番の配列
PDO::FETCH_KEY_PAIR	2カラム指定時にキーと値の連想配列
PDO::FETCH_OBJ	カラム名をプロパティとしたオブジェクト

PDO::FETCH_ASSOC

カラムをキーにした配列 PDO::FETCH_ASSOC がよく利用されます。

$stmt->fetch(PDO::FETCH_ASSOC);

$stmt->fetchAll(PDO::FETCH_ASSOC);

ユーザデータ取得

fetchAll() で一括取得

fetchAll() で、「users」テーブルデータを多次元連想配列として取得します。

require_once 'env.php';
require_once 'DB.php';

$sql = 'SELECT * FROM users LIMIT 10;';
$stmt = $pdo->query($sql);

// データフェッチ
$users = $pdo->fetchAll(PDO::FETCH_ASSOC);

ユーザデータ表示

多次元連想配列を、HTMLで繰り返し表示します。

    <h3>users</h3>
    <ul>
        <?php foreach ($users as $key => $user) : ?>
            <li><?= $user['id']  ?></li>
            <li><?= $user['account_name']  ?></li>
            <li><?= $user['name']  ?></li>
            <li><?= $user['email']  ?></li>
            <li><?= $user['password']  ?></li>
        <?php endforeach ?>
    </ul>

結果

fetch() の繰り返し

fetchAll() はデータが大量になると負荷がかかるため、while でfetch() を1件ずつ繰り返して処理することもできます。

// データフェッチ
// $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// while で fetch() 繰り返し
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $users[] = $row;
}

プリペアドステートメント

プリペアドステートメントとは

Prepared Statement（プリペアドステートメント） はSQL文を実行する前に準備のことで、SQL文にパラメータを安全にバインドすることができ、SQLインジェクション攻撃を防ぐことができます。

主な役割

SQL文の準備

prepare() は、SQL文をデータベースに送信し、実行する前に準備します。この準備された文は、PDOStatementオブジェクトとして返されます。

パラメータのプレースホルダ

SQL文内の値の代わりにプレースホルダを使用できます。プレースホルダは名前付きプレースホルダ : と ? の特殊記号で表現し、SQL文とデータを分離できます。

プレースホルダ形式

SELECT * FROM users WHERE name = :name

パフォーマンス向上

prepare() で準備されたSQLはデータベースでキャッシュされます。同じSQL文を複数回実行するときに、異なるパラメータで複数回実行することでパフォーマンス向上が期待できます。

実行メソッド

prepare()

PDOでプリペアドステートメントを利用するには prepare() メソッドを利用します。

$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");

execute()

execute() で準備されたSQLを実行します。

// 通常のSQLを実行する場合
$stmt->execute();
// データ代入してSQLを実行する場合
$stmt->execute($data);

以下は、users.id で検索します。

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");

// 実行
$stmt->execute();

データバインド

プリペアドステートメントでプレースホルダー記述したSQLは、データバインドが必要です。PDOのデータバインドメソッドには、参照渡しの bindParam() と値渡しの bindValue() があります。

bindParam()

bindParam() は、パラメータを参照渡しでバインドします。バインドした変数の値を後で変更すると、その変更がクエリの実行時に反映されます。

$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");

$name = 'Alice';  // 変数の値を Alice
$stmt->bindParam(':name', $name);
$name = 'Bob'; // 変数の値を Alice から Bobに変更
$stmt->execute(); // 'John' が使用される

bindValue()

bindValue() は、パラメータを値渡しでバインドします。バインドした値は実行時に固定され、後で値を変更してもクエリには影響しません。

$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");

$name = 'Alice';  // 変数の値を Alice
$stmt->bindValue(':name', $name);
$name = 'Bob'; // 変数の値を変更しても影響なし
$stmt->execute(); // 'Alice' が使用される

ユーザ検索

ユーザ検索処理

クエリパラメータ（GETパラメータ）「user_id」がリクエストされたときに、ユーザ検索します。

sql_test.php

if (isset($_GET['user_id']) && $_GET['user_id'] > 0) {
    $sql = "SELECT * FROM users WHERE id = :id;";
    $stmt = $db->pdo->prepare($sql);
    // user_id バインド
    $stmt->bindValue(":id", $_GET['user_id']);
    $stmt->execute();
    // データフェッチ
    $select_user = $stmt->fetch(PDO::FETCH_ASSOC);
}

execute() に直接データをバインドすることもできます。

sql_test.php

if (isset($_GET['user_id']) && $_GET['user_id'] > 0) {
    $sql = "SELECT * FROM users WHERE id = :id;";
    $stmt = $db->pdo->prepare($sql);
    $data['id'] = $_GET['user_id'];
    // execute() で直接バインド
    $stmt->execute($data);
    // データフェッチ
    $select_user = $stmt->fetch(PDO::FETCH_ASSOC);
}

HTML表示

ユーザ一覧修正

ユーザ一覧表示で「user_id」クエリパラメータ付きの a リンクを追加します。

sql_test.php

    <h3>users</h3>
    <ul>
        <?php foreach ($users as $key => $user) : ?>
            <dt><?= $user['id'] ?></dt>
            <!-- 「user_id」クエリパラメータ付きのリンク -->
            <li><a href="?user_id=<?= $user['id'] ?>"><?= $user['account_name'] ?></a></li>
            <li><?= $user['name'] ?></li>
            <li><?= $user['email'] ?></li>
            <li><?= $user['password'] ?></li>
        <?php endforeach ?>
    </ul>

選択ユーザ表示

選択ユーザが存在した場合、HTML表示します。

sql_test.php

    <p>
        <?= $sql2 ?>
    </p>
    <?php if (isset($select_user) && $select_user["id"] > 0) : ?>
        <ul>
            <dt><?= $select_user['id']  ?></dt>
            <li><?= $select_user['account_name']  ?></li>
            <li><?= $select_user['name']  ?></li>
            <li><?= $select_user['email']  ?></li>
            <li><?= $select_user['password']  ?></li>
        </ul>
    <?php endif ?>

一覧表のユーザリンクで、ユーザデータが選択されるか確認します。

結果

<< Lesson 02 データベース設計

Lesson 04 CRUD処理 >>

PHP + MySQL Webサーバプログラミング

Lesson 01 PDOの基本
Lesson 02 データベース設計
Lesson 03 SQLクエリー
Lesson 04 CRUD処理
Lesson 05 プロジェクト作成
Lesson 06 ユーザ登録確認
Lesson 07 コンポーネント
Lesson 08 ユーザログイン
Lesson 09 投稿ページ
Lesson 10 投稿処理
Lesson 11 MVC
Lesson 12 ビュー
Lesson 13 コントローラー
Lesson 14 モデル
Lesson 15 Like機能
Lesson 20 管理者ページ