Getting image from SQL database using PDO

Question

I have searched high and low for many many hours for a week or two if not more on the interent in how to upload images to a database. I know some poeple may say its not recommended but I want to do it for a project that Im working on.

Im using a local server XAMPP

PHP Version 5.6.3 ---- Client API version mysqlnd 5.0.11

and inserting this image and it works

http://ctd-web.fr/blog/wp-content/uploads/2009/06/php.jpg

Heres how I insert the image into the database

 <html>
 <body>
<form action="lastimagetest.php" method="POST" enctype="multipart/form-  data">
    <input type="file" name="image"><input type="submit" name="submit" value="Upload">
</form>

<?php

if(isset($_POST['submit']))
 {
TRY{
$pdo = new PDO('mysql:host=localhost;dbname=database;','root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
}catch(Exception $e){
    echo $e->getMessage();
    die();
    }
var_dump($pdo);

$imageName = ($_FILES["image"]["name"]);
$imageData = (file_get_contents($_FILES["image"]["tmp_name"]));
$imageType = ($_FILES["image"]["type"]);

if(substr($imageType,0,5) == "image")
{
TRY{
$results = $pdo->prepare("INSERT INTO cspposts (name, image,   type)VALUES(?,?,?)");
$results->bindParam(1, $imageName);
$results->bindParam(2, $imageData, PDO::PARAM_LOB);
$results->bindParam(3, $imageType);
$results->execute();
}catch(PDOException $e){
    exit('Database error. Sorry please try again later.');
    }


echo "Working code";
}
else
{
    echo "only images";
}

//print_r($_FILES["image"]);

}

?>
<br>
<img src="showimage.php?id=173">
</body>
</html>

I know id=173 is there and id 173 is in the database

This all works fine and inserts data into the table

The table in phpMyAdmin

id is an int - AUTO_INCREMENT name is varchar(50) image is longblob type is varchar(10)

I then try and get the image back from the database using the code

<?php

TRY{
$pdo = new PDO('mysql:host=localhost;dbname=database;','root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
}catch(Exception $e){
    echo $e->getMessage();
    die();
    }

if(isset($_GET['id']))      
{
$id = ($_GET['id']);    
$query = $pdo->prepare("SELECT * FROM cspposts WHERE id = ?");
$query->bindParam(1, $id)
$query->execute();
    while($row = $query->fetch(PDO::FETCH_ASSOC)){

    echo $row["image"];
    }

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

}
else
{
echo"Error!";
}

?>

the result is just a broken image

please, please i hope someone can help me get the results.

Many thanks

`echo $row['image']`. No echo, no output. You're basically outputting a header and nothing else. — Marc B, Apr 01 '15 at 18:09
^^ That's almost all you should need here. However, you cannot use `mysql_real_escape_string()` with PDO! That's part of an entirely different API. Use `bindParam()` and `?` for `$id` just as you did in the `INSERT` ---> `SELECT * FROM cspposts WHERE id = ? ` — Michael Berkowski, Apr 01 '15 at 18:11
Isn't this what you want for the output? http://php.net/manual/en/function.imagecreatefromstring.php — phpmeh, Apr 01 '15 at 18:40
maybe useful: [PHP: Retrieve image from MySQL using PDO](https://stackoverflow.com/questions/5999466/php-retrieve-image-from-mysql-using-pdo). On the insert I would use: _$results->bindParam(2, $imageData, PDO::PARAM_LOB);_ — Ryan Vincent, Apr 01 '15 at 19:53
Thanks @Ryan Vincent but I have had a good go at using your information to try and get it to work and I just cant extract the data from inside the database :( — Jdobbo, Apr 01 '15 at 21:01
Ok, maybe more information is needed as to what you are trying to do. It does and should work. Would you please edit your question and add your table definition for the table that will hold the image. Also please specify your version of PHP and MYSQL, in your question, so we know what we are working with. If you have a link to a sample image then that will help as well. That way you will know that the provided code works. — Ryan Vincent, Apr 01 '15 at 21:12

Getting image from SQL database using PDO

0 Answers0