Display an image stored as blob in SQL Server

Question

I have a query to fetch info images :

$q4 = "SELECT TOP 3 b.BadgeName, b.BadgeImage FROM BadgeImageTable AS b
INNER JOIN employee_badge AS e
ON e.badge_id = b.BadgeID
WHERE e.employee_id = 2164
ORDER BY e.earned_on DESC ";

$stmt3=sqlsrv_query($conn,$q4);
if($stmt3==false){
  echo 'error to retrieve info !! <br/>';
  die(print_r(sqlsrv_errors(),TRUE));
}

HTML previous:

<!-- 
<span class="fa-stack fa-5x has-badge" >
  <div class="badgesize">
    <img src="images/1.png"  alt=""   >
  </div>
</span> 
-->

Now in the above HTML i am trying to echo the images :

<span class="fa-stack fa-5x has-badge" >

                    <div class="badgesize">

                <img src="

                <?php

                                if($count = sqlsrv_num_rows($stmt3) > 0){
                                  while($recentBadge = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)){
                                    $result[] = $recentBadge;
                                  }

                                  if($count > 3){
                                    $result = array_rand($result, 3);
                                  }

                                  foreach($result as $recentBadge){
                                    echo
                                      $recentBadge['BadgeName'],
                                      '<img src="data:image/png;base64,'.base64_encode($recentBadge['BadgeImage']).'"/>',
                                      '<br>'
                                    ;
                                  }
                                } else {
                                  echo 'no results';
                                }

              ?>

                            "  alt=""  >


                            </div>


        </span>


</div>
</span>

BadgeImage,BadgeID and BadgeName are column names in the "BadgeImageTable" from where I am trying to echo.

when I use the query in my DB its fetching the data as desired. PLz,see below.

Problem : I am unable to echo the above BadgeImage in the html.

I stored the image with the below query :

Insert Into BadgeImageTable(BadgeID, BadgeImage)
Select '77', BulkColumn 
from Openrowset (Bulk 'C:\Users\mrashidjne\Desktop\diligent.png', Single_Blob) as Image

currently this Is what i see when I try to echo the image :

Answer 1

The only thing I could find about this issue is that you should fetch the image as binary:

while(sqlsrv_fetch($stmt3)){
  $result[] = [
    'name' => sqlsrv_get_field($stmt3, 0),
    'img'  => sqlsrv_get_field($stmt3, 1, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY))
  ];
}

With this you should be able to display the image:

function blob2image($mime, $blob){ 
  ob_start(); 
  header("Content-type:$mime"); 
  echo $blob; 
  return ob_get_contents(); 
} 

$mime = 'image/png';
foreach($result as $row){
    echo $row['name'];
    echo '<img src="data:'.$mime.';base64,'.base64_encode(blob2image($mime, $row['img'])).'"/>';
}

I can't say for certain this works because I do not have the ability to test and I could not find much information on this subject as well.

---

An alternative way is to store the image as:

1. Base64 encoded text
2. Store the path of the image

Either method requires to change the column data-type of BadgeImage to either text for encoded images or varchar if you want to store the path.

$file = 'C:\Users\mrashidjne\Desktop\diligent.png';
#$file = 'images/1.png'; // or store path relative to webroot and echo image as normally.
$mime = mime_content_type($fn);
$img  = base64_encode(file_get_contents($fn));
$src  = "$data:$mime;base64,$img"

$sql = "INSERT INTO BadgeImageTable (BadgeName, BadgeImage) VALUES ('diligent', '$src')";

Now while printing, it should be as easy as:

echo '<img src="'.$row['BadgeImage'].'"/>';