Storing File in MySQL
Storing file content in database is always a debate and there are certain pros and cons about it. Most of the applications generally store files in a physical location and file metadata are stored in the database. But in certain situation where you find it more beneficial to store files into the database itself. Generally when you have comparatively small file size then it may be worth to store into the database.
In this example I am going to show you how you can store file content and its metadata into the MySQL database.
Prerequisites
PHP 8.2.7, MySQL 8.1.0
MySQL Table
To store file content and its metadata I am going to create a table in the MySQL database server.
CREATE TABLE IF NOT EXISTS `file` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`content` mediumblob,
`size` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Notice the column content is of type mediumblob, you may choose the type (tinyblob, blob, mediumblob, longblob) according to your application’s requirement.
Project Directory
The name of the project’s root directory is php-save-file-mysql. I may not mention the project’s root folder’s name but the file I will be creating with respect to the project’s root folder.
Database Connectivity
Now I am moving to the file upload example and store the uploaded file using PHP to MySQL database.
The first step is to establish the database connection to MySQL server using PHP scripting language.
The following database connectivity code is written into the file db_config.php:
<?php
// Database connection details
$db_host = "localhost";
$db_username = "root";
$db_password = "root";
$db_name = "roytuts";
// Database connection
$db = new mysqli($db_host, $db_username, $db_password, $db_name);
// Verify connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}Utility Method
The following utility method is used to convert the size of the file in human readable size. For example, if your file size is shown as 2345 bytes, so it could be more meaningful while it is shown as 2.34 KB.
The following code is written into the file utils.php file.
<?php
/**
* Converts bytes into human readable file size.
*
* @param string $bytes
* @return string human readable file size (2.87 МB)
* @author Mogilev Arseny
*/
function fileSizeConvert($bytes)
{
$bytes = floatval($bytes);
$arBytes = array(
0 => array(
"UNIT" => "TB",
"VALUE" => pow(1024, 4)
),
1 => array(
"UNIT" => "GB",
"VALUE" => pow(1024, 3)
),
2 => array(
"UNIT" => "MB",
"VALUE" => pow(1024, 2)
),
3 => array(
"UNIT" => "KB",
"VALUE" => 1024
),
4 => array(
"UNIT" => "B",
"VALUE" => 1
),
);
foreach($arBytes as $arItem)
{
if($bytes >= $arItem["VALUE"])
{
$result = $bytes / $arItem["VALUE"];
$result = strval(round($result, 2)) . " " . $arItem["UNIT"];
break;
}
}
return $result;
}
?>File Upload and Save
Here I am going to write PHP code for uploading a file using web page and save the file into the MySQL database.
<?php
// Include the database configuration file
require_once 'db_config.php';
// Include utility function file
require_once 'utils.php';
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>PHP Save File To Database</title>
</head>
<body>
<?php
// If file upload form is submitted
$status = $status_msg = '';
if(isset($_POST["submit"])){
$status = 'error';
if(!empty($_FILES["file"]["name"])) {
// Get file info
$file_name = basename($_FILES["file"]["name"]);
$fileType = pathinfo($file_name, PATHINFO_EXTENSION);
clearstatcache();
$file_size = fileSizeConvert($_FILES["file"]["size"]);
$file = $_FILES['file']['tmp_name'];
$imgContent = addslashes(file_get_contents($file));
// Insert file content into database
$insert = $db->query("INSERT into file (name, content, size, type, created) VALUES ('$file_name', '$imgContent', '$file_size', '$fileType', NOW())");
if($insert){
$status = 'success';
$status_msg = "File uploaded successfully.";
}else{
$status_msg = "File upload failed, please try again.";
}
}else{
$status_msg = 'Please select a file to upload.';
}
}
// Display status message
echo $status_msg;
?>
<p/>
<form action="upload.php" method="post" enctype="multipart/form-data">
<label>Select A File:</label>
<input type="file" name="file">
<input type="submit" name="submit" value="Upload">
</form>
</body>
</html>In the above code snippets I am not going to restrict to any particular file type for uploading. I am allowing all types of files to be uploaded and saved into the database.
Testing File Upload
Once you run the application and open the home page URL http://localhost then you will see the following page:
Next if you try to click on Upload button without selecting any file then you will see an error message:
Once you select a file and click on Upload button to upload and save the file to MySQL database, you will see the success message:
Once file uploaded successfully in the database, you can view the content in the database:
Hope you have got an idea how o save file to MySQL database in the blob column.