How to Store Images in MySQL

Today we are going to show you how to store images in a MySQL database.

Some may consider doing this to be a pad practice. It could be considered better to just store images on a file system and store links in the database. There are many good reasons that you may prefer to keep images in a database. These include the following:

No extra file system needed
More portable
More secure
Easy backups
Faster data retrieval
Referential integrity and data validation rules

Create a new test database:

sudo mysql -u root -p
CREATE DATABASE test_company;
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON test_company.* TO 'test_user'@'localhost';
FLUSH PRIVILEGES;
QUIT;

Create a new test table:

mysql -u test_user -p
USE test_company;
CREATE TABLE `products` (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE, product_image BLOB) ENGINE = InnoDB;
QUIT;

NOTE - You might want to use a LONGBLOB instead of a BLOB.

You can load an image into the database from the CLI like this:

insert into image(id,image) values (2,load_file('/home/user1/image1.png'));

Create a basic PHP script to test inserting records:

<?php

define('DB_NAME', 'test_company');
define('DB_USER', 'test_user');
define('DB_PASSWORD', 'PASSWORD');
define('DB_HOST', 'localhost');

$pdo = new PDO("mysql:host=" . DB_HOST . "; dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$product = [
              'product_name' => 'MySQL DATABASES',
              'price' => 15,
              'product_image' => file_get_contents("https://i.imgur.com/UYcHkKD.png" )
              ];

$sql = "INSERT INTO products(product_name, price, product_image) VALUES (:product_name, :price, :product_image)";

$stmt = $pdo->prepare($sql);
$stmt->execute($product);

>

You can create a page like this to query and display records:

<html>
  <title>MySQL Image Test</title>
  <body>

  <?php

  require_once 'config.php';

  $sql = "SELECT * FROM products";
  $stmt = $pdo->prepare($sql);
  $stmt->execute();
  ?>


  <?php
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      echo $row['product_id'] . '</td>';
      echo $row['product_name'] . '</td>';
      echo $row['price'] . '</td>';
      echo '<img src = "data:image/png;base64,' . base64_encode($row['product_image']) . '" width = "50px" height = "50px"/>';
  }
  ?>

  </body>
</html>

References

Store Images in MySQL