Использование типа данных MySQL BLOB для хранения изображений с PHP в Ubuntu 18.04

Для выполнения этого обучающего модуля вам потребуется следующее:

• Сервер Ubuntu 18.04, настроенный в соответствии с указаниями руководства Начальная настройка сервера Ubuntu 18.04 и пользователь без прав root с привилегиями sudo.
• Apache, MySQL и PHP, установленные в соответствии с указаниями руководства «Установка комплекта Linux, Apache, MySQL, PHP (LAMP) в Ubuntu 18.04». Для этого обучающего модуля необязательно создавать виртуальные хосты, поэтому вы можете пропустить шаг 4.

Для начала создайте тестовую базу данных для своего проекта. Для этого подключитесь к серверу через SSH и запустите следующую команду для входа на сервер MySQL с привилегиями root:

sudo mysql -u root -p

Введите пароль пользователя root для вашей базы данных MySQL и нажмите ENTER, чтобы продолжить.

Затем запустите следующую команду для создания базы данных. В этом обучающем модуле мы назовем базу данных test_company:

CREATE DATABASE test_company;

После создания базы данных вы увидите следующее:

Output
Query OK, 1 row affected (0.01 sec)

Затем создайте учетную запись test_user на сервере MySQL и обязательно замените PASSWORD на надежный пароль:

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

Вывод должен выглядеть так:

Output
Query OK, 0 rows affected (0.01 sec)

Чтобы предоставить пользователю test_user полные права доступа к базе данных test_company, выполните команду:

GRANT ALL PRIVILEGES ON test_company.* TO 'test_user'@'localhost';

Вы должны увидеть следующее:

Output
Query OK, 0 rows affected (0.01 sec)

Очистите таблицу прав доступа, чтобы СУБД MySQL перезагрузила разрешения:

FLUSH PRIVILEGES;

На экран должно быть выведено следующее:

Output
Query OK, 0 rows affected (0.01 sec)

Мы подготовили базу данных test_company и пользователя test_user. Теперь мы можем перейти к созданию таблицы products для сохранения образцов продуктов. Позднее мы используем эту таблицу для вставки и получения записей, чтобы продемонстрировать работу объектов MySQL BLOB.

Выполните выход из сервера MySQL:

QUIT;

Снова войдите в систему под учетными данными созданного пользователя test_user:

mysql -u test_user -p

Введите в диалоге пароль пользователя test_user и нажмите ENTER, чтобы продолжить. Переключитесь на базу данных test_company с помощью следующей команды:

USE test_company;

После выбора базы данных test_company MySQL выведет на экран следующее:

Output
Database changed

Затем создайте таблицу products, запустив команду:

CREATE TABLE `products` (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE, product_image BLOB) ENGINE = InnoDB;

Эта команда создает таблицу с именем products. В таблице имеется четыре столбца:

• product_id: в этом столбце используется тип данных BIGINT, за счет чего обеспечивается поддержка больших списков продуктов, где может содержаться до 2⁶³-1 элементов. Вы пометили столбец как PRIMARY KEY для уникальной идентификации продуктов. Чтобы MySQL обрабатывала генерирование новых идентификаторов для вставленных столбцов, вы использовали ключевое слово AUTO_INCREMENT.

• product_name: в этом столбце хранятся названия продуктов. Мы использовали тип данных VARCHAR, поскольку это поле обычно обрабатывает не более 50 бувенно-числовых символов. Предел в 50 символов — это гипотетическое значение, используемое для целей этого обучающего модуля.

• price: для демонстрационных целей мы добавили в таблицу products столбец price для хранения информации о розничной цене продуктов. Поскольку некоторые продукты могут иметь значения с плавающей запятой (например, 23.69, 45.36, 102.99), мы использовали тип данных DOUBLE.

• product_image: в этом столбце данные типа BLOB используются для хранения двоичных данных изображений продуктов.

Мы использовали для таблицы InnoDB, СИСТЕМУ ХРАНЕНИЯ, поддерживающую широкий набор функций, включая транзакции MySQL. После выполнения этой команды для создания таблицы products вы увидите на экране следующее:

Output
Query OK, 0 rows affected (0.03 sec)

Выполните выход с сервера MySQL:

QUIT;

Вы увидите следующее

Output
Bye

Теперь таблица products готова для хранения записей, в том числе изображений продуктов, и на следующем шаге мы заполним ее продуктами.

На этом шаге мы создадим скрипт PHP для подключения к базе данных MySQL, созданной нами на шаге 1. Этот скрипт подготовит три образца продуктов и вставит их в таблицу products.

Для создания кода PHP откройте новый файл в текстовом редакторе:

sudo nano /var/www/html/config.php

Введите в файл следующую информацию и замените PASSWORD паролем пользователя test_user, созданным на шаге 1:

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

Сохраните и закройте файл.

В этом файле мы использовали четыре константы PHP для подключения к базе данных MySQL, созданной на шаге 1:

• DB_NAME : эта константа хранит название базы данных test_company.

• DB_USER : эта переменная хранит имя пользователя test_user.

• DB_PASSWORD : эта константа хранит ПАРОЛЬ MySQL для учетной записи test_user.

• DB_HOST: сервер, где располагается база данных. В данном случае мы используем сервер localhost.

Следующая строка в вашем файле инициирует объект данных PHP (PDO) и выполняет подключение к базе данных MySQL:

