www.webdeveloper.com
Results 1 to 11 of 11

Thread: PHP + MSSQL, displaying image file from db

  1. #1
    Join Date
    Aug 2006
    Posts
    23

    PHP + MSSQL, displaying image file from db

    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
    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;
    I've tried probably 30+ combinations of base64_encode/decode/imagejpg()/etc.. and still can't get this.

  2. #2
    Join Date
    Apr 2008
    Posts
    102
    Well as long as the images in the db are in url form, you could use this ...

    PHP Code:
    <? 

    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) > ) { 
    $imgurl = {$data['NAME OF COLUMN 1']}; 
    } else { 
    echo 
    "Sorry, We couldn't find any pictures."


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

    HTML Code:
    <img src="<? echo $imgurl; ?>" />
    Of course you could change this around to meet your needs.

    Hope this helps.

  3. #3
    Join Date
    Jan 2009
    Posts
    3,346
    @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?

  4. #4
    Join Date
    Aug 2006
    Posts
    23
    @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??"

  5. #5
    Join Date
    Jan 2009
    Posts
    3,346
    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.
    http://us.php.net/manual/en/function.mssql-query.php

  6. #6
    Join Date
    Aug 2006
    Posts
    23
    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'];
    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
    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).

  7. #7
    Join Date
    Jan 2009
    Posts
    3,346
    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.

  8. #8
    Join Date
    Aug 2006
    Posts
    23
    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.

  9. #9
    Join Date
    Dec 2009
    Posts
    3
    and have you resolved PHP display? cause I have the same issue... Insert is OK cause ASP shows pics well but PHP cant...

  10. #10
    Join Date
    Dec 2009
    Posts
    3
    FOUND IT!

    My MSSQL database is like this:
    Name varchar(50)
    Img image

    So... you upload file like this:

    PHP Code:
            $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); 
    And you retrive the image like this (photo.php):

    PHP Code:
        $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('\0'"\0"$obrazek);
        
    $obrazek str_replace('\'\''"'"$obrazek);

        echo 
    $obrazek
    These two lines are the most important:
    PHP Code:
        $obrazek str_replace('\0'"\0"$obrazek);
        
    $obrazek str_replace('\'\''"'"$obrazek); 
    ...because they let PHP to retrive properly MSSQL image datatype (varbinary is similar I think).

  11. #11
    Join Date
    Dec 2009
    Posts
    3
    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 \0 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...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles