Help us understand the problem. What is going on with this article?

PHPでPDOを使ってAND検索(OR検索)を実装する

More than 5 years have passed since last update.

概要

  • 日本語で検索
  • GETパラメータをキーワードとして検索
  • PDO使ってMySQLに接続し、プレースホルダを使ってSQLを構築する
  • 可変長なAND(OR)検索

ポイント

日本語対策

  • mb_internal_encoding('UTF-8');をファイルの先頭に書く
    • 今回は特に関係ないっぽいですが、とりあえず書いてます
  • PDO作成の際にutf8を指定する
  • MySQLの設定を変更する

    • こうなってたら動いた

    mysql> show variables like 'character_set_%';

    Variable_name Value
    character_set_client utf8
    character_set_connection utf8
    character_set_database latin1
    character_set_filesystem utf8
    character_set_results utf8
    character_set_server utf8
    character_set_system utf8

tips

  • SQLの組み立てから実行まで

    1. SQL文をprepareする
    2. プレースホルダに変数をbindParamする
    3. executeする
    4. while文の中でfetchして一行ずつ取り出す
  • プレースホルダにbindParamをする際に型を指定できる

    • デフォルトだと数値を入れたら文字列にされてしまう
    • 第3引数にPDO::PARAM_INTPDO::PARAM_STRを指定すれば良い
  • SQLでカラム名を指定する場合(FROM句やORDER BY句)

  • $_GETのエスケープ

    • htmlspecialchars()はDBから得た結果をhtmlとして出力する際に呼ぶといいらしい
    • プレースホルダを使ってSQLを組み立てる場合は必要ない
      • 自分でやる場合はPDO::quoteが使えるっぽいが、prepareしましょう
  • bindParamする変数に注意する

    • 同じ変数を書き換えながら複数のプレースホルダにbindParamすると、その変数の最後の値が全てのプレースホルダに入ってしまう
      • 実行時にバインドされるっぽい
    • bindValueを使えばその場でバインドされる
  • SELECT文を実行した際の結果数を取得したい

    • 別でSELECT COUNT(*)を実行しないとだめっぽい...なんでだよ...

AND検索、OR検索

  • SQLでのAND検索とOR検索

    select * from product where name like "hoge" and (name like "foo" or name like "bar") limit 100 order by id;
    
    • 普通にandorlike句を繋げたら出来る

関数から複数の返り値を返し、受け取る

  • 返り値
    • return array($foo, $bar);
  • 受け取る
    • list($foo, $bar) = ...;

コード

  1. productテーブルからwhere句でnameでAND検索をして、limitorder byを指定する

流れ

  1. $_GETからパラメータを取り出す
  2. パラメータのバリデーション
  3. AND検索をするために前処理
    • もちろん、1単語ならAND検索にはならない
  4. PDOにSQLをprepareしてプレースホルダにbindParamしてexecute
  5. 結果の取得

function.php

functions.php
<?php
mb_internal_encoding('UTF-8');
require('./_config.php');  // MySQL接続用の定数を定義してある

function connectDB() {
  // DB接続用
  try {
    // utf8で接続する
    $data_source =
      sprintf("mysql:dbname=%s;host=%s;charset=%s", DB_NAME, DB_HOST, "utf8");
    $pdo = new PDO($data_source , DB_USERNAME, DB_PASSWD);
    return $pdo;
  } catch (PDOException $e) {
    var_dump($e->getMessage());
    exit('cannot connect to database');
  }
}


search.php

search.php
<?php
mb_internal_encoding('UTF-8');
require_once('./functions.php');

function productSearchAPI() {
  // $_GETからパラメータを取り出してバリデーション
  list($params, $paramsType) = getValidParamater($_GET);
  if ($params[":query"] === "") {
    exit("invalid query...");
  }
  // SQLを組み立てて実行
  $results = doSearch($params, $paramsType);  
}

