Solved

Store and show Image ms sql

Posted on 2013-12-06
7
563 Views
Last Modified: 2013-12-16
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. ???
0
Comment
Question by:team2005
  • 4
  • 3
7 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39701227
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
 
LVL 2

Author Comment

by:team2005
ID: 39712756
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39712904
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Author Comment

by:team2005
ID: 39713374
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39714052
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
 
LVL 2

Author Closing Comment

by:team2005
ID: 39721515
thanks
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39721538
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article discusses four methods for overlaying images in a container on a web page
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now