Bobcares

WeSupport

Call Us! 1-800-383-5193
Call Us! 1-800-383-5193
Call Us! 1-800-383-5193

Need Help?

Emergency Response Time custom

Our experts have had an average response time of 11.06 minutes in March 2021 to fix urgent issues.

We will keep your servers stable, secure and fast at all times for one fixed price.

MySQL BLOB Data Type – How to set up & its uses

by | Jan 25, 2021

Figuring out MySQL BLOB Data Type? We can help you.

The advantages of storing images in a database as opposed to storing them in a file system include portability, security, and ease of backup.

Suppose we are building an application such as an employees’ database that requires information and images to be stored together, then this technology can be of great use to us.

As part of our Server Management Services, we assist our customers with several MySQL queries.

Today, let us see how to use the MySQL BLOB data type to store images with PHP on Ubuntu 18.04.

 

What is MySQL BLOB Data Type?

A Binary Large Object (BLOB) is a MySQL data type that can store binary data such as images, multimedia, and PDF files.

As we mentioned early, if we create applications that require a tightly-coupled database where images should be in sync with related data, it is convenient to store them in a MySQL database.

Here comes the benefit of MySQL BLOB data type. It eliminates the need for creating a separate file system for storing images. Similarly, they centralize the database, making it more portable and secure because the data isolates from the file system.

Since we can create a single MySQL dump file that contains all the data, creating backups is also more seamless.

 

MySQL BLOB Data Type

In order to begin, our Support Techs suggest having:

  • An Ubuntu 18.04 server configured with a non-root user with sudo privileges.
  • Apache, MySQL, and PHP set up on Ubuntu 18.04 server.

 

Step 1 – Create a Database

Initially, let us create a sample database. To do this, access the server via SSH and run the following command to log in to the MySQL server as root:

$ sudo mysql -u root -p

We need to enter the root password of the MySQL database.

Then, run the following command to create a database. Let’s name it test_company:

mysql> CREATE DATABASE test_company;

Once done, our output will be similar to:

Query OK, 1 row affected (0.01 sec)

Next, we create a test_user account on the MySQL server and replace PASSWORD with a strong password:

mysql> CREATE USER ‘test_user’@’localhost’ IDENTIFIED BY ‘PASSWORD’;

The output will be like this:

Query OK, 0 rows affected (0.01 sec)

To grant test_user full privileges on the test_company database, we run:

mysql> GRANT ALL PRIVILEGES ON test_company.* TO ‘test_user’@’localhost’;

Our output will be like the following:

Query OK, 0 rows affected (0.01 sec)

Finally, we flush the privileges table in order for MySQL to reload the permissions:

mysql> FLUSH PRIVILEGES;

We will receive an output similar to:

Query OK, 0 rows affected (0.01 sec)

Now that it is done, we will move on to create a products table to store sample products. This table is to insert and retrieve records to demonstrate how MySQL BLOB works.

First, we need to log out from the MySQL server:

mysql> QUIT;

Then, log back in again with the credentials of the test_user:

$ mysql -u test_user -p

We will receive a prompt, enter the password for the test_user there. Next, run the below command to switch to the test_company database;

mysql> USE test_company;

Once we select the test_company database, MySQL will display:

Database changed
Next, we run the below command to create a products table:
mysql> CREATE TABLE `products` (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE, product_image BLOB) ENGINE = InnoDB;

The table has four columns:

  • product_id:

This column uses a BIGINT data type in order to accommodate a large list of products up to a maximum of 2⁶³-1 items. We mark the column as PRIMARY KEY to uniquely identify products. For MySQL to handle the generation of new identifiers for inserted columns, we use the keyword AUTO_INCREMENT.

  • product_name:

This column holds the names of the products. We use the VARCHAR data type since this field will generally handle alphanumerics up to a maximum of 50 characters(hypothetical).

  • price:

The products table contains the price column to store the retail price of products. Since a few products have floating values we use the DOUBLE data type.

  • product_image:

It uses a BLOB data type to store the actual binary data of the products’ images.

We use the InnoDB storage ENGINE for the table to support a wide range of features including MySQL transactions.

Once we are done creating the products table, we will receive the following output:

Query OK, 0 rows affected (0.03 sec)

Then we log out from your MySQL server:

mysql> QUIT;

Then, our output will be:

Bye

 

Step 2 – Create PHP Scripts to connect and populate the Database

In this step, we will create a PHP script that will connect to the MySQL database. It will prepare three sample products and insert them into the products table.

To create the PHP code, we open a new file in a text editor:

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

Then, we enter the following information into the file and replace PASSWORD with the test_user password.

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

Save and close the file.

In this file, we have four PHP constants to connect to the MySQL database:

  • DB_NAME: This constant holds the name of the test_company database.
  • DB_USER: This variable holds the test_user username.
  • DB_PASSWORD: This constant stores the MySQL PASSWORD of the test_user account.
  • DB_HOST: This represents the server where the database resides. In this case, you are using the localhost server.

The following line in the file initiates a PHP Data Object (PDO) and connects to the MySQL database:

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

Toward the end of the file, we have set a couple of PDO attributes:

  • ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: This attribute instructs PDO to throw an exception that can be logged for debugging purposes.
  • ATTR_EMULATE_PREPARES, false: This option increases security by telling the MySQL database engine to do the preparation instead of PDO.

We will include the /var/www/html/config.php file in two PHP scripts that we will create next for inserting and retrieving records respectively.

First, create the /var/www/html/insert_products.php PHP script for inserting records to the products table:

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

Then, we add the following information into the /var/www/html/insert_products.php file:

<?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”;
Save and close the file.

In the file, we have the config.php file at the top which is to define the database variables and to connect to the database. The file also initiates a PDO object and stores it in a $pdo variable.

Next, we have created an array of the products’ data to insert into the database. Apart from the product_name and price, the script uses PHP’s in-built file_get_contents function to read images from an external source and pass them as strings to the product_image column.

Next, we have prepared an SQL statement and used the PHP foreach{…} statement to insert each product into the database.

To execute the /var/www/html/insert_products.php file, we run it in a browser window using the following URL:

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

Make sure to replace your-server-IP with the public IP address of your server.

Finally, we will see a success message in the browser confirming records were inserted into the database.

 

Step 3 – Displaying Products’ Information From the MySQL Database

Let us now code another PHP script that queries and displays the products’ information in an HTML table on the browser.

To create the file, we type the following:

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

Then, enter the following information into the file:

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

Save the changes to the file and close it.

Here we have the config.php file in order to connect to the database. Then, we have prepared and executed an SQL statement using PDO to retrieve all items from the products table using the SELECT * FROM products command.

Next, we have an HTML table and populated it with the products’ data using the PHP while() {…} statement.

The line $row = $stmt->fetch(PDO::FETCH_ASSOC) queries the database and stores the result in the $row variable as a multi-dimensional array, which we display in an HTML table column using the $row[‘column_name’] syntax.

The images from the product_image column are enclosed inside the <img src = “”> tags. We use the width and height attributes to resize the images to a smaller size that can fit in the HTML table column.

In order to convert the data held by the BLOB data type back to images, we use the in-built PHP base64_encode function and the following syntax for the Data URI scheme:

data:media_type;base64, base_64_encoded_data

In this case, the image/png is the media_type and the Base64 encoded string from the product_image column is the base_64_encoded_data.

Next, we execute the display_products.php file in a web browser:

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

After running the display_products.php file in the browser, we will see an HTML table with a list of products and associated images.

This confirms that the PHP script for retrieving images from MySQL is working as expected.

[Stuck between the process? We are here to help you]

 

Conclusion

In short, we used the MySQL BLOB data type to store and display images with PHP on Ubuntu 18.04. Today, we saw the methods our Support Techs employ in order to get it done.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF