Display Images From a SQL Server and MySQL Database Using PHP and PDO

I’ll show here how I displayed images stored in a database, in a blob column. There are a few variation between the code of each database. I divided the article in three steps. We’ll use PDO with PHP 5.3 and later. It can work with prior versions but I did not try. I describe a method to show images and play sounds. Most of the time, you will want to make a page showImage.php?id=25 or a playSound.php?id=414 and call those pages in HTML tag img and audio.

Step 1 : Connection

The options I use:

1
2
3

$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);

Let’s describe those:

• Setting ATTR_MODE to Exception allows you to handle errors easily. When I write a new request, I use setAttribute to set the error mode to warning.
• The FETCH_ASSOC is explained in the documentation: it returns an array indexed by column name as returned in your result set. With SQL Server, I can’t get back columns with accents in the name: I have to use aliases ortherwise the array returned by PDO will have some strange named columns, with black question marks.
• Set Names UTF-8: SET NAMES indicates what character set the client will use to send SQL statements to the server (source). Of course, it only works with MySQL.

Mysql:

1

$pdo = new \PDO("mysql:host=" . BDD_DSN . ";dbname=" . BDD_NAME, BDD_USER, BDD_PASS, $options);

SQL Server:

This might not work, because you need a specific driver to apply the solution I’m using. You have several ways to know if you are eligible. The driver is not enabled in WAMP but it looks like it is included, you should only have to enable it and restart your server.

• create a file that contains <?php phpinfo(); and search for one of these lines.

• create a file with the following content:

<?php var_dump(PDO::getAvailableDrivers());

I hope you use XDebug to have nice var_dump. If you have ‘sqlsrv’ in the array, then we can continue.

For example:

array(4) { [0]=> string(5) "mysql" [1]=> string(4) "odbc" [2]=> string(6) "sqlite" [3]=> string(6) "sqlsrv" }

Finally, the connection with SQL Server

1

new PDO('sqlsrv:Server='.BDD_DSN.';Database='.BDD_NAME, BDD_USER, BDD_PASS, $options);

Step 2: Writing the request

I could keep the same request for both MySQL and SQL Server. I did it this way:

1
2
3
4
5
6
7
8
9
10
11
12

$requestImg = $pdo->prepare('SELECT Picture FROM Table WHERE PrimaryKey = :id');
      $requestImg->bindParam(':id', $idImage, PDO::PARAM_INT);
      try {
          $requestImg->execute();
          $requestImg->bindColumn(1, $myImage, PDO::PARAM_LOB);
          $reponse = $requestImg->fetch(PDO::FETCH_BOUND);
          if ($reponse !== false && $myImage != null) {
              $imageExistsInDB = true;
          }
      } catch (PDOException $e) {

      }

If you want to get the size of the data (image, sound) you are collecting, there is the LENGTH function in Mysql and DATALENGTH for SQL Server ( SELECT Picture, LENGTH(Picture) FROM ...)

The size can be useful for large files you want your users to download. It’s nice to specify the size of the file in the headers so that a progress bar can be displayed in the browser. Be careful: blob usually are heavy. Write your request with a Where clause on the primary key of the table, so that you’re getting only one result.

Step 3: send the data

1. Send the headers with the php function headers. Some fun here: what kind of images do you have in your database? My images started with 0xFF, it rang a bell, I knew it was jpeg images. Try a google search.

2. • Mysql : echo $myImage;
   • SQL Server
     • Prior to PHP 5.4 echo pack("H*" , $image);
     • PHP 5.4 and higher echo hex2bin($image);

Bonus

The full code, in a gist

Later in the week

the header to play mp3

header('Content-Type: audio/mpeg');

print a default image in the case the image isn’t found in the database.

1
2
3
4
5
6
7

if ($imageExistsInDB) {
  //headers here
    echo $myImage; // or the pack or the hex2bin
} else {
  //headers here
    echo file_get_contents('path/to/image/default.png');
}

Take care of headers (jpeg in your database, png for your default image…).

Useful documentation:

• http://www.php.net/manual/en/function.hex2bin.php
• http://www.php.net/manual/en/pdo.lobs.php
• http://us1.php.net/manual/en/function.pack.php

Note: It took me quite a while to figure out how to solve this problem, along with the issues on accents. Be nice enough to add in your code a link to this article :)