Save Files to MySQL using PHP

I have shown file upload example using PHP in previous tutorials PHP file upload but in this tutorial I will show you how to save the uploaded files to MySQL database using PHP.

You may be wonder why you would need to save file content into database rather than just into the file system. In situations where PHP application needs to put entire file then the preferred method would be to put the file into server’s file system and save the file path into the database.

However in situations, where an application needs to save the entire file content into database along with other data. In such cases you have to put the file content into MySQL’s BLOB data type field.

Pros of Storing Files into Database

  1. ACID (Atomicity, Consistency, Integrity, Durability) consistency including a rollback of an update which is complicated when the files are stored outside the database. Having the files and database in sync and able to participate in transactions can be very useful.
  2. Files go with the database and cannot be orphaned from it.
  3. Backups automatically include the file binaries.

Cons of Storing Files into Database

  1. The size of a binary file differs among databases.
  2. Storing the files in the database can make the database much larger. Thus increases the size of the database. The level of knowledge required to maintain a database goes up in proportion to the size of the database, i.e., large databases are more complicated to maintain than small databases. Therefore it increases the maintenance cost to the business.
  3. Larger database consumes more memory as they try to do stuffs into memory as much data as possible.
  4. Portability needs to be taken care if system specific features like SQL Server’s FILESTREAM object is used and in future you need to to migrate to a different database system.
  5. The code that stores files into the database can be a problem. When you store files on the file system, there isn’t an additional layer involved to wrap/tweak/alter the source file.
  6. It is more complicated to serve up the files to a website because, you have to write a handler to stream the file binary from the database. Again, adding a handler is not impossible but adds complexity.
  7. In many cases, it will bloat the database files and cause several performance issues. If you stick the blobs in a table with a large number of columns it’s even worse.

Prerequisites

PHP 7.4.3, MySQL 7.0.17, Apache HTTP Server 2.4

Upload Files and Save into MySQL

In this example the files are selected using the browse buttons and files are saved into MySQL database’s BLOB field. PHP script stores the files into the specified column of the database table and returns response as a success message from the PHP script. As a validation step I have added only to check if you have selected at least one file for uploading or not.

This tutorial will give you more attachment link for uploading file, so you will click on Attach another file then you will get Choose File button to attach another file. If you do not want to upload the chosen file then you can delete the attached file using Delete link.

The below HTML form is required in order to upload multiple files:

<form name="upload_form" enctype="multipart/form-data" action="php-files-upload-save-mysql.php" method="POST">
	<fieldset>
		<legend>Files Save into MySQL database using PHP</legend>
		<section>
			<label>Browse a file</label>
			<label>
				<input type="file" name="upload_file1" id="upload_file1" readonly="true"/>
			</label>
			<div id="moreFileUpload"></div>
			<div style="clear:both;"></div>
			<div id="moreFileUploadLink" style="display:none;margin-left: 10px;">
				<a href="javascript:void(0);" id="attachMore">Attach another file</a>
			</div>
		</section>
	</fieldset>
	<div>&nbsp;</div>
	<footer>
		<input type="submit" name="upload" value="Upload"/>
	</footer>
</form>

Below jQuery code snippets are required in order to attach more file and delete the selected file if you do not want to upload the chosen file.

<script type="text/javascript">
    $(document).ready(function () {
        $("input[id^='upload_file']").each(function () {
            var id = parseInt(this.id.replace("upload_file", ""));
            $("#upload_file" + id).change(function () {
                if ($("#upload_file" + id).val() !== "") {
                    $("#moreFileUploadLink").show();
                }
            });
        });
    });
</script>

<script type="text/javascript">
    $(document).ready(function () {
        var upload_number = 2;
        $('#attachMore').click(function () {
            //add more file
            var moreUploadTag = '';
            
			moreUploadTag += '<div class="element"><label for="upload_file"' + upload_number + '>Upload File ' + upload_number + '</label>';
            moreUploadTag += '&nbsp;<input type="file" id="upload_file' + upload_number + '" name="upload_file' + upload_number + '"/>';
            moreUploadTag += '&nbsp;<a href="javascript:void" style="cursor:pointer;" onclick="deletefileLink(' + upload_number + ')">Delete ' + upload_number + '</a></div>';
			
            $('<dl id="delete_file' + upload_number + '">' + moreUploadTag + '</dl>').fadeIn('slow').appendTo('#moreFileUpload');
            
			upload_number++;
        });
    });
</script>

<script type="text/javascript">
    function deletefileLink(eleId) {
        if (confirm("Are you really want to delete ?")) {
            var ele = document.getElementById("delete_file" + eleId);
            ele.parentNode.removeChild(ele);
        }
    }
</script>

Below PHP code is required for validating and saving files into MySQL database.

<?php
	if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['upload'])) {
		if ($_FILES['upload_file1']['size'] <= 0) {
			echo 'Hey, Please choose at least one file';
		} else {
			foreach ($_FILES as $key => $value) {
				if (0 < $value['error']) {
					echo 'Error during file upload ' . $value['error'];
				} else if (!empty($value['name'])) {
					$dbConn = mysqli_connect('localhost', 'db_name', 'db_pass', 'roytuts') or die('MySQL connect failed. ' . mysqli_connect_error());
					
					$sql = "insert into files_data(name, type, size, content, saved_date) values('".$value['name']."', '".$value['type']."', '".filesize_formatted($value['size'])."', '".mysqli_escape_string($dbConn, file_get_contents($value['tmp_name']))."', '".date('Y-m-d H:i:s')."')";
					
					$result = mysqli_query($dbConn, $sql) or die(mysqli_error($dbConn));
					
					if($result) {
						echo 'File successfully saved to database';
					}
				}
			}
		}
	}
	
	function filesize_formatted($size) {
		$units = array( 'B', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB');
		$power = $size > 0 ? floor(log($size, 1024)) : 0;
		
		return number_format($size / pow(1024, $power), 2, '.', ',') . ' ' . $units[$power];
	}
?>

MySQL Table

I am creating a table called files_data under roytuts database in MySQL server.

CREATE TABLE `files_data` (
  `id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `size` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` blob NOT NULL,
  `saved_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Testing the application

When you run the above PHP file then you would see the below form for uploading files:

save files to mysql using php

When files are chosen for uploading:

save uploaded files to mysql using php

When files are saved into MySQL database then you will get success message.

save uploaded files to mysql using php

Not choosing any file for upload and clicking on Upload button will give you error message.

Source Code

Download

1 thought on “Save Files to MySQL using PHP

  1. I use this method, and is great, but, how can I download the files that are stored in there. Is exist any method to decode the blob? Thanks

Leave a Reply

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