Displaying images from a mysql database

Question

i am relatively new to php. I have a problem with displaying images in my browser ( google chrome) after retrieving blob data from mysql database.

Basically the following code works when the slashes are added in front of the echo at the bottom. However i have followed other online tutorials and the tutor has been able to display their images without the use of slashes whilst i am unable to get the image up. I just wondered what the standard rule is? Another thing to add - when i do fail to get the images up in the browser i get instead a ting thumbnail. I would really appreciate if anybody could tell me how to reliably display images. The site i wish to create is just about images. So its kind of fundamental. Thanks a lot in advance for your time.

<?php
$conn = mysql_connect ("localhost","root","arctic123");
$db = mysql_select_db ("user_images", $conn);

if(!$db) {
    echo mysql_error();
}

$q = "SELECT * FROM images";
$r = mysql_query ("$q",$conn);
if($r) {
    while($row = mysql_fetch_array($r)) {
        //echo $row ['username'];
        //echo "<br>";
        header ("Content-type: image/jpeg");
        echo $row ['logo'];
        //echo "<br>";
    }
}else{
    echo mysql_error();
}
?>

For the love of God, don't use `mysql_*`. Those functions are deprecated and you should really be using `mysqli` at a minimum... — BenM, Nov 26 '12 at 23:12
Possible duplicates: http://stackoverflow.com/questions/5932249/show-a-blob-image-php-mysql-along-with-other-data and http://stackoverflow.com/questions/5525830/displaying-an-image-stored-in-a-mysql-blob; Also, besides switching to mysqli, you should probably set up a non-root user for your DB as well. — ernie, Nov 26 '12 at 23:14
If your site is like "all about images" then why store them in a database? Is there any reason for that? I don't get what "the use of slashes" means. I don't see any slashes but those indicating comments. — m02ph3u5, Nov 26 '12 at 23:17
Since you're new to php I assume you're new to web programming. Try to avoid storing images in a database. Just use the file system. I have encountered only a couple instances where storing images in a database made any sense. — Kai Qing, Nov 26 '12 at 23:23
possible duplicates of [How to read images from MySQL database using PHP?](http://stackoverflow.com/questions/4770834/how-to-read-images-from-mysql-database-using-php) and [How can I display images stored in a MySQL database?](http://stackoverflow.com/questions/5125650/how-can-i-display-images-stored-in-a-mysql-database) — Jocelyn, Nov 26 '12 at 23:39
In the tutorial the slashes you refer to as comments were necessary to get the image up else an error displayed in the tutors browser. I wish for images to be uploaded to the database because i wish to sort them according to popularity. Can you order if they were to be stored in a file system or directory? — user1849962, Nov 26 '12 at 23:46
Take a step back - the suggestion to keep your images on the filesystem is good, but that doesn't mean you won't use the database. You'll keep a table in your database that has the image path, and probably a score, and maybe some other attributes. So, instead of sorting the images on the filesystem, you'll use the score in the database to sort them, and then load the images. — ernie, Nov 27 '12 at 09:07

score 2 · Answer 1 · answered Nov 26 '12 at 23:20

2

You can't have header after any output in your code: http://php.net/manual/en/function.header.php

Best practice is to upload images to a directory and just store the image's path/file name in the database. Also makes it easier to manipulate the image, e.g. create different sizes and thumbnails with PHP. And it takes about four times less the disk space...

answered Nov 26 '12 at 23:20

tobiv

821
1
8
20

Can images be sorted in a directory? I had presumed this was impossible. I don't mean manually. But so that functions can do it automatically. – user1849962 Nov 26 '12 at 23:48
You won't need to sort the images, if I understand you correctly. You can order by the reference, or any other field, in your database. – tobiv Nov 27 '12 at 00:53

score 0 · Answer 2 · answered Nov 26 '12 at 23:22

0

Hope you are not storing your images on MySQL, please if you do, desist from that detrimental act because it is going to make your database unnecessarily heavy...

answered Nov 26 '12 at 23:22

Emmy Steven

93
6

That is a blanket statement be carefull. I have had blobs stored in a database with a user base of over 10 million and 250 000+ concurrent connections and had no problems. It depends on your physical architecture i.e. disk layout etc. – Namphibian Nov 27 '12 at 05:30

score 0 · Answer 3 · answered Nov 27 '12 at 00:34

I would recommend not storing your images in a database. While it is technically possible, it has serious performance concerns. Best practice would be to designate a folder for the images, then access them directly. If you'd like the filtering and sorting ablities of MySQL, then replace the BLOB column that currently stores the image data with a VARCHAR containing the file name.

<?php
$conn = mysql_connect ("localhost","root","arctic123");
$db = mysql_select_db ("user_images", $conn);
$imgdir = "/path/to/image/directory/";

if(!$db) {
    echo mysql_error();
}

$q = "SELECT * FROM images";
$r = mysql_query ("$q",$conn);
if($r) {
    while($row = mysql_fetch_array($r)) {
        echo $row['username'];
        echo "<br>";
        echo "<img src='" . $imgdir . $row['logo'] . "' />";
        echo "<br>";
    }
}else{
    echo mysql_error();
}
?>

Displaying images from a mysql database

3 Answers3