PHP: Retrieve image from MySQL using PDO

Question

I am refactoring some old code, including rewriting basic mysql queries to use PDO.

The following works brilliantly in all browsers and for all image types:

$query = 'SELECT image FROM image WHERE imageid=' . $image_id;
$result = mysql_query($query, $db_conn); querycheck($result);
header("Content-type: image");
echo mysql_result($result, 0);

Unfortunately, however I rewrite it using PDO, it doesn't work. I've been through the entire PDO documentation and the standard web search, but none of the advice/solutions work.

How can one easily fetch and image from MySQL using PDO and display it?

Edit 1:

Matthew Ratzloff gives what should be the obvious answer below, but it does not work. Here is the actual code that I test using PDO (and I have tried many variants/parameters):

$connectstring_temp = 'mysql:host=' . $A . ';dbname=' .$B;
$dbh_temp = new PDO($connectstring_temp, $login, $password);
#$dbh_temp->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
#$dbh_temp->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);

$sql = "SELECT image FROM image WHERE imageid=" . $image_id;
$query = $dbh_temp->prepare($sql);
$query->execute();

$query->bindColumn(1, $image, PDO::PARAM_LOB);
$query->fetch(PDO::FETCH_BOUND);
header("Content-Type: image");
echo $image;

I've kept the same syntax, although for the final code $image_id needs to be passed as a parameter. The code above does NOT work. PDO works fine for all other queries of all types.

I posted the answer which I tested it works then u will just modify — Humphrey
– Humphrey, Commented Feb 21, 2013 at 12:00

outis · Accepted Answer · 2012-01-02 19:14:43Z

4

You need to paramaterize the imageid value and bind the parameter to PDO::PARAM_LOB:

$sql = "SELECT image FROM image WHERE imageid=:id";
$query = $db_conn->prepare($sql);
$query->execute(array(':id' => $image_id));

$query->bindColumn(1, $image, PDO::PARAM_LOB);
$query->fetch(PDO::FETCH_BOUND);
header("Content-Type: image");
echo $image;

Of course, you'll also want to specify the complete, correct content type (e.g., image/png).

edited Jan 2, 2012 at 19:14

outis

77.7k23 gold badges155 silver badges228 bronze badges

answered May 14, 2011 at 3:09

Matthew Ratzloff

4,6631 gold badge34 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charles Over a year ago

While this should work, you do not need to use LOB mode for MySQL. It can be necessary with other databases.

Matthew Ratzloff Over a year ago

Never hurts to be explicit. :-)

Svein Tjønndal Over a year ago

This works now. I can only assume it was a problem with earlier verions of PHP/PDO. Wish I could upvote.

Ruby Racer · Accepted Answer · 2013-05-19 22:01:23Z

My personal approach to Image Blobs is using a php file to serve the image, in combination with a GET argument... It's 100% effective and it doesn't involve file creation... For instance, the file to serve the image from the blob would be:

image.php:

<?php
$db = new PDO('mysql:host=localhost;dbname=anything; charset=utf8', 'account','password');
if (isset($_GET['imageid'])) {
    $result = $db->prepare("SELECT image FROM image where imageid = ?");
    if ($result->execute(array($_GET['imageid']))) {
        $row=$result->fetch();
        echo $row['pic']; //this prints the image data, transforming the image.php to an image
        }
    } // you can put an "else" here to do something on error...
?>

This can be called from you main script... For instance:

<?php
$db = new PDO('mysql:host=localhost;dbname=anything; charset=utf8', 'account','password');
//... some code to do your job, where you get your imageid from
$imageid=...
?>
<img src="./image.php?imageid=<?php echo $imageid;?>">

Farzad · Accepted Answer · 2014-03-30 21:09:23Z

0

You can use this code to get image from database using PDO:

public function getImage($id){
    $sql = "SELECT * FROM images WHERE id = ?";
    $sth = $this->dbh->prepare($sql);

    $sth->bindParam(1,$id);
    $sth->execute();


    $num = $sth->rowCount();
    if( $num ){
        $row = $sth->fetch(PDO::FETCH_ASSOC);
        header("Content-type: ".$row['type']);
        print $row['image'];
        exit;
    }else{
        return null;
    }
}

type - data type(column name) such as image/png or image/gif
image - image data(column name) stored in table as LOB
$this->dbh connection handler
It works for me but now I need to find out how to use it with JS because result of this function is passed to JavaScript code - so called ajax

edited Mar 30, 2014 at 21:09

Farzad

8562 gold badges9 silver badges26 bronze badges

answered Jul 13, 2012 at 16:12

Marek Bar

9033 gold badges16 silver badges32 bronze badges

Comments

Humphrey · Accepted Answer · 2013-02-21 11:53:56Z

-2

This code displays all the images in the dabase so you can change it and make it do what you want it to do

getMessage(); } ?>

<?php 
#the folder where the images are saved 
$target = "image_uploads/";
$image_name = (isset($_POST['image_name']));

$query = ("SELECT user_id ,image_name FROM tish_images");
$image_show= $con-> prepare($query);
$image_show->execute();
while($record =$image_show->fetch(PDO::FETCH_ASSOC)) {
#this is the Tannery  operator to replace a pic when an id do not have one
$photo = ($record['image_name']== null)? "me.png":$record['image_name'];
#display image 
echo '<img src="'.$target.$photo.'">';
echo $record['user_id'];
}
?>

answered Feb 21, 2013 at 11:53

Humphrey

2,8273 gold badges30 silver badges39 bronze badges

2 Comments

Your Common Sense Over a year ago

...not to mention it doesn't answer the question at all

Svein Tjønndal Over a year ago

Not relevant to the question.

Collectives™ on Stack Overflow

PHP: Retrieve image from MySQL using PDO

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related