概要
- 日本語で検索
- 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の組み立てから実行まで
- SQL文を
prepareする - プレースホルダに変数を
bindParamする -
executeする -
while文の中でfetchして一行ずつ取り出す
- SQL文を
-
プレースホルダに
bindParamをする際に型を指定できる- デフォルトだと数値を入れたら文字列にされてしまう
- 第3引数に
PDO::PARAM_INTやPDO::PARAM_STRを指定すれば良い
-
SQLでカラム名を指定する場合(FROM句やORDER BY句)
- sprintfとかで文字列として埋め込む必要がある
- ホワイトリストを作って対処する
-
$_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) = ...;
コード
-
productテーブルからwhere句でnameでAND検索をして、limitとorder byを指定する
流れ
-
$_GETからパラメータを取り出す - パラメータのバリデーション
- AND検索をするために前処理
- もちろん、1単語ならAND検索にはならない
-
PDOにSQLをprepareしてプレースホルダにbindParamしてexecute - 結果の取得
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しないとだめでめんどくさい