PHP: Retrieve image from MySQL using PDO
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).
Admin
Updated on June 25, 2022Comments
-
Admin almost 2 years
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.
-
Charles almost 13 yearsWhile this should work, you do not need to use LOB mode for MySQL. It can be necessary with other databases.
-
Matthew Ratzloff almost 13 yearsNever hurts to be explicit. :-)
-
Your Common Sense about 11 years...not to mention it doesn't answer the question at all
-
Admin about 11 yearsThis works now. I can only assume it was a problem with earlier verions of PHP/PDO. Wish I could upvote.
-
Admin about 11 yearsNot relevant to the question.