How to Upload a Picture as Blob Into Mysql
The author selected Girls Who Code to receive a donation as role of the Write for DOnations program.
Introduction
A Binary Large Object (BLOB
) is a MySQL information blazon that can store binary information such as images, multimedia, and PDF files.
When creating applications that require a tightly-coupled database where images should be in sync with related data (for example, an employee portal, a student database, or a financial awarding), you lot might find it convenient to store images such equally students' passport photos and signatures in a MySQL database aslope other related information.
This is where the MySQL BLOB
data type comes in. This programming approach eliminates the need for creating a separate file system for storing images. The scheme also centralizes the database, making it more portable and secure considering the data is isolated from the file system. Creating backups is also more than seamless since you lot can create a single MySQL dump file that contains all your data.
Retrieving data is faster, and when creating records you can be certain that data validation rules and referential integrity are maintained especially when using MySQL transactions.
In this tutorial, you will apply the MySQL Blob
data type to store images with PHP on Ubuntu 18.04.
Prerequisites
To follow along with this guide, y'all will need the following:
- An Ubuntu xviii.04 server configured using the Initial Server Setup with Ubuntu 18.04 and a not-root user with
sudo
privileges. - Apache, MySQL, and PHP prepare up by following the guide on How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu xviii.04. For this tutorial, it isn't necessary to create virtual hosts, so y'all can skip Step 4.
Pace 1 — Creating a Database
Yous'll start off past creating a sample database for your project. To practise this, SSH in to your server and then run the following command to log in to your MySQL server every bit root:
- sudo mysql -u root -p
Enter the root password of your MySQL database and striking ENTER
to continue.
Then, run the post-obit control to create a database. In this tutorial nosotros'll proper noun it test_company
:
- CREATE DATABASE test_company ;
Once the database is created, yous volition see the following output:
Output
Query OK, one row affected (0.01 sec)
Next, create a test_user
account on the MySQL server and remember to replace PASSWORD
with a strong password:
- CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'Countersign' ;
Y'all'll see the following output:
Output
Query OK, 0 rows affected (0.01 sec)
To grant test_user
full privileges on the test_company
database, run:
- GRANT ALL PRIVILEGES ON test_company.* TO 'test_user'@'localhost' ;
Make sure yous get the following output:
Output
Query OK, 0 rows affected (0.01 sec)
Finally, affluent the privileges table in order for MySQL to reload the permissions:
- FLUSH PRIVILEGES;
Ensure you meet the post-obit output:
Output
Query OK, 0 rows affected (0.01 sec)
Now that the test_company
database and test_user
are ready, you'll motility on to creating a products
table for storing sample products. Y'all'll use this table later to insert and call back records to demonstrate how MySQL Blob
works.
Log out from the MySQL server:
- QUIT;
Then, log back in again with the credentials of the test_user
that y'all created:
- mysql -u test_user -p
When prompted, enter the password for the test_user
and hit ENTER
to go on. Side by side, switch to the test_company
database by typing the following:
- Utilize test_company ;
One time the test_company
database is selected, MySQL volition brandish:
Output
Database inverse
Next, create a products
tabular array past running:
- CREATE TABLE `products` (product_id BIGINT Primary Fundamental AUTO_INCREMENT, product_name VARCHAR( 50 ), toll DOUBLE, product_image Blob) ENGINE = InnoDB;
This command creates a table named products
. The table has four columns:
-
product_id
: This column uses aBIGINT
data type in order to adjust a big list of products upwardly to a maximum of 2⁶³-i items. You've marked the column asMain KEY
to uniquely identify products. In order for MySQL to handle the generation of new identifiers for inserted columns, you have used the keywordAUTO_INCREMENT
. -
product_name
: This cavalcade holds the names of the products. You've used theVARCHAR
data blazon since this field volition generally handle alphanumerics upwards to a maximum offifty
characters—the limit of50
is just a hypothetical value used for the purpose of this tutorial. -
price
: For sit-in purposes, yourproducts
table contains thecost
cavalcade to store the retail toll of products. Since some products may have floating values (for example, 23.69, 45.36, 102.99), you've used theDOUBLE
data type. -
product_image
: This cavalcade uses aBLOB
data type to store the actual binary data of the products' images.
You've used the InnoDB
storage ENGINE
for the table to support a broad range of features including MySQL transactions. Afterwards executing this for creating the products
table, you'll see the post-obit output:
Output
Query OK, 0 rows affected (0.03 sec)
Log out from your MySQL server:
- QUIT;
You volition get the following output
Output
Cheerio
The products
tabular array is now ready to store some records including products' images and you lot'll populate information technology with some products in the next step.
Step ii — Creating PHP Scripts for Connecting and Populating the Database
In this step, you'll create a PHP script that will connect to the MySQL database that you created in Stride 1. The script will prepare three sample products and insert them into the products
table.
To create the PHP code, open a new file with your text editor:
- sudo nano /var/www/html/config.php
Then, enter the following information into the file and supervene upon PASSWORD
with the test_user
password that you created in Step 1:
/var/www/html/config.php
<?php define ( 'DB_NAME' , 'test_company' ) ; ascertain ( '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 , faux ) ;
Save and close the file.
In this file, you've used four PHP constants to connect to the MySQL database that you created in Pace ane:
-
DB_NAME
: This constant holds the proper name of thetest_company
database. -
DB_USER
: This variable holds thetest_user
username. -
DB_PASSWORD
: This constant stores the MySQLCountersign
of thetest_user
account. -
DB_HOST
: This represents the server where the database resides. In this instance, you are using thelocalhost
server.
The following line in your file initiates a PHP Information 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, you've 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, simulated
: This choice increases security by telling the MySQL database engine to do the gear up instead of PDO.
You'll include the /var/world wide web/html/config.php
file in two PHP scripts that you will create next for inserting and retrieving records respectively.
First, create the /var/world wide web/html/insert_products.php
PHP script for inserting records to the products table:
- sudo nano /var/www/html/insert_products.php
And so, add the post-obit information into the /var/www/html/insert_products.php
file:
/var/www/html/insert_products.php
<?php require_once 'config.php' ; $products = [ ] ; $products [ ] = [ 'product_name' => 'VIRTUAL SERVERS' , 'cost' => v , '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' => fifteen , 'product_image' => file_get_contents ( "https://i.imgur.com/UYcHkKD.png" ) ] ; $sql = "INSERT INTO products(product_name, cost, product_image) VALUES (:product_name, :price, :product_image)" ; foreach ( $products as $production ) { $stmt = $pdo -> set ( $sql ) ; $stmt -> execute ( $product ) ; } echo "Records inserted successfully" ;
Save and shut the file.
In the file, yous've included the config.php
file at the top. This is the starting time file you lot created for defining the database variables and connecting to the database. The file too initiates a PDO object and stores it in a $pdo
variable.
Adjacent, you've created an array of the products' data to be inserted into the database. Apart from the product_name
and price
, which are prepared as strings and numeric values respectively, the script uses PHP'south in-built file_get_contents
function to read images from an external source and pass them every bit strings to the product_image
column.
Next, yous accept prepared an SQL statement and used the PHP foreach{...}
statement to insert each product into the database.
To execute the /var/world wide web/html/insert_products.php
file, run information technology in your browser window using the following URL. Remember to supplant your-server-IP
with the public IP accost of your server:
http://your-server-IP/insert_products.php
After executing the file, you'll see a success message in your browser confirming records were inserted into the database.
You have successfully inserted three records containing product images into the products
table. In the next pace, you'll create a PHP script for retrieving these records and displaying them in your browser.
Step 3 — Displaying Products' Information From the MySQL Database
With the products' data and images in the database, you lot're now going to code another PHP script that queries and displays the products' information in an HTML table on your browser.
To create the file, type the following:
- sudo nano /var/world wide web/html/display_products.php
So, enter the post-obit information into the file:
/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 -> gear up ( $sql ) ; $stmt -> execute ( ) ; ?> <table border = '1' align = 'heart' > <explanation > Products Database </caption > <tr > <th > Product Id </thursday > <th > Product Name </th > <th > Cost </th > <th > Production Image </th > </tr > <?php while ( $row = $stmt -> fetch ( PDO :: FETCH_ASSOC ) ) { repeat '<tr>' ; echo '<td>' . $row [ 'product_id' ] . '</td>' ; echo '<td>' . $row [ 'product_name' ] . '</td>' ; echo '<td>' . $row [ 'toll' ] . '</td>' ; repeat '<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.
Hither you've again included the config.php
file in order to connect to the database. Then, you lot have prepared and executed an SQL argument using PDO to recollect all items from the products
tabular array using the SELECT * FROM products
command.
Next, you take created 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 consequence in the $row
variable as a multi-dimensional array, which you take and so displayed in an HTML table column using the $row['column_name']
syntax.
The images from the product_image
column are enclosed within the <img src = "">
tags. You lot've used the width
and height
attributes to resize the images to a smaller size that tin can fit in the HTML table column.
In order to convert the data held by the BLOB
data type dorsum to images, you've used the in-congenital PHP base64_encode
function and the following syntax for the Data URI scheme:
information:media_type;base64, base_64_encoded_data
In this case, the image/png
is the media_type
and the Base64
encoded cord from the product_image
column is the base_64_encoded_data
.
Adjacent, execute the display_products.php
file in a web browser past typing the following address:
http://your-server-IP/display_products.php
After running the display_products.php
file in your browser, y'all will see an HTML table with a listing of products and associated images.
This confirms that the PHP script for retrieving images from MySQL is working every bit expected.
Conclusion
In this guide, you used the MySQL BLOB
information type to shop and display images with PHP on Ubuntu xviii.04. You've likewise seen the basic advantages of storing images in a database as opposed to storing them in a file organization. These include portability, security, and ease of backup. If you are building an application such as a students' portal or employees' database that requires information and related images to be stored together, so this applied science tin can exist of great apply to yous.
For more than information about the supported data types in MySQL follow the MySQL Data Types guide. If you lot're interested in further content relating to MySQL and PHP, check out the following tutorials:
- How To Implement Pagination in MySQL with PHP on Ubuntu xviii.04
- How To Use the PDO PHP Extension to Perform MySQL Transactions in PHP on Ubuntu 18.04
Source: https://www.digitalocean.com/community/tutorials/how-to-use-the-mysql-blob-data-type-to-store-images-with-php-on-ubuntu-18-04
0 Response to "How to Upload a Picture as Blob Into Mysql"
Post a Comment