Storing images into the Database

phpでデータベースにバイナリデータ(画像・ファイル)を保存する方法。
$binaryはfile_get_contents()などで取得したバイナリデータ。

・MySQL

$query = "INSERT INTO test(bindata) VALUES('%s')";
$query = sprintf($query, mysql_real_escape_string($binary, $conn));
mysql_query($query, $conn);

・MySQLi

$query = "INSERT INTO test(bindata) VALUES('%s')";
$query = sprintf($query, mysqli_real_escape_string($conn, $binary));
mysqli_query($conn, $query);

・PostgreSQL

$query = "INSERT INTO test(bindata) VALUES('%s')";
$query = sprintf($query, pg_escape_bytea($conn, $binary));
pg_query($conn, $query);

・OCI8

$query = "INSERT INTO test(bindata) VALUES(EMPTY_BLOB()) RETURNING bindata INTO :bindata";
$stmt = oci_parse($conn, $query);
$lob = oci_new_descripter($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ":bindata", $lob, -1, SQLT_BLOB);
$res = oci_execute($stmt, OCI_DEFAULT);
$lob->save($binary);
oci_commit($conn);

・SQL Server
デフォルトでは4KBを超えるクエリは制限されているので、php.iniのmssql.textlimitやmssql.textsizeのサイズを大きくする必要がある。

$query = "INSERT INTO test(bindata) VALUES(%s)";
$query = sprintf($query, "0x" . bin2hex($binary));
mssql_query($query, $conn);

・Firebird/Interbase

$query  = "INSERT INTO test(bindata) VALUES(?)";
$blobId = ibase_blob_create();
ibase_blob_add($blobId, $binary);
$blob = ibase_blob_close($blobId);
ibase_query($conn, $query, $blob);

・PDO_MYSQL/PDO_PGSQL/PDO_SQLITE

$query = "INSERT INTO test(bindata) VALUES(:bindata)";
$stmt = $pdo->prepare($query);
$stmt->bindValue(":bindata", $binary, PDO::PARAM_LOB);
$pdo->beginTransaction();
$stmt->execute();
$pdo->commit();

・PDO_OCI
他のPDO拡張のようにバイナリデータを直接渡せないので注意。

$query = "INSERT INTO test(bindata) VALUES(EMPTY_BLOB()) RETURNING bindata INTO :bindata";
$stmt = $pdo->prepare($query);
$tmpfile = file_put_contents($tmpfilePath, $binary);
$fp = fopen($tmpfile, "rb");
$stmt->bindValue(":bindata", $fp, PDO::PARAM_LOB);
$pdo->beginTransaction();
$stmt->execute();
$pdo->commit();
fclose($fp);
unlink($tmpfilePath);

PDO_MYSQL::lastInsertId()

トランザクション内(begin〜commit)でINSERTされたデータの連番を取得する場合、commitの前にlastInsertId()をコールする必要がある。そうしないと"0"が返される。

CREATE TABLE test
(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(24) NOT NULL
)

$pdo = new PDO("mysql:host=localhost;dbname=testdb", "root", "");
$stmt = $pdo->prepare("INSERT INTO test(name) VALUES(:name)");
$stmt->bindValue(":name", "somename");
$pdo->beginTransaction();
$pdo->execute();
$pdo->lastInsertId();  // last id
$pdo->commit();

$pdo->lastInsertId();  // "0"

commitの後でも、LAST_INSERT_ID()関数なら取得できる。

$stmt = $pdo->prepare("SELECT LAST_INSERT_ID() AS id");
$stmt->execute();
$row = $stmt->fetch(PDO_::FETCH_ASSOC);
$row["id"]  // last id

PDO_OCI CLOB

PDO_OCIではCLOBやBLOBカラムのデータがリソースで返される。
データ(文字列)の取得は以下のようにする。

$contents = stream_get_contents($row["body"]);

oracle(oci8)関数でフェッチする場合はデータ(文字列)で返されるため、このようなことをやる必要はない。