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

Sometimes, we want to store and retrieve images from a MySQL database using PHP.

In this article, we’ll look at how to store and retrieve images from a MySQL database using PHP.

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

To store and retrieve images from a MySQL database using PHP, we can store it as a blob.

For instance, we write

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

to create the testblob table that stores the image field as a blob.

Then we write

$img_data = file_get_contents($filename);
$size = getimagesize($filename);
mysql_connect("localhost", "$username", "$password");
mysql_select_db("$dbname");
$sql = sprintf(
    "INSERT INTO testblob
    (image_type, image, image_size, image_name)
    VALUES
    ('%s', '%s', '%d', '%s')",
    mysql_real_escape_string($size["mime"]),
    mysql_real_escape_string($img_data),
    $size[3],
    mysql_real_escape_string($_FILES["userfile"]["name"])
);
mysql_query($sql);

to insert the $image_data image file as a blob by calling mysql_real_escape_string with $img_data to create a string from it.

Then we use '%d' to interpolate $img_data as an integer.

Then we call mysql_query with $sql to run the $sql statement.

Next, we get the image from the database by writing

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

We call mysql_query with $sql to get the image from the database.

Then we call mysql_result with $result to return the image.

And then we echo it.

We set the Content-type header with header so it’ll be returned as a jpeg image.

Conclusion

To store and retrieve images from a MySQL database using PHP, we can store it as a blob.