Display image saved as blob in SQL Server

Question

I have a request for informational 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 image:

 <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> 

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

when I use a query in my DB, it retrieves the data as desired. Plz see below.

Problem: I cannot repeat the above BadgeImage in html.

I saved the image with the following query:

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

This is currently what I see when I try to repeat the image:

Answer 1

The only thing I can find on this problem is that you should get 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 sure that this works because I don’t have the opportunity to test, and I could not find much information on this.

---

An alternative way is to save the image as:

• Base64 encoded text
• Save image path

For any method, you need to change the data type of the BadgeImage column to text for encoded images or varchar if you want to save 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, when printing, this should be as simple as:

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