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

概要

• 日本語で検索
• 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_INTやPDO::PARAM_STRを指定すれば良い

• SQLでカラム名を指定する場合(FROM句やORDER BY句)

  • sprintfとかで文字列として埋め込む必要がある
  • ホワイトリストを作って対処する
    • 参考：http://blog.a-way-out.net/blog/2013/12/19/sql-injection-prevention/

• $_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;
  

  • 普通にandとorでlike句を繋げたら出来る

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

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

コード

1. productテーブルからwhere句でnameでAND検索をして、limitとorder 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しないとだめでめんどくさい