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.
1 2 3 4 5 6 | 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
1 2 3 4 5 6 | <input type="file" name="file" value="" /> <input type="submit" name="Upload" value="Upload"> </form> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | // 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() . "<br><br>". "The SQL was: $SQL<br><br>"); 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 (13)