Store and show Image ms sql

Hi!

Have a table :

CREATE TABLE [dbo].[ImageTable] (
    [ImageID]       BIGINT        IDENTITY (1, 1) NOT NULL,
    [TransactionID] BIGINT        NOT NULL,
    [LocationID]    BIGINT        NOT NULL,
    [UserID]        BIGINT        NOT NULL,
    [Organisation]  BIGINT        NOT NULL,
    [CreatedDate]   DATETIME      NULL,
    [UpdatedDate]   DATETIME      NULL,
    [TransImage]    VARCHAR (MAX) NULL
);

Open in new window


Have mad this stored procedure to store
Base64 string to Image table -> TransImage have the stored image

CREATE PROCEDURE [dbo].[P_INSERT_ImageTrans]
@UserID BIGINT, @LocationID BIGINT, @TransactionID BIGINT, @Image varchar(max)
AS

Declare @Organisation bigint
Declare @dagensdato nvarchar(30)

exec GET_Todaysdatetime @dagensdato=@dagensdato output

set @Organisation = 1

exec dbo.GET_Active_Userorganisation @UserID=1,@Organisation=@Organisation output

INSERT INTO dbo.ImageTable
    (TransImage, TransactionID, LocationID, UserID, Organisation, CreatedDate , UpdatedDate)
VALUES
    (@Image,
	 @TransactionID,
	 @LocationID, 
	 @UserID,
	 @Organisation,
	 @dagensdato,
	 @dagensdato
	 )

Open in new window


When picture is stored in the database

The TransImage field in the database contains now Bind64 string


In php i use this code to display picture

<?php

  $sql = "SELECT * FROM {$table6} WHERE ImageID=9";
  $resultraptmp = sqlsrv_query($conn,$sql); 

  if( $resultraptmp === false )
   {
       echo "Error in executing statement 7.\n";
       die( print_r( sqlsrv_errors(), true));
   }

    while($row3 = sqlsrv_fetch_array($resultraptmp)){

       $bilde = $row3['TransImage'];
    ?>
      <tr>
        <td  class="rapport1_del3" valign="top"><?php echo base64_encode($bilde); ?></td>
      </tr>    



   <?php } ?>    

Open in new window


But it only display characters and not the picture


What am i doing wrong. ???
LVL 2
team2005Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ray PaseurConnect With a Mentor Commented:
PHP base64_encode() is appropriate for encoding an image (or any binary string) for safe transport over interfaces that might not be binary safe, such as data base queries, HTTP requests, etc.  It makes the string bigger, but removes any of the risky characters like NUL, etc.  It is reversible with base64_decode() which can restore the original binary data.

One possible design would be to take the raw image file and use base64_encode() before putting it into the data base, then use base64_decode() after retrieving the image file from the data base.  If you want to send an image to the browser as part of an HTML document, you will need to deal with the image header(), so you will need to learn all about this:
http://www.faqs.org/rfcs/rfc2616.html

A more sensible design would put the image file into the server file system and put the URL of the image file into the data base.  There are a host of reasons for not putting an image into the data base, among them usability, performance and backup.
0
 
team2005Author Commented:
Hi!

My Image is stored in the database as varchar(max)

And tryed this code to display pictures, but no picture is shown ?
What is wrong ?
<?php
   error_reporting(E_ALL);
   ini_set("display_errors", 1);

require_once('RAY_EE_config.php');
require_once('Connect_databse.php');
$table6 = 'ImageTable';

  $sql = "SELECT * FROM {$table6}";
  $resultraptmp = sqlsrv_query($conn,$sql); 
?>

<table>
   <tr>
<?php


  if( $resultraptmp === false )
   {
       echo "Error in executing statement 7.\n";
       die( print_r( sqlsrv_errors(), true));
   }
    $y=0;
    while($row3 = sqlsrv_fetch_array($resultraptmp)){
       
    
       $bilde = $row3['TransImage'];


       
    ?>
        <td valign="top" class="rapport_image1"><img src='data:image/jpg;base64,'<?php echo base64_encode($bilde);?> height="160" align="left"></td>

   <?php } ?>
bilde
   </tr>
   </table>    

Open in new window

0
 
Ray PaseurCommented:
Typically pictures are stored in the server file system, and just the URL is stored in the data base.  Then you can use the URL to construct the HTML <img> tag.  This is the way browsers, databases, and HTML work together to render online documents with images.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
team2005Author Commented:
Hi!

I dont have a file, i have a stored base64 string in database.

My question is how to get the base64 picture shown, using php code ?
0
 
Ray PaseurCommented:
It will always be easier to ride the horse in the direction he is going, and the image horse does not want to ride in the database!  It is possible to drive nails with a wrench, but it's not the right tool, and you will never see a carpenter using a wrench to drive nails.  The data base is the wrong tool for image storage.  The file system is the right tool.  You want to get the image out of the data base and store it in the file system.  That is the only professional solution.  Here is what I recommend:

1. Read the base64 string out of the data base by using a SELECT query.
2. Decode the base64 string into the original image string with base64_decode().
3. Write the image string into the server file system with file_put_contents().
4. Generate the HTML <img> tag with a src= attribute pointing to the location in the file system.

Once you have done that, you have an image file in a place that can be useful.
0
 
team2005Author Commented:
thanks
0
 
Ray PaseurCommented:
Thanks for the points and thanks for using EE, ~Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.