Php blob to image

Pulling BLOB image data from MySQL in PHP

I was reading from a few tutorials on how to upload my image into the DB as binary opposed to putting them on the server itself, well I got it to work like this:

My issue is how do you now pull it from the database, I’ve read several ways of doing it, tried them all, can’t figure it out, I’m not getting a MySQL error, here’s how I’m trying it:

This works. kind of, what’s happening is that it’s printing out raw binary in the IMG tag.

How do I get this to compile to a readble image again? Also, is storing the images in the database stupid? Should I just do what I usually do and store them on the server?

3 Answers 3

You can store images in your database if you want to (though there’s nothing wrong with just storing them as files either, choose whatever is appropriate in your situation), but store the raw binary data in a BLOB (i.e. don’t encode it with base64). You can embed the binary data you get from file_get_contents in your query directly, provided you use the proper escape function ( mysql_real_escape_string in your case) first.

As for the outputting of the image, you can do it the way you’re doing it right now, but you’ll have to output it base64-encoded and with a data URI scheme like this:

Note that there are some advantages and disadvantages of embedded image data. Some important disadvantages to be aware of are the severe overhead of base64 encoding (around 33% larger than original) and potential caching problems.

Источник

How To Use the MySQL BLOB Data Type to Store Images with PHP on Ubuntu 18.04

Published on May 5, 2020

The author selected Girls Who Code to receive a donation as part of the Write for DOnations program.

Introduction

A Binary Large Object ( BLOB ) is a MySQL data type that can store binary data 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 application), you might find it convenient to store images such as students’ passport photos and signatures in a MySQL database alongside 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 because the data is isolated from the file system. Creating backups is also more seamless since you can create a single MySQL dump file that contains all your data.

Retrieving data is faster, and when creating records you can be sure that data validation rules and referential integrity are maintained especially when using MySQL transactions.

In this tutorial, you will use the MySQL BLOB data type to store images with PHP on Ubuntu 18.04.

Prerequisites

To follow along with this guide, you will need the following:

Step 1 — Creating a Database

You’ll start off by creating a sample database for your project. To do this, SSH in to your server and then run the following command to log in to your MySQL server as root:

Enter the root password of your MySQL database and hit ENTER to continue.

Then, run the following command to create a database. In this tutorial we’ll name it test_company :

Once the database is created, you will see the following output:

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

You’ll see the following output:

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

Make sure you get the following output:

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

Ensure you see the following output:

Now that the test_company database and test_user are ready, you’ll move on to creating a products table for storing sample products. You’ll use this table later to insert and retrieve records to demonstrate how MySQL BLOB works.

Log out from the MySQL server:

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

When prompted, enter the password for the test_user and hit ENTER to continue. Next, switch to the test_company database by typing the following:

Once the test_company database is selected, MySQL will display:

Next, create a products table by running:

product_name : This column holds the names of the products. You’ve used the VARCHAR data type since this field will generally handle alphanumerics up to a maximum of 50 characters—the limit of 50 is just a hypothetical value used for the purpose of this tutorial.

price : For demonstration purposes, your products table contains the price column to store the retail price of products. Since some products may have floating values (for example, 23.69, 45.36, 102.99), you’ve used the DOUBLE data type.

product_image : This column uses a BLOB 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 wide range of features including MySQL transactions. After executing this for creating the products table, you’ll see the following output:

Log out from your MySQL server:

You will get the following output

The products table is now ready to store some records including products’ images and you’ll populate it with some products in the next step.

Step 2 — 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 Step 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:

Then, enter the following information into the file and replace PASSWORD with the test_user password that you created in Step 1:

Save and close the file.

In this file, you’ve used four PHP constants to connect to the MySQL database that you created in Step 1:

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 your file initiates a PHP Data Object (PDO) and connects to the MySQL database:

Toward the end of the file, you’ve set a couple of PDO attributes:

You’ll include the /var/www/html/config.php file in two PHP scripts that you 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:

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

Save and close the file.

Next, you 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, run it in your browser window using the following URL. Remember to replace your-server-IP with the public IP address of your server:

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 step, 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’ information and images in the database, you’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:

Then, enter the following information into the file:

Save the changes to the file and close it.

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

The images from the product_image column are enclosed inside the tags. You’ve used 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, you’ve used the in-built PHP base64_encode function and the following syntax for the Data URI scheme:

Next, execute the display_products.php file in a web browser by typing the following address:

After running the display_products.php file in your browser, you 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.

Conclusion

