Using MySQL BLOB Data with PHP on Ubuntu 20.04

Introduction

BLOB is an acronym for Binary Large Object. This data-type stores binary information in a MySQL database(e.g., images, PDF, and multimedia files). Use BLOB instead of using a file-system to store images and referencing the path in your database when developing applications that require tightly-coupled data. For instance: an employee portal, light-weight e-commerce websites, school databases, and bank-related applications.

When using a BLOB data type in an application, you should create a specific column to hold the binary data (e.g., a student's passport in a column named photo).

Using BLOB data-type allows your application to have a central repository for all related information, making maintenance and database backup easier. Another advantage is database integrity. Use MySQL transactions when users enter data into your application to check consistency at the database level.

In this guide, you'll use PHP to create a sample project that Uses MySQL BLOB data type to store students' information, including photos in a central database.

Prerequisites

Before you begin, ensure you have the following:

• An Ubuntu 20.04 server.
• A sudo user.
• An Apache web server.

Create a Test Database

First, SSH to your server. Then, download some samples images we generated from This Person Does Not Exist to use in your database and save them to the /var/lib/mysql-files directory. Please note, this is a default read directory defined by the secure_file_priv system variable.

$ sudo wget -O /var/lib/mysql-files/mary.png https://raw.githubusercontent.com/vultr/vultr-docs/main/article-assets/5743/mary.png
$ sudo wget -O /var/lib/mysql-files/john.png https://raw.githubusercontent.com/vultr/vultr-docs/main/article-assets/5743/john.png
$ sudo wget -O /var/lib/mysql-files/baby.png https://raw.githubusercontent.com/vultr/vultr-docs/main/article-assets/5743/baby.png

Next, log in to the MySQL server.

$ sudo mysql -u root -p

When prompted, enter the root password for your MySQL server and press Enter to proceed.

Create a students database.

mysql> CREATE DATABASE students;

Select the database.

mysql> USE students;

Create a students' register table. For this project, you'll use the MEDIUMBLOB data type, which can store up to 16 MB of binary data.

mysql> CREATE TABLE register (
       student_id INT AUTO_INCREMENT PRIMARY KEY, 
       first_name VARCHAR(50), 
       last_name  VARCHAR(50),
       photo      MEDIUMBLOB
       );

Then, add some sample students to the register table by running the commands below one by one. The LOAD_FILE function retrieves the images you've downloaded using wget and converts them to a format understandable by the MySQL BLOB column defined as photo.

mysql> INSERT INTO register (first_name, last_name, photo) VALUES ('MARY', 'ROE', LOAD_FILE("/var/lib/mysql-files/mary.png"));
mysql> INSERT INTO register (first_name, last_name, photo) VALUES ('JOHN', 'DOE', LOAD_FILE("/var/lib/mysql-files/john.png"));
mysql> INSERT INTO register (first_name, last_name, photo) VALUES ('BABY', 'ROE', LOAD_FILE("/var/lib/mysql-files/baby.png"));

Query the register table to ensure that the records were inserted successfully. Since the BLOB column lists a large string of characters, use the MySQL LEFT keyword to get the first 10 characters, which is sufficient to show that the photo column has some data.

mysql> SELECT
       student_id,
       first_name,
       last_name,
       HEX(LEFT(photo, 10))
       from register;

You should get the output shown below if you've followed the instructions correctly.

+------------+------------+-----------+----------------------+
| student_id | first_name | last_name | HEX(LEFT(photo, 10)) |
+------------+------------+-----------+----------------------+
|          1 | MARY       | ROE       | 89504E470D0A1A0A0000 |
|          2 | JOHN       | DOE       | 89504E470D0A1A0A0000 |
|          3 | BABY       | ROE       | 89504E470D0A1A0A0000 |
+------------+------------+-----------+----------------------+
3 rows in set (0.00 sec)

Close the MySQL console.

quit;

Create a PHP Script to Retrieve Records from the Database

Next, create a PHP script in the root folder of your website to connect to the MySQL database using PDO(PHP Data Objects) and retrieve the records you've added above. The script will also display each student's information and photo in an HTML web page.

For convenience, download the example script from GitHub here:

$ sudo wget -O /var/www/html/students.php https://raw.githubusercontent.com/vultr/vultr-docs/main/article-assets/5743/students.php

Open the file for editing.

$ sudo nano /var/www/html/students.php

Update the database variables with your database name, user, password, and host. Look for this section:

$db_name     = 'students';
$db_user     = 'sample_user';
$db_password = 'SAMPLE_PASSWORD';
$db_host     = 'localhost';

Save and close the file by pressing Ctrl + X, Y then Enter.

Test the PHP Script

Next, visit the URL below and remember to replace public_ip_address with the public IP address or domain name of your website.

http://your_server_public_ip_address/students.php

You should see an HTML web page with the records you've created in the database, including the students' photos, as shown below.

Conclusion

As you can see from this guide, MySQL BLOB data type is advantageous in applications that require binary data to be stored together with the associated information in a single database. This approach for storing binary data is excellent for light-weight applications.