anyone? using PHP to display an imagetype BLOB from MSSQL

Discussion in 'Web Design and Development' started by ChoMomma, Nov 27, 2007.

  1. ChoMomma macrumors 6502

    ChoMomma

    #1
    So I have an MSSQL Database that has photos loaded into it as BLOBs, and I'm trying to grab the BLOB and display it in the browser.

    I'm pretty good with PHP and SQL, but for some reason I cannot get this to happen. I either get the 'file' displayed as gibberish inline or nothing at all. :(
     
  2. X1Lightning macrumors 6502

    X1Lightning

    Joined:
    Feb 19, 2007
    #2
    why are you storeing images in the database? just put the images in the file system, and store their location in the db
     
  3. ChoMomma thread starter macrumors 6502

    ChoMomma

    #3
    Yeah.. It's not my doing on the storing of actual files in the DB.. I'm pulling from a commercial app that is written by M$ "professionals." If I had been doing it all it would just be a file path in the DB.. so now you can see why I am not sure how to get this BLOB out correctly.

    I've dealt with large text entries stored in BLOBs before. But not images.
     
  4. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #4
    I've done this before, but with MySQL and PHP.

    It is relatively simple to do. You will need a seperate file for viewing the image, for example viewimage.php. This file needs to do the following:

    connect to the database
    retrieve the relevant data
    output a header content-type of whatever (jpg for example)
    output the actual image data

    This makes the assumption that you:
    • Are using 'old school' php - not zend framework, cakephp etc
    • All images are of the same type (not jpgs and gifs etc)

    This link may be of some use:
    http://www.codewalkers.com/c/a/Database-Articles/Storing-Images-in-Database/3/

    They store the content type for each image also, and use it to output the relevant header.

    If for some reason your image does not work, comment out the header part (to get it in the standard text/html content type). That way you will be able to see any errors should they appear.

    Any problems, post back here.
     
  5. ChoMomma thread starter macrumors 6502

    ChoMomma

    #5
    Well so far I'm getting the same nothingness I was getting before. I've tried commenting out the header info and that didn't change my results.

    I've also tried turning on error reporting in the page to see what it shows. But that doesn't seem to be working.
     
  6. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #6
    So you are actually getting nothing at all? Any chance of posting code (not too long though)...?
     
  7. ChoMomma thread starter macrumors 6502

    ChoMomma

    #7
    Here are two methods I'm trying:

    blob.php
    Code:
    <?php 
    // image.php - by Hermawan Haryanto <hermawan@dmonster.com> 
    // Example PHP Script, demonstrating Storing Image in Database 
    // Detailed Information can be found at http://www.codewalkers.com 
    
    // database connection 
    $conn = mssql_connect("10.10.x.x", "xxxxx", "xxxx") 
      OR DIE (mssql_error()); 
    @mssql_select_db ("database", $conn) OR DIE (mssql_error()); 
    $sql    = "SELECT * FROM Company_Person WHERE PersonID=".$_GET["PersonID"]; 
    $result = mssql_query ($sql, $conn); 
    if (mssql_num_rows ($result)>0) { 
      $row = @mssql_fetch_array ($result); 
      $image_type = $row["image/jpeg"]; 
      $image = $row["Picture"]; 
      Header ("Content-type: $image_type"); 
      echo base64_encode($image); 
      
    } 
    ?>
    
    and image.php

    Code:
    <?php 
    header('Content-type: image/jpeg;');
    $db = mssql_connect("10.10.x.x", "xxxxx", "xxxxx");
    $PersonID = $_GET['PersonID'];
    // Specify database here
    mssql_select_db('Company_Person');
    
    // Build SQL statement here
    $sqlText = 'SELECT Picture FROM Comany_Person WHERE PersonID= ' . $PersonID;
    $sql = mssql_query($sqlText);
    echo mssql_result($sql,0,"Picture");
    mssql_close();
    ?>
    
    
    displayed in individual.php

    Code:
     <td width="28%" height="135" align="center" valign="middle"><img src="image.php?PersonID=<?php echo $DirectoryIndividual->Fields('PersonID'); ?>" width="100" height="100" alt="<?php echo $DirectoryIndividual->Fields('FullName'); ?>"></td>
    
    or
    Code:
     <td width="28%" height="135" align="center" valign="middle"><img src="blob.php?PersonID=<?php echo $DirectoryIndividual->Fields('PersonID'); ?>" width="100" height="100" alt="<?php echo $DirectoryIndividual->Fields('FullName'); ?>"></td>
    
     
  8. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #8
    The image.php version looks okay to me. What output do you actually get? Have you tried putting the address in the browser, with a known good personId value?

    Edit: also once it is working, it will need to be protected form SQL injection attacks!
     
  9. saltyzoo macrumors 65816

    saltyzoo

    Joined:
    Oct 4, 2007
    #9
  10. ChoMomma thread starter macrumors 6502

    ChoMomma

    #10

    No it doesn't work, all I get is a 100x100 box in FF or in IE I get a ? in the regular view (where the image should be) and if I right-click and 'open the image in a new tab or window' I get a blank page. And the ID I'm using is the only one that has an image to show.

    If I right-click and view source on the blank page I get nothing there too.
     
  11. thejadedmonkey macrumors 604

    thejadedmonkey

    Joined:
    May 28, 2005
    Location:
    Pa
    #11
  12. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #12
    Can you take out the 'header' line, and try visiting it directly in the browser again? You should get some kind of textual output...?
     
  13. saltyzoo macrumors 65816

    saltyzoo

    Joined:
    Oct 4, 2007
    #13
    If you can PM me the url I'd like to see exactly what is coming back.
     
  14. Nugget macrumors 65816

    Nugget

    Joined:
    Nov 24, 2002
    Location:
    Houston Texas USA
    #14
    Code:
      $image_type = $row["image/jpeg"]; 
    This line does not make sense to me at all. Is there actually a field in your table named "image/jpeg"? This line of code appears to be trying to set the value of the string to the contents of a field titled "image/jpeg" which I doubt is actually the case.

    Shouldn't this be something like:

    Code:
     $image_type = "image/jpeg"; 
    but adjusted depending on the nature of the image type (which I presume is stored in the database somewhere).

    With this line wrong you're going to be emitting a NULL or malformed content-type header which can lead to exactly the failure you're describing.

    Also, if you've got the content-type header correct you shouldn't need to do the base64 encode.

    Oh, and yeah, to reiterate splashtech's excellent advice -- the code as written is very dangerous. Imagine if someone did...

    Code:
    http://example.com/blob.php?PersonID=1;DROP TABLE Company_Person
     
  15. ChoMomma thread starter macrumors 6502

    ChoMomma

    #15
    clarity

    Okay let me first say that this is for an intranet site so the issue of security is not too much of a concern.

    Second, some things I tried and saw.


    So I tried this code and.. I get back the "Please use a real id number" everytime, even though I know this is the ID of the record. I also verified in Zend that the image blob is populated and shows up.

    Code:
    <?php
        // just so we know it is broken
        error_reporting(E_ALL);
        // some basic sanity checks
        if(isset($_GET['PersonID']) && is_numeric($_GET['PersonID'])) {
            //connect to the db
            $link = mssql_connect("10.10.x.x", "Jxxxxxx", "Jxxxxxx") or die("Could not connect: " . mssql_error());
     
            // select our database
            mssql_select_db("Jxxxxxx") or die(mssql_error());
     
            // get the image from the db
            $sql = "SELECT Picture FROM Jxxxxxx WHERE PersonID=003100";
     
            // the result of the query
            $result = mssql_query("$sql") or die("Invalid query: " . mssql_error());
     
            // set the header for the image
            header("Content-type: image/jpeg");
            echo mssql_result($result, 0);
     
            // close the db link
            mssql_close($link);
        }
        else {
            echo 'Please use a real id number';
        }
    ?>
    
    If I run the code through Zend it gives me the same message at the end... It feels like the ID# is not working, but it should..
     
  16. ChoMomma thread starter macrumors 6502

    ChoMomma

    #16
    Almost there!!

    So I did some more digging and now this is the code I am using:

    Code:
    <?php
        // just so we know it is broken
        error_reporting(E_ALL);
        // some basic sanity checks
        if(isset($_GET['PersonID']) && is_numeric($_GET['PersonID'])) {
            //connect to the db
            $link = mssql_connect("xxxxxxxxxxx", "Jxxxxxx", "Jxxxxxx") or die("Could not connect: " . mssql_error());
     
            // select our database
            mssql_select_db("Jxxxxxxx") or die(mssql_error());
     
            // get the image from the db
            $sql = "SELECT Picture FROM Jxxxxxxx WHERE PersonID= 003100";
     
            // the result of the query
            $result = mssql_query("$sql") or die("Invalid query: " . mssql_error());
     
            // set the header for the image
            header("Content-type: image/jpeg");
            echo mssql_result($result, 0, 0);  // <---added the second 0
     
            // close the db link
            mssql_close($link);
        }
        else {
            echo 'Please use a real id number';
        }
    ?> 
    

    This works! it outputs the persons picture!!!

    Now for some reason it doesn't yet display in the page it should be on.
     
  17. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #17
    You should be able to now change this line:

    Code:
    // get the image from the db
            $sql = "SELECT Picture FROM Jxxxxxxx WHERE PersonID= 003100";
    to pass in the correct person ID value (rather than hard code the person ID in), and assuming your image tag and the calling page do in fact send the correct ID, it should be fine.

    Code:
    // get the image from the db
            $sql = "SELECT Picture FROM Jxxxxxxx WHERE PersonID=" . $_GET['PersonID'];
    HTML:
    <image src="image.php?PersonID=<?php echo $thePerson->id; ?>" alt="" />
    Also, I notice the person Id is 003100. What kind of data type is that (in the db)? If its a varchar or similar, it will need quotes around it ('003100') - and adjust my code sample above accordingly. If its an integer or similar, why the trailing zeros?
     
  18. ChoMomma thread starter macrumors 6502

    ChoMomma

    #18
    "Some days are better than others" - U2


    Well, what was working doesn't want to work now.
    I'm trying to figure out what could have possibly changed...:eek::confused:
     
  19. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #19
    Are you sure all the image data in the database is okay?
     
  20. ChoMomma thread starter macrumors 6502

    ChoMomma

    #20
    I did recheck that in Zend, and the images are still there and can be seen. :confused:
     
  21. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #21
    Strange. I really dont see how it can not work. I never had this sort of problem when I did mine... I'll see if I can dig out the code for you at some point (although it uses MySQL not MSSQL)
     
  22. ChoMomma thread starter macrumors 6502

    ChoMomma

    #22
    Yeah thats pretty much how I feel. I have a feeling its something really M$ stupid.. that I'm overlooking.
     
  23. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #23
    Yeah I think you might be right. I've not used MSSQL through PHP before (only MySQL from PHP and MSSQL from ASP.NET). But from what I've heard, MSSQL from PHP isnt too hot...
     
  24. ChoMomma thread starter macrumors 6502

    ChoMomma

    #24
    Issue Solved!!

    Okay, I took the day off from that issue and let it sleep. Came back to it this morning and had it up and running in 10 minutes :D

    Love how the brain works!

    Anyhow, here is the code here and please everyone who needs it use it! I know I Googled long and hard for a solution this, and all the PHP forums out there did very little to help me. Leave it to the Web Dev forum on a Mac :apple: Rumors website!! LOL!

    Thanks for all the help and advice!!

    Code:
    
    <?php
        // just so we know it is broken
        error_reporting(E_ALL);
        // some basic sanity checks
        if(isset($_GET['PersonID']) && is_numeric($_GET['PersonID'])) {
            //connect to the db
            $link = mssql_connect("ixxxxxxxx:1433", "Jxxxxxx", "Jxxxxxx") or die("Could not connect: " . mssql_error());
     
            // select our database
            mssql_select_db("Jxxxxx") or die(mssql_error());
     
            // get the image from the db
            $sql = "SELECT Picture FROM Jxxxxxx WHERE PersonID = $_GET[PersonID] ";
     
            // the result of the query
            $result = mssql_query("$sql") or die("Invalid query: " . mssql_error());
     
            // set the header for the image
            header("Content-type: image/jpeg");
            echo mssql_result($result, 0, 0);  // <---added the second 0
     
            // close the db link
            mssql_close($link);
        }
        else {
            echo 'Please use a real id number';
        }
    ?>
    

    further info: this script is running now on my clients Mac Mini intranet webserver : )
     
  25. splashtech macrumors regular

    Joined:
    Oct 25, 2007
    #25
    Congrats :D

    What was the problem in the end? I havent compared them properly, but on initial inspection that code doesnt look any different to previous versions...
     

Share This Page