Categories
blob database mysql php

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

66

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.

1

123

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);

9

  • 4

    If your images are larger than 65K you’ll want to change the type of the blob field. stackoverflow.com/questions/3503841/…

    Apr 13, 2013 at 6:09

  • 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.

    Apr 13, 2013 at 6:16

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

    Apr 13, 2013 at 6:26

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

    – Ry-

    Jun 18, 2013 at 13:11

  • 1

    @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.” +!

    – J. Allan

    Sep 5, 2016 at 18:20

24

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

3

  • 1

    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).

    – ekerner

    Feb 22, 2017 at 11:32

  • 1

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

    Apr 6, 2019 at 18:13

  • 1

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

    – DonP

    Jan 10, 2020 at 8:28

12

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.