PHP+MySQL SQL文を組み立てる際のテクニックを集めてみた

はじめに

PHPでSQL文を記述する際のちょっとしたテクニックになります。

生のSQLを記述する機会は減ってきてはいるものの、過去のソースを修正したり、
ちょっとしたツールを作ったりするときに、顔をのぞかせてくる事があるかとおもいます。

データ取得の改善

横持ちのデータは縦持ちに変換してから使う

横持ちのデータは縦持ちに変換したほうが、完結に記述できる場合があります。

オリジナル

## データ取得
$SQL="SELECT column_1, column_2, column_3, column_4";
$pdo = new PDO('mysql:hest=localhost;dbname=test;charset=utf8mb4', 'root', '');
$results = $pdo->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
## データ加工
foreach ($results as $row) { 
  aaa ($row['column_1']);
  bbb ($row['column_1']);

  aaa ($row['column_2']);
  bbb ($row['column_2']);
   ：  
}

まれにfor文で頑張っているコードを見かけます。サンプルコード並の単純なコードであれば問題ないのですが、
複数の横持ちデータを取得したりすると、とたんに複雑なコードになりがちです。

良く見かける改善策

## データ取得
$SQL="SELECT column_1, column_2, column_3, column_4";
$pdo = new PDO('mysql:hest=localhost;dbname=test;charset=utf8mb4', 'root', '');
$results = $pdo->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
## データ加工
foreach ($results as $row) { 
  for (i=1;i<=4;i++) {
    aaa ($row["column_${i}"]);
    bbb ($row["column_${i}"]);
  }
}

そのため横持ちのデータを発見した場合、
SQLで縦持ちに変換してから使うと複雑さが回避できるかを検討してみましょう。

## データ取得
$SQL= <<< EOL
          SELECT 'column_1' AS col_name, column_1 AS col_data
UNION ALL SELECT 'column_2' AS col_name, column_2 AS col_data
UNION ALL SELECT 'column_3' AS col_name, column_3 AS col_data
UNION ALL SELECT 'column_4' AS col_name, column_4 AS col_data
EOL;
$pdo = new PDO('mysql:hest=localhost;dbname=test;charset=utf8mb4', 'root', '');
$results = $pdo->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
## データ加工
foreach ($results as $row) { 
  aaa ($row['column_data']);
  bbb ($row['column_data']);
}

データの変換をSQLで行う

例えばNULLデータを 0もしくは空欄として扱う場合、SQLで変換したほうが簡潔に書ける場合があります。

phpで変換

$SQL ="SELECT item_id, SUM(amount) AS amount FROM xxxx GROUP BY tem_id"
$pdo = new PDO('mysql:hest=localhost;dbname=test;charset=utf8mb4', 'root', '');
$results = $pdo->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) { 
  echo $row['amount'] ?? 0;
}

SQLで変換

$SQL ="SELECT item_id, IFNULL(SUM(amount), 0) FROM xxxx GROUP BY tem_id"
$pdo = new PDO('mysql:hest=localhost;dbname=test;charset=utf8mb4', 'root', '');
$results = $pdo->query($SQL)->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) { 
  echo $row['amount'];
}

サンプルは単純なので余り差は感じませんが、amount が何カ所にも出てくる場合を考えると、
データの発生元となるSQLで変換したほうが漏れがありません。

where句の改善

条件「1 = 1」を追加する

SQLの条件句を組み立てる時、だいたい以下のような考慮が必要となります。

・WHERE句内の条件文が空となる場合
・各条件前にANDを付けるかの判定

そのままをPHPのコードで表すと以下のようになります。

# ユーザテーブルを ユーザID もしくはユーザ名で検索する
$SQL  = "SELECT user_id, user_name FROM user ";

if (!is_null($user_id) || !is_null($user_name) ) {
  $SQL .= "WHERE "
}
if (!is_null($user_id)) {
  $SQL .= " user_id = ${user_id} "
}
if (!is_null($user_name)) {
  if (!is_null($user_id)) $SQL .= " user_name = :user_name "
  else $SQL .= " AND user_name = :user_name "
}

これを、WHERE の後ろに条件「1=1」を追加することで、WHERE句が空になる時とANDが必要か不要かの判断が不要になるため、
以下のように簡潔にかけるようになります。

$SQL  = "SELECT user_id, user_name FROM user ";
$SQL .= "WHERE 1=1 "
if (!is_null($user_id)) $SQL .= " AND user_id = :user_id"
if (!is_null($user_name)) $SQL .= " AND user_name = :user_name"

IFNULL関数の活用

そもそもPHPではなくSQLでNULLの判定を行う方法となります。
PHPのIFを使う場合に比べてSQL文が途切れないため読みやすく感じます。

$SQL = <<< EOL
SELECT
  user_id
 ,user_name
FROM
  user
WHERE
  user_id = IFNULL(:user_id ,user_id)
  user_name = IFNULL(:user_name ,user_id)
EOL;