PreparedなINSERT文を簡単に作る方法

PHPでWebアプリケーションなどを開発していて、SQL文を発行する際に、セキュア面や利便性などから、ADODBやPDOなどを用いて、Prepared Statementを使うSQLを書くこともあると思います。

その際、特にINSERT文などはカラムの数だけVALUESの中に ? が並ぶことになるかと思います。

-- 例:
INSERT INTO 
`persons` (`id`, `name`, 'age`, `birthday`, `mailaddress`, `phone`, `zipcode`, `address`) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?);

このとき、 ? がひたすら並んでいるだけとなると非常に見づらく、INSERTする情報が多くなってくると、指定したカラム数と VALUESの ? の数が一致せず、

Number of variables doesn’t match number of parameters in prepared statement

といったエラーに遭遇したことが一度はあるかと思います。
特に、プログラムの改修をする際などは、カラム名だけ追加して、うっかり VALUESの ? だけ追加のし忘れなどをしてしまうことなんかも。。。

そこで、Prepared Statementを作る際のINSERT文のSQLを簡単で分かりやすくしたいと思います。

< ?php
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $db = new PDO($dsn, $user, $password);
	
	// INSET対象となるカラム名を指定
	$columns = array(
		'id',
		'name',
		'age',
		'birthday',
		'mailaddress',
		'phone',
		'zipcode',
		'address',
	);
	
	$binds = array(
		1	=> 'anon',
		2	=> 'anon',
		3	=> '25',
		4	=> '1984-05-02',
		5	=> '[email protected]',
		6	=> '03-xxxx-xxxx',
		7	=> 'xxxxxx',
		8	=> 'Tokyo',
	);
	
	$sql = 'INSERT INTO persons '
		 . implode(', ', $columns)
		 . ') VALUES ('
		 . implode(', ', array_fill(0, count($columns), '?'))
		 . ')'
		 ;
	
	/*
	// またはテーブル名やカラム名を明示的にクオートする場合はこちら
	$sql = 'INSERT INTO `persons` '
		 . '`' . implode('`, `', $columns) . '`'
		 . ') VALUES ('
		 . implode(', ', array_fill(0, count($columns), '?'))
		 ;
	*/
	
	$stmt = $db->prepare($sql);
	
	foreach($binds as $key => $value){
		$stmt->bindValue($key, $value);
	}
	
	return $stmt->execute();
	
} catch (Exception $e) {
	error_log('[' . get_class($e) . '] ' . $e->getMessage() . ' in ' . $e->getFile() . ' on line ' . $e->getLine());
}

要点は、 $columnsという配列にカラム名を配列で持たせ、Prepared INSERT文を発行する際に、implode()関数でそのカラム名をカンマ区切りで連結、VALUESの ? は array_fill()関数で、カラム名の配列の値の数だけ ? で埋めた配列を作成し、さらにそれをimplode()関数で連結していく。

というだけです。

バインドの個所は今回手抜きにしてしまいました。
本当はバインドも同じようにもう少し見直せばもっと分かりやすく、簡潔にできると思うのですが、
いい書き方が浮かびませんでした。。。

Related posts:

1. 接続先のサーバが応答するか判別する