I can' display an image from sql database with php. How is it possible to display it and how can I display more pictures at a time?

Question

I'm trying to display a picture from mysql database, but somehow it isn't working. The picture is in jpeg format. I use a simple php code with a database connect file and a source file. The weird thing is that if I want to download the picture directly from the database, it stays as a binary .bin file. Here are my codes:

db_connect.php:

<?php
$dbh = new PDO('mysql:host=localhost;dbname=probe', $root);
?>

source.php:

<?php
include ‘db_connect.php’;
$query = “select * from users”;
$stmt = $con->prepare( $query );t
$stmt->bindParam($_GET['image']);
$stmt->execute();
$num = $stmt->rowCount();
if( $num ){
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    header(“Content-type: image/jpeg”);
    print $row['image'];
    exit;
}else{
    //if no image found with the given id,
    //load/query your default image here
}
?>

And in the index file I just call the source.php to display the picture:

<img src="../reg/source.php" >

This is the SQL source:

CREATE TABLE IF NOT EXISTS `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `image` blob NOT NULL,
      `image_type` varchar(100) NOT NULL,
      `image_size` varchar(100) NOT NULL,
      `image_name` varchar(100) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `name` (`name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=44 ;

it's unclear whether those curly quotes `‘ ’` and `“ ”` are part of your actual code or not, including the "t" in `;t` — Funk Forty Niner, Aug 30 '15 at 15:26
It's my mistake. I should've been look it trough more carefully and post the correctly coded version. I'm sorry! — Adam.Biczo, Aug 30 '15 at 15:33
After doing a bit more digging, you're also missing something `WHERE id = ?` to be inserted in `select * from users` as an example because you'd not binding anything. You may have found that code here https://www.codeofaninja.com/2011/02/how-to-resize-and-print-image-file-from.html - here is another answer http://stackoverflow.com/a/22352477/ with an example. — Funk Forty Niner, Aug 30 '15 at 16:25

Funk Forty Niner · Accepted Answer · 2015-08-30T16:18:52.633

1

There are a few issues here and I'm unsure if those curly quotes as I stated in comments are part of your code or not, including the "t" in );t <= delete that "t" if it's part of your code.

If so, ‘ ’ and “ ” need to be changed to ' and " respectively.

However, your connection is using $dbh, and you're using $con in your query.

$dbh = new PDO('mysql:host=localhost;dbname=probe', $root);
^^^^                                                ^^^^^ unknown

and the query:

$stmt = $con->prepare( $query ); // minus the "t" of course.
        ^^^^

That would cause your query to fail right there.

However, $root is unknown and not posted.

Check for errors:

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

Also add $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); right after the connection is opened.

Either $dbh or $con, depending on which variable you are using.

edited Aug 30 '15 at 16:18

answered Aug 30 '15 at 15:33

Funk Forty Niner

74,450
15
68
141

This was helpful, because I've made some banal mistakes, however I've received such a "broken image" file: http://www.supertintin.com/blog/wp-content/uploads/2011/02/120px-Crystal_Clear_filesystem_file_broken.png – Adam.Biczo Aug 30 '15 at 15:42
@Adam.Biczo might be a path issue. Add error reporting to the top of your file(s) right after your opening PHP tag for example ` – Funk Forty Niner Aug 30 '15 at 15:43
@Adam.Biczo also, if your `blob` column is too small to hold larger images, you will need to use `LONGBLOB`. There could be numerous reasons as to why it's failing you. – Funk Forty Niner Aug 30 '15 at 15:49
@Adam.Biczo This line `$stmt->bindParam($_GET['image']);` modify it to read as `$stmt->bindParam($_GET['image']), PDO::PARAM_LOB;` try that. Plus, your GET method might be failing as to how you're retrieving that, which is unknown to us/me. Based on an example I found, being `$query->bindColumn(1, $image, PDO::PARAM_LOB);` maybe even `$query->fetch(PDO::FETCH_BOUND);` pulled from http://stackoverflow.com/a/5999568/ --- I.e. `$row = $stmt->fetch(PDO::FETCH_BOUND);` suggestion. – Funk Forty Niner Aug 30 '15 at 15:54
@Adam.Biczo and lastly, because that's all I can think of, is to remove `$num = $stmt->rowCount();` and the conditional statement for it. I am out of options. – Funk Forty Niner Aug 30 '15 at 16:07
I've changed the orders like you suggested and also given a method="get" into img src="", but it displays still a broken image. Now I'm trying the error reporting via php. – Adam.Biczo Aug 30 '15 at 16:08
You will need to post your form and how you are retrieving the images. See the example pages https://www.codeofaninja.com/2011/02/how-to-resize-and-print-image-file-from.html and under **Displaying all the information** in http://www.phpro.org/tutorials/Storing-Images-in-MySQL-with-PHP.html @Adam.Biczo – Funk Forty Niner Aug 30 '15 at 16:14

score 0 · Answer 2 · edited Aug 30 '15 at 16:28

This is how I do upload the picture in the database. You were absolutely right, because I used the code from this site: http://www.phpro.org/tutorials/Storing-Images-in-MySQL-with-PHP.html. But I did some changes in the code.

     <?php
        if(!isset($_FILES['userfile']))
            {
            echo '<p>Please select a file</p>';
            }
        else
            {
            try    {
                upload();

                echo '<p>Thank you for submitting</p>';
                }
            catch(Exception $e)
                {
                echo '<h4>'.$e->getMessage().'</h4>';
                }
            }
        ?>

        <?php 

        function upload(){

        if(is_uploaded_file($_FILES['userfile']['tmp_name']) && getimagesize($_FILES['userfile']['tmp_name']) != false)
            {
            $size = getimagesize($_FILES['userfile']['tmp_name']);
            $type = $size['mime'];
            $imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');
            $size = $size[3];
            $name = $_FILES['userfile']['name'];
            $maxsize = 100000;
             if($_FILES['userfile']['size'] < $maxsize )
                {
                $dbh = new PDO("mysql:host=localhost;dbname=probe", 'root', '');
     $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $stmt = $dbh->prepare("UPDATE users SET image='$imgfp', image_type='$type', image_size='$size', image_name='$name' ");
      $stmt->execute();
                }
            else
                {

                throw new Exception("File Size Error");
                }
            }
        else
            {

            throw new Exception("Unsupported Image Format!");
            }
        }
        ?>

This is how I retrieve the picture.

<div class="row">
            <div class="col-md-3 col-sm-3">

                <form
                    img src="../reg/source.php" method="get" ></form></div></div>

Yep, I wrote that. Why? Has is any mistakes? Because I still couldn't fiugre out, what the right solotion might be. — Adam.Biczo, Aug 30 '15 at 16:44

I can' display an image from sql database with php. How is it possible to display it and how can I display more pictures at a time?

2 Answers2