PHP's MySQLi extension: Storing and retrieving blobs

Preparing the databasemysql> CREATE TABLE images (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,image MEDIUMBLOB NOT NULL,PRIMARY KEY (id));Query OK, 0 rows affected (0.02 sec)Storing the blob<?php$mysqli=mysqli_connect('localhost','user','password','db');if (!$mysqli)die("Can't connect to MySQL: ".mysqli_connect_error());$stmt = $mysqli->prepare("INSERT INTO images (image) VALUES(?)");$null = NULL;$stmt->bind_param("b", $null);$stmt->send_long_data(0, file_get_contents("osaka.jpg"));$stmt->execute();?>

1. 
   

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';+--------------------+----------+| Variable_name | Value |+--------------------+----------+| max_allowed_packet | 16776192 | +--------------------+----------+1 row in set (0.00 sec)$fp = fopen("osaka.jpg", "r");while (!feof($fp)) { $stmt->send_long_data(0, fread($fp, 16776192));}Retrieving the blob<?php$mysqli=mysqli_connect('localhost','user','password','db');if (!$mysqli)die("Can't connect to MySQL: ".mysqli_connect_error());$id=1; $stmt = $mysqli->prepare("SELECT image FROM images WHERE id=?"); $stmt->bind_param("i", $id);$stmt->execute();$stmt->store_result();$stmt->bind_result($image);$stmt->fetch();header("Content-Type: image/jpeg");echo $image; ?>

mysql> INSERT INTO images (image) VALUES( LOAD_FILE("/home/oswald/osaka.jpg") );mysql> SHOW VARIABLES LIKE 'max_allowed_packet';+--------------------+-------+| Variable_name | Value |+--------------------+-------+| max_allowed_packet | 7168 | +--------------------+-------+1 row in set (0.00 sec)mysql> INSERT INTO images (image) VALUES( LOAD_FILE("/home/oswald/osaka.jpg") );ERROR 1048 (23000): Column 'image' cannot be nullmysql> SET @@max_allowed_packet=16777216;Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'max_allowed_packet';+--------------------+----------+| Variable_name | Value |+--------------------+----------+| max_allowed_packet | 16777216 | +--------------------+----------+1 row in set (0.00 sec)mysql> INSERT INTO images (image) VALUES( LOAD_FILE("/home/oswald/osaka.jpg") );Query OK, 1 row affected (0.03 sec)

There are a lot of tutorial out there describing how to use PHP's classic MySQL extension to store and retrieve blobs. There are also many tutorials how to use PHP's MySQLi extension to use prepared statements to fight SQL injections in your web application. But there are no tutorials about using MySQLi with any blob data at all.

Until today... ;)

Okay, first I need a table to store my blobs. In this example I'll store images in my database because images usually look better in a tutorial than some random raw data.

In general you don't want to store images in a relational database. But that's another discussion for another day.

To make a long story short, here's the code to store a blob using MySQLi:

If you already used MySQLi, most of the above should look familiar to you. I highlighted two pieces of code, which I think are worth looking at:

1. The $null variable is needed, because bind_param() always wants a variable reference for a given parameters. In this case the "b" (as in blob) parameter. So $null is just a dummy, to make the syntax work.
2. In the next step I need to "fill" my blob parameter with the actual data. This is done by send_long_data(). The first parameter of this method indicates which parameter to associate the data with. Parameters are numbered beginning with 0. The second parameter of send_long_data() contains the actual data to be stored.

While using send_long_data(), please make sure that the blob isn't bigger than MySQL's max_allowed_packet:

If your data exceeds max_allowed_packet, you probably don't get any errors returned from send_long_data() or execute(). The saved blob is just corrupt!

Simply raise the value max_allowed_packet to whatever you'll need. If you're not able to change MySQL's configuration, you'll need to send the data in smaller chunks:

Usually the default value of 16M should be a good start.

Getting the blob data out of the database is quite simple and follows the usual way of MySQLi:

Connect to the database, prepare the SQL statement, bind the parameter(s), execute the statement, bind the result to a variable, and fetch the actual data from the database. In this case there is no need to worry about max_allowed_packet. MySQLi will do all the work:

If you want to insert a blob from the command line using MySQL monitor, you can use LOAD_FILE() to fetch the data from a file:

Be aware that also in this case max_allowed_packet limits the amount of data you're able to send to the database: