複数カラム複数ワード検索

前提

MySQLでデータの検索を行う。
前回作成した「なんちゃって個人情報」様の5000件のデータをそのまま流用しました。
- 「名前」「ふりがな」の2カラム同時検索
- スペース区切りでAND検索
- MySQLiを使用
- プレースホルダを使用。

つまり何個キーワードがあるか分からない状態でプレースホルダを使います。
例えば『佐藤　り』で検索した場合は「名前カラムまたはふりがなカラムに”佐藤”と”り”が両方ある」人を出力します。

デモページはこちら

HTMLテンプレート

  <article>
    <section>
      <form method="post" action="">
        <div class="form-group">
          <label for="word">名前またはふりがなを入力してください。</label>
          <input type="text" class="form-control" id="word" name="word" placeholder="名前 ふりがな" value="<?php echo $word_raw; ?>">
          <span class="help-block">スペースを挟み複数ワードを入力するとAND検索を行います</span>
        </div>
        <button type="submit" class="btn btn-default">検索</button>
      </form>
    </section>
    <section>
<?php echo $count_disp; ?>
<?php echo $list_disp; ?>
    </section>
  </article>

検索処理と整形

$test = new Address();

if(isset($_POST['word']))
{
    $word_input = htmlspecialchars($_POST['word'], ENT_QUOTES);
    $list = $test->search($word_input);//検索結果を配列で取得

    //HTML整形
    if(count($list) > 0)
    {
        $count_disp = '<p>全' . count($list) . '件表示中</p>';
        $list_disp  = "<table class=\"table table-striped\">\n<thead>\n<tr>\n";
        $list_disp .= "<th>名前</th>\n<th>ふりがな</th>\n<th>E-Mail</th>\n<th>性別</th>\n<th>年齢</th>\n";
        $list_disp .= "<th>誕生日</th>\n<th>結婚</th>\n<th>血液型</th>\n<th>出身地</th>\n</tr>\n</thead>\n<tbody>\n";
        for($i=0; $i<count($list); $i++)
        {
            $list_disp .= "<tr>\n";
            $list_disp .= "<td>".$list[$i]['name']."</td>\n";
            $list_disp .= "<td>".$list[$i]['kana']."</td>\n";
            $list_disp .= "<td>".$list[$i]['email']."</td>\n";
            $list_disp .= "<td>".$list[$i]['gendar']."</td>\n";
            $list_disp .= "<td>".$list[$i]['age']."</td>\n";
            $list_disp .= "<td>".$list[$i]['birth']."</td>\n";
            $list_disp .= "<td>".$list[$i]['married']."</td>\n";
            $list_disp .= "<td>".$list[$i]['blood']."</td>\n";
            $list_disp .= "<td>".$list[$i]['pref']."</td>\n";
            $list_disp .= "</tr>\n";
        }
        $list_disp .= "</tbody>\n</table>\n";
    }
    else {
        $list_disp = '<p>一致する用語はありません</p>';
    }
}
$word_raw = htmlspecialchars_decode($word_input, ENT_NOQUOTES);

Class

class Address
{

    public function __construct()
    {
        $this->cnn = new mysqli('localhost', 'user', 'pass', 'dbname', 3306);
        $this->cnn->set_charset("utf8");
    }

    public function search($word)
    {

        $word = str_replace('　', ' ', $word);//半角統一
        $pattern = "/(?<!^)[\s ]+(?!$)/";
        $word_array = preg_split($pattern, $word);//2重スペース防止の上配列化

        // SQL生成
        $sql1 =
'SELECT '
    .'name, kana, email, gender, age, birth, married, blood, pref'.
' FROM '
    .'personal_data'.
' WHERE '
    .'id > 0';

        for($i=0; $i<count($word_array); $i++)
        {
            $sql2 .=
' AND '
    .'(name REGEXP ?'.
' OR '
    .'kana REGEXP ?)';

            // bind_param用データ生成
            $sqlParams[0] .= 'ss';
            $sqlParams[] = $word_array[$i];
            $sqlParams[] = $word_array[$i];
        }
        $params = [];
        foreach ($sqlParams as $key => $value)
        {
            $params[$key] = &$sqlParams[$key];
        }

        $sql3 =
' GROUP BY '
    .'id'.
' ORDER BY '
    .'kana';

        $stmt = $this->cnn->prepare($sql1.$sql2.$sql3);
        call_user_func_array(array($stmt, 'bind_param'), $params);
        $stmt->execute();
        $stmt->bind_result($name, $kana, $email, $gendar, $age, $birth, $married, $blood, $pref);
        while($stmt->fetch())
        {
            $result[] = compact('name', 'kana', 'email', 'gendar', 'age', 'birth', 'married', 'blood', 'pref');
        }
        $stmt->close();

        return $result;

    }

}

途中でvar_dump

「佐藤　り」で検索し、クラス内でvar_dumpした結果

var_dump($word_array);

array(2) {
  [0]=>
  string(6) "佐藤"
  [1]=>
  string(3) "り"
}

var_dump($sqlParams);

array(5) {
  [0]=>
  string(4) "ssss"
  [1]=>
  string(6) "佐藤"
  [2]=>
  string(6) "佐藤"
  [3]=>
  string(3) "り"
  [4]=>
  string(3) "り"
}

var_dump($params);

array(5) {
  [0]=>
  &string(4) "ssss"
  [1]=>
  &string(6) "佐藤"
  [2]=>
  &string(6) "佐藤"
  [3]=>
  &string(3) "り"
  [4]=>
  &string(3) "り"
}

var_dump($result);

array(3) {
  [0]=>
  array(9) {
    ["name"]=>
    string(12) "佐藤璃子"
    ["kana"]=>
    string(15) "さとうりこ"
    ["email"]=>
    string(21) "[e-mail]"
    ["gendar"]=>
    string(3) "女"
    ["age"]=>
    string(2) "28"
    ["birth"]=>
    string(10) "1987/11/10"
    ["married"]=>
    string(6) "既婚"
    ["blood"]=>
    string(4) "O型"
    ["pref"]=>
    string(9) "東京都"
  }
  [1]=>
  array(9) {
    ["name"]=>
    string(9) "佐藤涼"
    ["kana"]=>
    string(18) "さとうりょう"
    ["email"]=>
    string(21) "[e-mail]"
    ["gendar"]=>
    string(3) "女"
    ["age"]=>
    string(2) "46"
    ["birth"]=>
    string(8) "1970/3/3"
    ["married"]=>
    string(6) "既婚"
    ["blood"]=>
    string(4) "B型"
    ["pref"]=>
    string(9) "秋田県"
  }
  [2]=>
  array(9) {
    ["name"]=>
    string(9) "佐藤涼"
    ["kana"]=>
    string(18) "さとうりょう"
    ["email"]=>
    string(22) "[e-mail]"
    ["gendar"]=>
    string(3) "女"
    ["age"]=>
    string(2) "20"
    ["birth"]=>
    string(10) "1996/10/12"
    ["married"]=>
    string(6) "既婚"
    ["blood"]=>
    string(4) "O型"
    ["pref"]=>
    string(9) "東京都"
  }
}

念のため

実際にDBに個人情報を出し入れする場合は暗号化復号化してください。

URL

デモページ
MySQL 5.6 リファレンスマニュアル 12.5.2 正規表現
PHP マニュアル preg_split
PHP マニュアル call_user_func_array