概要

• 日本語で検索
• 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する 1. プレースホルダに変数をbindParamする 1. executeする 1. 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;
  `
  
  る
  

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

• 返り値 - 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

<?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

<?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しないとだめでめんどくさい

from: https://qiita.com/petitviolet/items/a3e96a44898bebcb07ce