...
$pdo = new PDO("mysql:host=" . DB_HOST . "; dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
...

Ближе к концу файла мы зададим пару атрибутов PDO:

• ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: этот атрибут предписывает PDO выдать исключение, которое можно зарегистрировать для целей отладки.
• ATTR_EMULATE_PREPARES, false: эта опция повышает безопасности, предписывая СУБД MySQL выполнять подготовку вместо PDO.

Мы добавим файл /var/www/html/config.php в два скрипта PHP для вставки и получения записей, которые мы сейчас создадим.

Вначале создайте скрипт PHP /var/www/html/insert_products.php для вставки записей в таблицу products:

sudo nano /var/www/html/insert_products.php

Затем добавьте следующую информацию в файл /var/www/html/insert_products.php:

<?php

require_once 'config.php';

$products = [];

$products[] = [
              'product_name' => 'VIRTUAL SERVERS',
              'price' => 5,
              'product_image' => file_get_contents("https://i.imgur.com/VEIKbp0.png")
              ];

$products[] = [
              'product_name' => 'MANAGED KUBERNETES',
              'price' => 30,
              'product_image' => file_get_contents("https://i.imgur.com/cCc9Gw9.png")
              ];

$products[] = [
              '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)";

foreach ($products as $product) {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($product);
}

echo "Records inserted successfully";

Сохраните и закройте файл.

Вы добавили файл config.php в верхней части файла. Это первый файл, который вы создали для определения переменных базы данных и подключения к базе данных. Также этот файл инициирует объект PDO и сохраняет его в переменной $pdo.

Далее вы создали массив данных о продуктах, которые нужно вставить в базу данных. Помимо значений product_name и price, подготовленных в виде текстовых и числовых значений соответственно, данный скрипт использует встроенную функцию PHP file_get_contents для чтения изображений из внешнего источника и передачи их в виде строк в столбец product_image.

Затем вы подготовили выражение SQL и использовали выражение PHP foreach{...} для вставки каждого продукта в базу данных.

Для выполнения файла /var/www/html/insert_products.php запустите его в окне браузера, используя следующий URL. Замените your-server-IP публичным IP-адресом вашего сервера:

http://your-server-IP/insert_products.php

После выполнения файла вы получите в браузере сообщение, подтверждающее успешную вставку записей в базу данных.

Вы успешно вставили три записи с изображениями продуктов в таблицу products. На следующем шаге мы создадим скрипт PHP для извлечения этих записей и их вывода в браузере.

Мы разместили в базе данных информацию о продуктах и их ихображения. Теперь мы создадим еще один скрипт PHP, который будет запрашивать и выводить информацию о продуктах в таблице HTML в браузере.

Для создания файла введите следующее:

sudo nano /var/www/html/display_products.php

Затем введите в файл следующую информацию:

<html>
  <title>Using BLOB and MySQL</title>
  <body>

  <?php

  require_once 'config.php';

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

  <table border = '1' align = 'center'> <caption>Products Database</caption>
    <tr>
      <th>Product Id</th>
      <th>Product Name</th>
      <th>Price</th>
      <th>Product Image</th>
    </tr>

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

  </table>
  </body>
</html>

Сохраните изменения в файле и закройте его.

Здесь мы опять использовали файл config.php для подключения к базе данных. Затем мы подготовили и выполнили команду SQL, используя PDO для извлечения всех элементов из таблицы products с помощью команды SELECT * FROM products.

Затем мы создали таблицу HTML и заполнили ее данными о продуктах, используя выражение PHP while() {...}. Строка $row = $stmt->fetch(PDO::FETCH_ASSOC) отправляет запрос в базу данных и сохраняет результат в переменной $row как многомерный массив, который затем отображается как столбец таблицы HTML с использованием синтаксиса $row['column_name'].

Изображения из столбца product_image заключены в теги <img src = "">. Мы использовали атрибуты width и height для уменьшения ширины и высоты изображений, чтобы они поместились в столбце таблицы HTML.

Чтобы конвертировать данные, хранящиеся в объекте типа BLOB, обратно в изображения, мы использовали встроенную функцию PHP base64_encode и следующий синтаксис схемы URI данных:

data:media_type;base64, base_64_encoded_data

В данном случае image/png — это значение параметра media_type (тип файла), а закодированная строка Base64 из столбца product_image — это данные base_64_encoded_data.

Выполните в браузере файл display_products.php, введя следующий адрес:

http://your-server-IP/display_products.php

Запустив в браузере файл display_products.php, вы увидите таблицу HTML со списком продуктов и связанных с ними изображений.

Это подтверждает, что скрипт PHP для извлечения изображений из базы данных MySQL работает ожидаемым образом.

В этом обучающем модуле мы использовали тип данных MySQL BLOB для хранения и вывода изображений в коде PHP в Ubuntu 18.04. Мы увидели основные преимущества хранения изображений в базе данных по сравнению с их хранением в файловой системе. В число этих преимуществ входят портативность, безопасность и удобство резервного копирования. Эта технология будет вам полезна, если вы создаете приложение, для которого требуется хранить информацию и изображения вместе, например, студенческий портал или базу данных сотрудников.

Дополнительную информацию о поддерживаемых типах данных в MySQL можно найти в руководстве по типам данных MySQL. Если вас интересуют дополнительные материалы по MySQL и PHP, пройдите следующие обучающие модули:

• Применение постраничного ввода данных в MySQL с PHP в Ubuntu 18.04​​​
• Использование расширения PDO PHP для выполнения транзакций MySQL на PHP в Ubuntu 18.04