MySQL – PDO inserting BLOB’s and reading


[adsense id=”0514458240″ width=”468″ height=”60″]

You should use either mysqli or PDO to connect to the database when using PHP 5.5 or higher. The mysql set of functions have been deprecated from this version (). Alternatively you can use mysqli instead of PDO. An identical example using mysqli is found by clicking the link.

The biggest advantage of using PDO is that by changing the DATASOURCE from mysql to postgresql this will work in equally as well without changing any lines of code. CARE must be taken that you use standard SQL and not proprietary statements to make this work across multiple databases.

This paper provides the code needed to both write binary content to a MySQL table and to read the same data back. In our example, we will create two scripts, one that reads the contents of a PDF file and adds this into a table. The second script will read the contents of the same record in MySQL and output it to a file. We will then show that the two files are identical.

The table that we created is as follows:

CREATE TABLE testblob(
  id INT AUTO_INCREMENT ,
  data MEDIUMBLOB,
  PRIMARY KEY ( id )
) ENGINE = InnoDB;

The first script reads the contents of a PDF file called testimage.jpg.

<?php
	define('DBUSERNAME','user');
	define('DBPASSWORD','');
	define('DBHOST','localhost');
	define('DBNAME','test');
	define('DATASOURCE','mysql');

	$conn = NULL;

	// Enable Database connection
	function getConnectionString() {
		$options = array();
		if(DBHOST != "") {
			$options[] = "host=" . DBHOST;
		}
		if(DBNAME != "") {
			$options[] = "dbname=" . DBNAME;
		}
		return implode(";", $options);
	}

	try {
		$conn = new PDO(DATASOURCE . ":" . getConnectionString(), DBUSERNAME, DBPASSWORD);
		$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	} catch(PDOException $e) {
		// If we get an exception then we need to terminate
		die('ERROR: ' . $e->getMessage() . "\n");
	}

	$data = file_get_contents("testimage.jpg");

	// Now let's insert an entry into the database
	try {
		$sql = "INSERT INTO testblob(data) values (:data)";
		$stmt = $conn->prepare($sql);
		$stmt->execute(array("data" => $data));
	} catch (PDOException $e) {
		die('ERROR: ' . $e->getMessage() . "\n");
	}
?>

At this point the binary has been added into the database. We now need a second script which will read this content and write it back out to a separate file:

<?php
	define('DBUSERNAME','user');
	define('DBPASSWORD','');
	define('DBHOST','localhost');
	define('DBNAME','test');
	define('DATASOURCE','mysql');

	$conn = NULL;

	// Enable Database connection
	function getConnectionString() {
		$options = array();
		if(DBHOST != "") {
			$options[] = "host=" . DBHOST;
		}
		if(DBNAME != "") {
			$options[] = "dbname=" . DBNAME;
		}
		return implode(";", $options);
	}

	try {
		$conn = new PDO(DATASOURCE . ":" . getConnectionString(), DBUSERNAME, DBPASSWORD);
		$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	} catch(PDOException $e) {
		// If we get an exception then we need to terminate
		die('ERROR: ' . $e->getMessage() . "\n");
	}

	$sql = "SELECT data FROM testblob WHERE id = 1";
	$stmt = $conn->prepare($sql);
	$stmt->execute();
	while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
		$data = $row['data'];
	}
	$stmt->closeCursor();
	file_put_contents("testblobpdo.jpg", $data);
?>

To see this in action:

$ mysql -D test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.6.17 openSUSE package

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(1) from testblob;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

$ php writeblob.php 
$ mysql -D test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.6.17 openSUSE package

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(1) from testblob;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

$ php readblob.php 


$ ls -ltr
-rw------- 1 rcashell users 229943 Jan 19 12:57 testimage.jpg
-rw-r--r-- 1 rcashell users 229943 Jan 19 16:19 testblobpdo.jpg

[googleplus]

,