PHPのPDOプレースホルダーでDATE型にNULLを挿入する方法

MySQLにデータを挿入するとき、DATE型に空データを挿入できません。どうすればいいのか、その解決方法を説明します

DATE型のデータ挿入がうまく動かない

PHPでMySQLにレコードを追加する方法として、プリペアドステートメントを作ってプレースホルダーを使った方法があります。

これはレコード追加の準備でダミーのテキストを使い、それから変数やリテラル値をダミーと置き換えることでデータベースに入力します。

カラムと入力したい値のセットが複数あるときは、この方法がわかりやすくなります。

MySQLの構文と合わせてPHPのプログラムを見てみましょう。

レコード追加構文

INSERT INTO [テーブル名] (列名, ...) VALUES (値, ...)

この構文を使ってレコードを追加します。

これをPHPから実行するプログラムは以下です。

(例)
$dsn = 'mysql:host=ホスト名;dbname=データベース名;charset=utf8';
$user = 'データベスのユーザー名';
$password = 'ユーザーのパスワード';
try {
    $db = new PDO($dsn, $user, $password);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $stmt = $db->prepare(
      "INSERT INTO users (hoge, foo, bar) VALUES (:hoge, :foo, :bar)"
    );
    $stmt->bindParam(':hoge', $hoge, PDO::PARAM_STR);
    $stmt->bindParam(':foo', $foo, PDO::PARAM_STR);
    $stmt->bindParam(':bar', $bar, PDO::PARAM_STR);
    $stmt->execute();
} catch(PDOException $e) {
    echo "エラー:" . $e->getMessage();
}

これはhoge, foo, barというカラムに値をプレースホルダーとして:hoge, :foo, :barを割り当てて、$hoge, $foo, $barの値をbindParam関数で追加しています。

ここではhoge, foo, barとも文字列型のデータを追加しています。

このときそれぞれに空データを入れてもエラーも発生せず、データベースに空データが追加されます。

例えば以下のようにhogeに空データを追加できます。

$hoge = '';
$stmt->bindParam(':hoge', $hoge, PDO::PARAM_STR);

しかし、hogeのデータベース型がDATE型のときは、これを実行してもレコードは追加されません。

DATE型のデータを追加するとき、bindParamで引数はPDO::PARAM_DATEではないかと思いがちですが、実はPDO::PARAM_DATEは存在しません。

DATE型のデータを追加するときもPDO::PARAM_STRを使います。

それではどのようにしてDATE型に空データを追加すればいいのでしょうか。

その方法はDATE型のカラムは空データではなくNULLを扱えるようにすることです。

つまり空データが追加できないのでNULLを追加します。

しかし以下は正常に動作しません。

$hoge = null;
$stmt->bindParam(':hoge', $hoge, PDO::PARAM_STR);

nullを文字列で代入してもだめです。($hoge = ‘null’;もだめ)

bindParam関数は引数に設定する変数が空文字やnullだと正常に動作しません。

bindParam関数だとnullを挿入できないので、bindValue関数を使います。

DATE型へのnull挿入方法

bindParam関数とbindValue関数はどちらも、プレースホルダーに値をバインド(結びつける)します。

しかし2つの関数の違いは、第2引数の扱い方です。

bindParam関数の第2引数は変数のみ設置可能です。

$stmt->bindParam(':hoge', $hoge, PDO::PARAM_STR);

第2引数に値を設置することができません。

(*これはダメ ✕)$stmt->bindParam(':hoge', "sample", PDO::PARAM_STR); 

bindValue関数の第2引数は変数と値の両方が設置可能です。

$stmt->bindValue(':hoge', $hoge, PDO::PARAM_STR);
$stmt->bindValue(':hoge', "sample", PDO::PARAM_STR);

bindParam関数の第2引数は変数の参照渡しとなります。
変数の値を渡しているわけではないので、以下のように処理するときは注意がひつようです。
動作しない例)
foreach ($params as $key => $val) {
    $sth->bindParam($key, $val, PDO::PARAM_STR);
}
$valが上書きされるため思ったように動作しません。
動作する例)
foreach ($params as $key => $val) {
    $sth->bindParam($key, &$val, PDO::PARAM_STR);
}

つまりこの違いから、DATE型にnullを挿入するにはbindValue関数を使えばいいのです。

$stmt->bindValue(':hoge', null, PDO::PARAM_NULL);

注意としては第3引数にPDO::PARAM_NULLを設置することです。

これで目的のカラムにNULLを挿入することができます。

まとめ

bindParam関数が多く使われているのを見かけます。

bindParam関数を使っていてうまく動かないときは、bindValue関数を試してみるのもいいかと思います。