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`する
	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検索

    ```sql
    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`からパラメータを取り出す
1. パラメータのバリデーション
1. AND検索をするために前処理
	- もちろん、1単語ならAND検索にはならない
1. `PDO`にSQLを`prepare`してプレースホルダに`bindParam`して`execute`
1. 結果の取得


### function.php

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

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

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

```php
$stmt->execute($params)
```
と出来るが、型を指定するにはいちいち`bindParam`しないとだめでめんどくさい