function getValidParamater($params){
  // GETパラメータをチェックする
  if(isset($params['query']) && is_string($params['query'])) {
    // 全角スペース => 半角
    $query = $params['query'];
    $query = str_replace(" ", " ", $query);
  } else {
    $query = "";
  }

  if(isset($params['limit']) && is_int($params['limit'])) {
    $limit = $params['limit'];
  } else {
    $limit = 100;
  }
  if(isset($params['orderBy']) && is_string($params['orderBy'])) {
    $orderBy = $params['orderBy'];
  } else {
    $orderBy = "id";
  }

  $valid_params = array(
    ":query" => $query,
    ":orderBy" => $orderBy,
    ":limit" => $limit
  );
  $paramsType = array(
    ":query" => PDO::PARAM_STR,
    ":limit" => PDO::PARAM_INT
  );
  // 複数の値を返す
  return array($valid_params, $paramsType);
}

function whiteOrder($order) {
    // ORDER BY句に入れるためのホワイトリスト
   if (in_array($order, array("id", "name", "detail"))) {
        return $order;
    } else {
        return "id";
    }
}

function makeQuery($dbh, $params, $paramsType) {
  // SQLを組み立てる
  // もととなるSQL
  $sql = "SELECT name, detail FROM product WHERE";
  // あとで付け足すSQL
  // カラム名を指定するには文字列として埋め込む必要がある
  $orderSql = " ORDER BY ".whiteOrder($params[":orderBy"])." LIMIT :limit";

  $query = $params[":query"];
  if(stristr($query, " ")){
    // クエリにスペースを含む時
    $queries = explode(" ", $query);
    $queryCount = count($queries);

    for ($i = 0; $i < $queryCount; $i++) {
      // 最初はANDを付けない
      if($i != 0){
        $sql .= " AND";
      }
      // placeholderに番号を付ける
      $sql .= " name LIKE :query".$i;
    }
  } else {
    // スペースが無い時
    $queryCount = 1;
  }

  // sqlの:queryに単語をbindする
  if ($queryCount == 1) {
    // 単単語クエリ
    $sql = $sql." name LIKE :query".$orderSql;
    $query = '%'.$query.'%';
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':query', $query, $paramsType[":query"]);
  } else {
    // 複単語クエリ
    $sql .= $orderSql;
    $stmt = $dbh->prepare($sql);
    for($i = 0; $i < $queryCount; $i++) {
      // placeholderは:query0, :query1となっている
      $placeholder = ':query'.$i;
      $queries[$i] = '%'.$queries[$i].'%';
      $stmt->bindParam($placeholder, $queries[$i], $paramsType[":query"]);
      /*
      $_query = '%'.$queries[$i].'%';
      $stmt->bindParam($placeholder, $_query, $paramsType[":query"]);
      とすると、実行時の$_queryの参照先が全て$queries[$queryCount-1]になってしまう
      bindValueにすれば上の$_queryを使って問題ない
      */
    }
  }

  // 後ろのlimit句にバインド
  $stmt->bindParam(':limit', $params[":limit"], $paramsType[":limit"]);
  return $stmt;
}

function doSearch($params, $paramsType) {
  // SQLを組み立てて実行
  $dbh = connectDB();
  $stmt = makeQuery($dbh, $params, $paramsType);
  $ret = $stmt->execute();

  // sqlを確認できる
  // $stmt->debugDumpParams();

  if (!$ret) {
    $err = $dbh->errorInfo();
    exit('SELECT 失敗:' . $err[2] . var_dump($params));
  }

  $results = makeResult($stmt);
  // 接続を閉じる
  $dbh = null;
  return $results;
}

function makeResult($stmt) {
  // MySQLから返ってきた値を辞書に格納する
  // 取得結果はカラム名をキーとする辞書になっている
  $names = [];
  while($row = $stmt->fetch()) {
    $names[] = trim($row["name"]);
    $details[] = trim($row["detail"]);
  }
  // 結果が無い場合
  // select count(*)するのが嫌だった
  if ($names === []) {
    return [];
  }
  $results = array(
    'names' => $names,
    'details' => $details
  );
  return $results;
}
?>

感想

PHPあんまり慣れてないし間違ってるかも知れない
コードがあんまり綺麗な分割じゃなくて気持ち悪いけど、とりあえず要件は満たせるはず

型を指定する必要がなければ

$stmt->execute($params)

と出来るが、型を指定するにはいちいちbindParamしないとだめでめんどくさい

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした