Cannot Display image from Sql Server using PHP PDO using Varbinary Max

Question

I have been racking my brain trying to figure out why an image will not display using Php PDO with it stored in Sql Server 2016 as a Varbinary Max field. I use Php 7, and have this working on Php 5 using the same code, but with MySql. I want to move to Sql Server instead.

When I display it all I get is a broken image, but in the source code it shows the image data. It is is encoded using base64, and I use a while loop to fetch the records. My code is below.

The insert which works fine and I can view the image in the database.

$sql1 = "INSERT INTO weather_stories (filedate, text, imgfile)
                VALUES (:filedate, :text, :imgfile)";
    $preparedStatement = $conn->prepare($sql1);
    $preparedStatement->bindParam(':filedate', $_POST['filedate']);
    $preparedStatement->bindParam(':text', $_POST['text']);
    $preparedStatement->bindParam(':imgfile', $output, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
    $preparedStatement->execute();

Displaying image:

$sql = "SELECT TOP (2) id, filedate, imgfile, text, adddatetime, CONCAT(ROUND(DATALENGTH(imgfile)/1024, 1), 'k') as size
            FROM weather_stories
            ORDER BY adddatetime DESC";
  // use exec() because no results are returned
  $preparedStatement = $conn->prepare($sql);

  $preparedStatement->execute();

    //retrieve records
    while ($row = $preparedStatement->fetch())  {

        //create table
        echo "<tr>";
        echo "<td>" . $row['filedate'] . "</td>";
        echo "<td>" . $row['text'] . "</td>";
        echo "<td>" . $row['adddatetime'] . "</td>";
        echo "<td>" . $row['size'] . "</td>";
        echo "</tr>";
        echo "<tr>";
        echo "<td><a href='viewws.php?id=" . $row['id'] . "'>";
        echo "<img width='300' height='300' src='data:image/png;base64," . base64_encode($row['imgfile'])  . "' />";

        echo "</a></td>";
        echo "<td><a href='#' id=" . $row['id'] . " class='deletews'>Delete</a></td>";
        echo "</tr>";
    }

I do not get why this doesnt work in an img tag. All it displays is the broken image icon but when you look at the source code of the page it shows the data after the data: tag in the image.

I have looked all over for a solution to this even on here and nothing works. I suspect it maybe with the fetch but dont know why, as there is limited documentation on the web about this.

Help would be appreciated. Thanks!

Answer 1

I figured it out by just playing with some ideas Ive found,

echo '<img src="data:image/png;base64,' . base64_encode(hex2bin($row['imgfile']))  . '" >';

The image had to be hex2bin 1st then base64 encoded and now it displays the image correctly. I thought I had tried this before and it didnt work, unless I missed something, but I wish there was an easier way to it.

Also header() method doesnt work it gives a black screen and image cannot be displayed cause of errors on firefox. I have tried that before and same problem. I have no idea on why it works on some and not here.