In this guide, you used the MySQL BLOB data type to store and display images with PHP on Ubuntu 18.04. You’ve also seen the basic advantages of storing images in a database as opposed to storing them in a file system. 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, then this technology can be of great use to you.

For more information about the supported data types in MySQL follow the MySQL Data Types guide. If you’re interested in further content relating to MySQL and PHP, check out the following tutorials:

Источник

MySQL BLOB using PHP

BLOB is a kind of MySQL datatype referred as Binary Large Objects. As its name, it is used to store huge volume of data as binary strings as similar as MYSQL BINARY and VARBINARY types.

Classification of MySQL BLOB

In this MySQL tutorial lets us learn to insert and read MySQL BLOB using PHP.

To start with, we need to create a MySQL table with a BLOB column and the SQL script is,

output_images.sql

Insert Image as MySQL BLOB

To insert an image into MySQL BLOB column the steps are,

PHP script to insert BLOB data is,

index.php

After executing this script image upload form will be shown,

On form submit, this PHP code gets the content of the image file and stores it into the MySQL BLOB column as binary data.

Read Image BLOB to Display

For displaying BLOB images to the browser, we have to create a PHP file to do to following.

imageView.php

This PHP code will display MySQL image BLOB data. From HTML image tag we can refer this PHP file with the corresponding image_id as an argument. For example,

listImages.php

Comments to “MySQL BLOB using PHP”

not working for me, where does if(isset($_GET[‘image_id’])) get set

image_id might be empty while requesting page

Check the db if there is required blob entry.

in “Read Image BLOB to Display”
instead of showing the image there it self, I would like to just display the name of the file, and when clicked it has to be downloaded.

Please give steps for the same… :))

when i retriving image from database, it show me too many samples, what is the problem

Thanks for this tutorial
i have a different problem
my database consist blob record of pics and i want to transfer pics to a folder (specified by a field in table)
regards
manoj bisht

thank you very mutch you help me to discover all of my default in my script… thank again?:)

Thanks very much for this info, it was just what I needed!

this was good code to insert image in database but i found error in display time

wow it’s work thanks

if i want to do the insert operation from a outer file called insert.php then how do i change this code mam? please let me know when you notice my query….

Dear, Vincy, thank you for your tutorial, it’s help me to improve my php programming skill, i hope we could discuss about php in this forum.
I have test the aplication with hijacked image file that contents php code, and it could not hijacked the web. This is the way that i hope by using the blob database than upload and save the file in a folder.
Bye the way,thank you and God bless you Vincy.

Your new friends from Indonesia,
Harry Witriyono

Hello,
Please Help me for my project, i am student of CS, so i am working on a project. My project title is “Daily K2” ePaper, (online news paper, using jpg in body). so first of all i need interface which i have designed using html and css, now i need Database (wamp server) and php code for inserting news paper image with date, and also display that jpg with date.
i have four pages of news paper with jpg imag in main body which must insert is database in every day by day, and must display with date…..
I also need to facility to display previous papers with date……

Please help me in my project sir
Am waiting for your Message
Karar Barcha

I’ve been searching for a couple days to find an example that shows how to update a longblob. If I use the exact same data to UPDATE in a MySQL statement, I don’t get any results. INSERT has been simple. UPDATE seems impossible.

I would like to add a 5 file fields undersame record is it possible?

Sure Jishnu, I will write an article on PDO and Sqlite soon. Thanks.

With some tweaks here and there I was able to complete my school assignment. This is the best tutorial I’ve ever seen for ‘Inserting Images in PHP’

Источник

How can I compress blob image when retrieving to database?

Im using the following code to upload the image in my database:

and retrieve the photo this way:

When I upload a large image it loads the original file size. I want to optimize my website. Is there any way to compress the image when I retrieve them to the database so it loads faster to the website?

1 Answer 1

I would suggest not adding images to the database. At least not a MySQL database. Instead, try to store images in the filesystem and only store the filesystem path in the database. Consider renaming the files to long unique random names and storing that along with the original filename in the database.

Using PHP you can serve your files using their original names:

MySQL is great att manipulating text, large blobs of binary data are a different story though. You should also ask yourself what you are gaining by storing images in the database.

About compression. Images are commonly compressed quite well so further compressing them for database storage is usually not feasible.

I would also strongly urge you to look into Prepared Statements for MySQL. https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

That allows you to use queries like:

The question mark signifies a variable which you pass to MySQL as binary with a separate function. This prevents SQL injection attacks which addslashes() definitely will not.

Источник