How can I store and retrieve images from a MySQL database using PHP?

Question

How can I insert an image in MySQL and then retrieve it using PHP?

I have limited experience in either area, and I could use a little code to get me started in figuring this out.

Projector

i also recommend thinking this thru and then choosing to store images in your file system rather than the DB .. see here: #4248 – Torpor

Answer 1

First you create a MySQL table to store images, like for example:

create table testblob (
    image_id        tinyint(3)  not null default '0',
    image_type      varchar(25) not null default '',
    image           blob        not null,
    image_size      varchar(25) not null default '',
    image_ctgy      varchar(25) not null default '',
    image_name      varchar(50) not null default ''
);

Then you can write an image to the database like:

/***
 * All of the below MySQL_ commands can be easily
 * translated to MySQLi_ with the additions as commented
 ***/ 
$imgData = file_get_contents($filename);
$size = getimagesize($filename);
mysql_connect("localhost", "$username", "$password");
mysql_select_db ("$dbname");
// mysqli 
// $link = mysqli_connect("localhost", $username, $password,$dbname); 
$sql = sprintf("INSERT INTO testblob
    (image_type, image, image_size, image_name)
    VALUES
    ('%s', '%s', '%d', '%s')",
    /***
     * For all mysqli_ functions below, the syntax is:
     * mysqli_whartever($link, $functionContents); 
     ***/
    mysql_real_escape_string($size['mime']),
    mysql_real_escape_string($imgData),
    $size[3],
    mysql_real_escape_string($_FILES['userfile']['name'])
    );
mysql_query($sql);

You can display an image from the database in a web page with:

$link = mysql_connect("localhost", "username", "password");
mysql_select_db("testblob");
$sql = "SELECT image FROM testblob WHERE image_id=0";
$result = mysql_query("$sql");
header("Content-type: image/jpeg");
echo mysql_result($result, 0);
mysql_close($link);

Charis

If your images are larger than 65K you'll want to change the type of the blob field. #3504341 – Actinometer

For a variety of images, I got encoding and escape sequence errors with the above code. I had to replace $imgData = file_get_contents($filename); with $fp = fopen($filepath, 'r'); $imgData = fread($fp, filesize($filepath)); $imgData = addslashes($imgData); fclose($fp); to get it to work consistently. – Actinometer

The link at the head of the answer is definitely worth looking at as it contains significantly more detail. – Actinometer

Seems like this would fail spectacularly if the image data contained an apostrophe. – Gag

@minitech: Or even the image name... added mysql_real_escape_string now. – Charis

if image type is gif or another type except jpeg then what to do. – Von

Very good code but mysql is outdated. can you update your answer to include mysqli? – Almeta

I downvoted because the best way of doing this is what @Ankid Sharma said: storing image in a server folder. – Windy

@KrLx_roller: Storing an image in a server folder may be better, but it's not what the OP asked. Hence, I believe this question to be "useful." +! – Helmand

Answer 2

Instead of storing images in database store them in a folder in your disk and store their location in your data base.

Lonne

Your making assumptions. Its very common now to have a web/browser based app which you want to function in offline mode, so you need some clever cache mgmt and/or images in the web based database (WebSQL, PouchDB, Couchbase, Mongo, etc). – Biochemistry

This answer purports as a better solution; the OP wants to store the image in a db. – Desegregate

Subjective and nothing to do with the question about how to insert images into a database. – Udele

Answer 3

Beware that serving images from DB is usually much, much much slower than serving them from disk.

You'll be starting a PHP process, opening a DB connection, having the DB read image data from the same disk and RAM for cache as filesystem would, transferring it over few sockets and buffers and then pushing out via PHP, which by default makes it non-cacheable and adds overhead of chunked HTTP encoding.

OTOH modern web servers can serve images with just few optimized kernel calls (memory-mapped file and that memory area passed to TCP stack), so that they don't even copy memory around and there's almost no overhead.

That's a difference between being able to serve 20 or 2000 images in parallel on one machine.

So don't do it unless you absolutely need transactional integrity (and actually even that can be done with just image metadata in DB and filesystem cleanup routines) and know how to improve PHP's handling of HTTP to be suitable for images.

Dermatology

Answer 4

i also recommend thinking this thru and then choosing to store images in your file system rather than the DB .. see here: Storing Images in DB - Yea or Nay?

Torpor

Answer 5

Personally i wouldnt store the image in the database, Instead put it in a folder not accessable from outside, and use the database for keeping track of its location. keeps database size down and you can just include it by using PHP. There would be no way without PHP to access that image then

Trossachs

Answer 6

My opinion is, Instead of storing images directly to the database, It is recommended to store the image location in the database. As we compare both options, Storing images in the database is safe for security purpose. Disadvantage are

1. If database is corrupted, no way to retrieve.

2. Retrieving image files from db is slow when compare to other option.

On the other hand, storing image file location in db will have following advantages.

1. It is easy to retrieve.

2. If more than one images are stored, we can easily retrieve image information.

Kkt

Answer 7

I agree with the basic concept of @Andomar, but would store the actual images on disk to avoid storing large data in SQL. Remember that a large field is 255 chars in SQL. So you'll need to store the images files in Blobs, which work well when you only have a small amount rows (think: hundreds, not millions). The reasoning is complex, but it's good practice. Here's how I know:

I wrote a large project that stores lots (and lots) of images. Always store them on disk to save yourself a SQL headache. If you're worried about a DB corruption leaving you with images you can't "rebuild" into a database, there's an easy fix (besides backing up your SQL table(s) )

Store your images in a directory tree with directory names that make sense for humans and parsing. So storing it by time might look like "/year/month/day/picture.jpg" or by user "/user_id/picture.jpg". Use this as a concept, and just store the path in SQL. This will make your SQL table small and often cacheable and allow your SQL table to be on one server, and your images to be stored on another "server" such as AWS's S3.

If you're really paranoid, you can store a file called "{image_name)_meta.json" in the same directory as the image itself. Fill it with meta data about the image that you normally store in the DB table. If the table corrupts, you can write a a little function that walks through the tree and rebuilds the table from the JSON files. This of course will take up more disk space, but it's pretty cheap (think: AWS's S3).

There are lots of ways to solve this, but storing large amounts of images in any SQL-type table is rarely a good idea. Exceptions would be displaying hundreds at once, or perhaps lighting-quick retrieval times (instead of sub-second).

Good luck with your project!

Doolittle

Answer 8

instead of storing image in the database, store it in your phone and pc and just use its path as it will save your database

Citron

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Vaudevillian