PHP + MSSQL, displaying image file from db

@BengaltgrsJul 27.2009

I have an image file saved in a MSSQL DB and I’m trying to display it using PHP. I’ve googled around for a few days now and have found plenty of other threads on this topic, but none of them seem to be working for me. The datatype of the column holding the data itself is of type “image”.

I have the it working in ASP fine with this code:

[CODE] // rs is the Result Set of the query attachment = rs(“data”).GetChunk(10485760) Response.ContentType = rs(“type_”) // ‘image/jpg’ Response.BinaryWrite attachment [/CODE]

I’m attempting to do the same thing in PHP with this code, but it doesn’t seem to be working:

[CODE] $type = $rs->Fields[‘type_’]->Value; $data = $rs->Fields[‘data’]->GetChunk(10485760); header(“Content-Type: $type”); // ‘image/jpg’ echo $data; [/CODE]

I’ve tried probably 30+ combinations of base64_encode/decode/imagejpg()/etc.. and still can’t get this.

to post a comment

PHP

10 Comments(s) _↴

@HHCOwnerJul 27.2009 — #Well as long as the images in the db are in url form, you could use this ...

[code=php]
 <? 
 
 include "../connect.php"; 
 
 $sql="SELECT * FROM whatever WHERE whatever='whatever' limit 1"; 
 $result=mysql_query($sql,$conn); 
 
 $data=mysql_fetch_assoc($result); 
 
 if (mysql_num_rows($result) > 0 ) { 
 $imgurl = {$data['NAME OF COLUMN 1']}; 
 } else { 
 echo "Sorry, We couldn't find any pictures."; 
 } 
 
 ?> 
 [/code]

... and whenever you wanted to place the image on the page you could use something like this ...

[code=html]
 <img src="<? echo $imgurl; ?>" />
 [/code]

Of course you could change this around to meet your needs.

Hope this helps. ?

@criterion9Jul 27.2009 — #@HHCOwner

I'm pretty sure the "image" type in MSSQL is binary and not a string pointing to a URI.

@OP

What object (class) are you using to retrieve your result set? Are you getting any errors?

@BengaltgrsauthorJul 27.2009 — #@HHCOwner

Yeah, the image data type is just a binary data type as criterion9 said.

@criterion9

I'm using the ADODB class for all DB connection stuff. I don't get any errors at all. If I remove the content-type line and just print out the straight $data variable it just looks like this: "?????AA??"

@criterion9Jul 27.2009 — #How are you inserting the image into the db? I found some info on storing image data types using php:

If you'd like to store binary data, such as an image, in MSSQL, it's common to have problems with addslashes and co.

This is because the MSSQL parser makes a clear distinction between binary an character constants. You can therefore not easilly insert binary data with "column = '$data'" syntax like in MySQL and others.

The MSSQL documentation states that binary constants should be represented by their unquoted hexadecimal byte-string. That is.. to set the binary column "col" to contain the bytes 0x12, 0x65 and 0x35 you shold do "col = 0x126535" in you query.

I've successfully stored and retrieved jpeg images in a column with the "image" datatype. Here's how:

// storing a file

$datastring = file_get_contents("img.jpg");

$data = unpack("H*hex", $datastring);

mssql_query("insert into images (name, data)

values ('img.jpg', 0x".$data['hex'].")");

// retrieving

$result = mssql_query("select data from images where name = 'img.jpg'");

$row = mssql_fetch_assoc($result);

header("Content-type: image/jpeg;");

echo $row['data'];

As you can see there is nothing to do with the image on they way out, just blurb out the buffer your recieve as with any other field type.
[/quote]

http://us.php.net/manual/en/function.mssql-query.php

@BengaltgrsauthorJul 28.2009 — #Yeah I've found a ton of examples that say to use this method to display (doesn't seem to be working for me):

header("Content-type: image/jpeg;");

echo $row['data'];
[/QUOTE]

But to answer your question, the insert query looks like this:

[CODE]INSERT INTO attachment (type_, data) SELECT 'image/jpeg' AS type_, * FROM OPENROWSET(BULK N'c:/attachments/somejpg.jpg', SINGLE_BLOB) AS data[/CODE]

I normally would just use the file system and skip the DB part altogether, but I'm re-writing some old code for an existing app, so it has to be backward compatible (which means handling files which exist in the DB and not in the filesystem).

@criterion9Jul 28.2009 — #Are you inserting using PHP or ASP? In PHP you'll need to unpack and add hex characters when you insert the image or it won't come out properly.

@BengaltgrsauthorJul 28.2009 — #The insert is being done with PHP. The ASP has no problem reading the data and displaying the image so I think the insert is ok, I'm pretty sure the only issue is displaying in PHP.

@hollow44Dec 02.2009 — #and have you resolved PHP display? cause I have the same issue... Insert is OK cause ASP shows pics well but PHP cant...

@hollow44Dec 02.2009 — #FOUND IT!

My MSSQL database is like this:

Name varchar(50)

Img image

So... you upload file like this:

[code=php]
  $link = mssql_connect('HOLLOW-PC', 'admin', 'password');
 mssql_query("SET ANSI_NULLS ON; SET ANSI_WARNINGS ON;");
 /*
 mssql_query ("SET TEXTSIZE 65536;");
 ini_set ( 'mssql.textlimit' , '65536' );
 ini_set ( 'mssql.textsize' , '65536' );
 */
 if(!$link) {
 die('Something went wrong while connecting to MSSQL');
 }
 
   $zapytanie = "INSERT INTO Photos2 (Name, Img) 
   SELECT '$nazwa', * FROM 
   OPENROWSET(BULK N'C:Image1.jpg', SINGLE_BLOB) rs";
   $wynik = mssql_query ($zapytanie) or die ("blad w zapytaniu mysq");
 
   mssql_close($link);
 [/code]

And you retrive the image like this (photo.php):

[code=php]
  $link = mssql_connect('HOLLOW-PC', 'admin', 'password');
 mssql_query("SET ANSI_NULLS ON; SET ANSI_WARNINGS ON;");
 if(!$link) {
 die('Something went wrong while connecting to MSSQL');
 }
 
  $zapytanie = "SELECT TOP 1 Name, Img 
   FROM Photos2
   ";
  $wynik = mssql_query ($zapytanie) or die ("blad w zapytaniu mysq");
 
  $rekord = mssql_fetch_array($wynik);
 
  $obrazek = $rekord[1];
 
  $obrazek = str_replace('', "", $obrazek);
  $obrazek = str_replace('''', "'", $obrazek);
 
  echo $obrazek;[/code]

These two lines are the most important:

[code=php]
  $obrazek = str_replace('', "", $obrazek);
 $obrazek = str_replace('''', "'", $obrazek);
 [/code]

...because they let PHP to retrive properly MSSQL image datatype (varbinary is similar I think).

@hollow44Dec 03.2009 — #HMM...

problem still exists because on images sometimes you can see wrong pixels...

don't know yet how to resolve this ....

when displaying in bytes there are and most of them you have to replece like in my solution but some you have to leave... don't know how to characterize them...

Also in #PHP _↴

Pls Help with this Parse Error...beginning PHP5 help please with PHP

Success!

Help @Bengaltgrs spread the word by sharing this article on Twitter...

Tweet This

PHP + MSSQL, displaying image file from db

10 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

PHP + MSSQL, displaying image file from db

10 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

10 Comments(s) _↴

Also in #PHP _↴