Displaying Image from Mysql with PHP

Question

This is what my table looks like in my database. I'm trying to display an image I stored it's a mimetype (longblob) . When I run the code it gives me a small box with a ? , no error just that box. Does anyone know what the error is and how I can fix it?

Display
+-------+------------+----------+
| Index | Display_ID | Picture  |
+-------+------------+----------+
|     1 |         12 | longblob |
+-------+------------+----------+


<?php
    $mysqli=mysqli_connect('localhost','root','','draftdb');


    if (!$mysqli)
        die("Can't connect to MySQL: ".mysqli_connect_error());

    $imageid= 12;

    $stmt = $mysqli->prepare("SELECT PICTURE FROM display WHERE DISPLAY_ID=$imageid"); 
    $stmt->bind_param("i", $imageid);

    $stmt->execute();
    $stmt->store_result();

    $stmt->bind_result($image);
    $stmt->fetch();

    header("Content-Type: image/jpeg");
    echo $image; 
?>

Lovely to see mysqli being used in new code! No idea what the issue though sorry :) — Dale, Jul 27 '12 at 08:16
check out the answer from this http://stackoverflow.com/questions/907361/show-image-from-blob-mysql — Alexander, Jul 27 '12 at 08:22
storing pictures in database is a bad practice which reduces execution speed. Save file paths instead — Bhuvan Rikka, Jul 27 '12 at 08:23
@BhuvanRikka You are wrong. Storing the picture in the DB is the right way to go. You should read [Bill Karwin's book](http://www.amazon.co.uk/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?s=books&ie=UTF8&qid=1343377617&sr=1-1) to find out why as it is too long an explanation for a comment. — vascowhite, Jul 27 '12 at 08:28
maybe there is more than one picture with specified `DISPLAY_ID`? try to limit your query `SELECT PICTURE FROM display WHERE DISPLAY_ID=$imageid LIMIT 1` — Alexander Larikov, Jul 27 '12 at 08:32
Why do you have two almoust identical questions up? http://stackoverflow.com/questions/11683098/error-retrieving-longblob-from-database — Peon, Jul 27 '12 at 08:38
@vascowhite well,no two minds think alike. i'm not blaming you but i've never seen anyone supporting "storing-images-in-db" concept unless it is for a minor use with limited rows. — Bhuvan Rikka, Jul 27 '12 at 08:38
Pros for storing files in the database: 1)transactional consistency 2)security (assuming you need it and that your database isn't wide open anyway) **Cons:** 1)much larger database files + backups (which can be costly if you are hosting on someone else's storage) 2)much more difficult to debug (you can't say "SELECT doc FROM table" in Management Studio and have Word pop up) — Bhuvan Rikka, Jul 27 '12 at 08:41
storing images as files is an anti-pattern. Remember, this files won't be managed by the database resulting in orphans and a flawed backup (possibly). — vascowhite, Jul 27 '12 at 08:43
3)more difficult to present the documents to users (and allow them to upload) - instead of just presenting a link to a file on the file system, you must build an app that takes the file and stores it in the database, and pulls the file from the database to present it..Check [this](http://stackoverflow.com/questions/10838448/how-to-store-different-document-types) — Bhuvan Rikka, Jul 27 '12 at 08:43
@BhuvanRikka I know that is the common stance, but it is flawed. Files stop you using transactions, they can't be deleted by the database, you can't roll back, they don't obey sql privelages etc. All that results in extra code to manage the files which introduces more chance of bugs. As you say, everybody thinks differently. I would recommend that book though, its a good read and will change the way you approach databases. [It is available here too](http://pragprog.com/book/bksqla/sql-antipatterns) — vascowhite, Jul 27 '12 at 08:49
@vascowhite Boy, you got a point! Yes, we can't alter/delete the images with the file path which we store in the database! May be i should give that book a try . Thanks FYI :) — Bhuvan Rikka, Jul 27 '12 at 08:53
FYI: for files that contain just PHP code, the closing tag ("?>") it's not required by PHP, and omitting it prevents the accidental injection of trailing white space into the response. — Fabio Mora, Jul 27 '12 at 09:01
@DainisAbols - I've reported the other one as a dup. OP, if you rewrite your code, you should append that to an existing question. As it stands, it is likely that respondents will duplicate effort on very similar questions. — halfer, Jul 27 '12 at 09:03

score 1 · Accepted Answer · answered Jul 27 '12 at 08:52

1

This:

$stmt = $mysqli->prepare("SELECT PICTURE FROM display WHERE DISPLAY_ID=$imageid");

Should be:

$stmt = $mysqli->prepare('SELECT PICTURE FROM display WHERE DISPLAY_ID=?');

You were directly embedding the variable in the query instead of actually using the bound variables like you intended to.

answered Jul 27 '12 at 08:52

Ariel

25,995
5
59
69

That fixes the error however it's just outputting a bunch of crazy symbols on the page even when i turn on the content header. Any ideas? – Undermine2k Jul 27 '12 at 09:28
@Undermine2k That's promising. What's at the very top of the page? It could be something is preventing the header from being transmitted. Impossible for me to debug remotely for you though. If it was me I'd use `curl` and `wget` from the command line to get the raw output (including http headers) and see what's going on. Any chance you have a URL I can access? – Ariel Jul 27 '12 at 10:03
http://i.imgur.com/3Cswc.jpg -- that is what it looks like when I edit out the content header. If I keep it in it gives no errors just displays a small block with a question mark in it, and doesn't display the rest of my page either. Unfortunately it's localhost so I don't have a url. – Undermine2k Jul 27 '12 at 10:21
Get rid of the HTML and BODY tags (and make sure you have nothing at the bottom either). Not even blank lines!!! Not at the top and not at the bottom. You are outputting raw binary JPEG data, not html. (And put back the content type header.) And do an end to end test: Upload an image, then download it back and do a file compare (diff) to make sure it's identical. – Ariel Jul 27 '12 at 10:22
I see tons of blank lines in your code. If they are outside the `` they will cause you severe problems. Suggestion: Start with `` right at the start and do not close it *AT ALL* (not even at the end - yes, that's legal). That way the entire file is PHP with no possibility of trailing blank lines. Make sure any included files follow the same rules!! – Ariel Jul 27 '12 at 10:26
That fixed it, thank you so much! Deleted the white-space and HTML tags. One more thing how can i return text based queries on the same page it seems to ignore that since it's using image/jpeg for content type? – Undermine2k Jul 27 '12 at 10:37
Just a final tidy up, you probably don't need the "store_result()". That's used for updating, not retrieving. Minor thing. Glad the rest is working. – Robbie Jul 27 '12 at 10:40
@Undermine2k You can't. You need an html page with the text, and an img tag linking to this jpeg outputter. – Ariel Jul 27 '12 at 10:51

score 0 · Answer 2 · answered Jul 27 '12 at 08:24

0

It's not directly answering this, but typically this isn't how you do this.

Usually you would store the path for the image in the database (maybe as a varchar field) then just load the image as per usual. This has benefits that it's easy, keeps the DB small and more easily versioned, the normal caching rules apply to the images etc.

The downside is that anyone can view the images, which may or may not cause an issue.

If you need to go down the route you have started, start by commenting out header("Content-Type: image/jpeg"); and see what the PHP errors are. This may help.

answered Jul 27 '12 at 08:24

Rich Bradshaw

71,795
44
182
241

Gives me this error:Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement – Undermine2k Jul 27 '12 at 08:32
Ah, `SELECT PICTURE FROM display WHERE DISPLAY_ID=$imageid` should end in `?` rather than `$imageid`. – Rich Bradshaw Jul 27 '12 at 08:53

score 0 · Answer 3 · answered Jul 27 '12 at 10:31

0

It is neccessary to specify a content-length header too:

header("Content-Length: ".strlen($image));
header("Content-Type: image/jpeg");

answered Jul 27 '12 at 10:31

Michael Besteck

2,415
18
10

Displaying Image from Mysql with PHP

3 Answers3

Linked