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関数を試してみるのもいいかと思います。