Back to Top

How to save image in MySQL Database

how-to-store-a-file-in-database

There are two ways to save images. Most common way is to save the file name in MySQL table and upload image in folder.Another way is to store the image into directly into the Database. As, Developers usually don’t use the second method, they might get confused.

To use MySQL for database information is undoubtedly a very good choice. Sometimes to save the image or file in the database than in a separate folder is more convenient. In this article, You will learn how to save the image or file into the MySQL database and how the database image will be displayed with the practical example.

To store an uploaded file in a database is more complex part than simply moving a file with move_uploaded_file. so today we will check for storing a file in the database.MySQL database provides specialized datatype to save a large amount of data and that data type is BLOB.

What is a BLOB?

BLOB data type is a binary large object that can hold a variable length of data.A BLOB is typically used to store binary data and has four types:’

1. TINYBLOB
2. BLOB
3. MEDIUMBLOB
4. LONGBLOB

The main difference between all types is the length of the respective data can be saved.

To store a file in the database or in MySQL, Let’s create a table with the data type BLOB where the file can be stored using the following statement.As BLOB stands for Binary Large Object and its a binary-safe version of the TEXT type that means data is not readable text and will not be interpreted as readable text whereas it stored strictly binary data.

Here, I am storing an image in the database instead of moving the file from the temporary location to a final directory only so you’re going to read its contents and insert it into the database.

NOTE: BLOB columns are case-sensitive if you store data in them and then try to search them with a MySQL query.

Example:

Here is the Table Structure have two fields, First is the autoincrement Primary id and the second field is Blob for saving the image.

CREATE TABLE files (
	id INT AUTO_INCREMENT PRIMARY KEY,
	file_data MEDIUMBLOB NOT NULL
);

Next, You need a web interface for uploading an image. For that,Let’s create HTML form which allow user to upload a file which you want to store in database

After form, create filedb.php file which shows you main code to store the file in the database. Here fread function read the file or image content and save content into the MySQL database using MySQL query.

// This is the file we're going to add it in the database
$MY_FILE = $_FILES['file']['tmp_name'];

// To open the file and store its contents in $file_contents
$file = fopen($MY_FILE, 'r');
$file_contents = fread($file, filesize($MY_FILE));
fclose($file);
/* We need to escape some stcharacters that might appear in  file_contents,so do that now, before we begin the query.*/

$file_contents = addslashes($file_contents);

// To add the file in the database
mysql_connect('localhost', 'root', '') or die("Unable to connect to database.");
mysql_select_db('test') or die("Unable to select the DB.");
mysql_query("INSERT INTO files SET file_data='$file_contents'") or die("MySQL Query Error: " . mysql_error() . "

". "The SQL was: $SQL

"); mysql_close(); echo "File INSERTED into files table successfully.";

That’s it.I am done with an explanation.If you get any issue to setup this code, do let me know via comment.

Comments (9)

  1. David Zapata says:

    This is a good way. But you have to care for special chars, that’s why you use addslashes(). This could be avoided, by using base64_encode() and storing the base64 encoded content. Sure the content size will increase a little, but it is another way to store a file content with no worries about special chars.

    To retrieve it, you just need to select the needed column, and apply a base64_decode() to that content and that’s it: The original content, ready to be used.

    1. yes we can store file by using base64_encode and retrive it with base64_decode.

  2. Why would you?
    I think there’s gotta be about one use-case for storing files in a database. Hang on. I take that back. There aren’t any.

    Filesystems are optimised for File Access. Databases are best optimised for row/column based tabular data access. The two should not be confused.

    Here’s how I store uploaded files:
    1) Upload the file to …/uploaded_files/…
    2) Rename it to something sensible.
    3) In the database, store the original filename, and the path to the uploaded file.

    This makes a few drastic differences to database-based file storage.
    1) The database contains data of a predictable size.
    2) Backups are smaller.
    3) You can scale easier by having a shared/clustered filesystem, such as Gluster.
    4) If it’s a website, loading files can happen externally of your webapp, simply have a media subdomain and handle files with a lightweight webserver such as LightHTTP or Nginx.
    This means that your webapplication isn’t a bottleneck for loading every damn file that’s requested, into memory, stripping the slashes or base64_decoding it, before streaming it back to the user.

    See what I mean?

    You *can* store files in a database. Doesn’t mean you should.

  3. Mohamad Sibai says:

    Also you should store file name with extention so that when downloading the file browser can detect document type i.e. text/plain for *.txt and application/pdf for *.pdf and so on

  4. I’ll right away seize your rss as I can’t find your email subscription link or newsletter service. Do you have any? Kindly let me recognize so that I may just subscribe. Thanks.

  5. Hie thanks alot i m searching this from many days and your post help me its really good and thank you very much

  6. TambeSarakar says:

    Yes save file possible using moduleclob datatype and same as store image in database moduleblob datatype.

  7. Anuradha Nandanwar says:

    Thank you so much… i m searching this code on many days but there are so many code that not work… but this code is easy to understand and it is work… thank you..

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

PHP ODBC connection with MySQL

Posted on 7 years ago

Bhumi

How to set Expire Headers using .htaccess?

Posted on 4 years ago

Bhumi

How to use HTML5 download attribute?

Posted on 3 years ago

Bhumi

What are Traits in PHP

Posted on 4 years ago

Bhumi

The Reader’s Poll – June 2015

Posted on 3 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 4 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 5 years ago

Bhumi