We have seen file upload using PHP in previous tutorials PHP file upload but in this tutorial we will see how to save 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 in the database

  1. ACID 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 in the 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.

In this example the files are selected using the browse buttons and files are 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="uploadFilesDb.php" method="POST">
	<fieldset>
		<legend>File Management using PHP, MySQL</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(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];
}
?>

The complete source code is given below; file name – uploadFilesDb.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>File Management using PHP, MySQL</title>
        <style type="text/css">
            body {
                background-color: #fff;
                margin: 40px;
                font: 13px/20px normal Helvetica, Arial, sans-serif;
                color: #4F5155;
            }
            #body{
                margin: 0 15px 0 15px;
            }
            #container{
                margin: 10px;
                width: 600px;
                padding: 10px;
                border: 1px solid #D0D0D0;
                -webkit-box-shadow: 0 0 8px #D0D0D0;
            }
            .error {
                color: #E13300;
            }
            .info {
                color: gold;
            }
            .success {
                color: darkgreen;
            }
        </style>
        <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
    </head>
    <body>
        <?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(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];
		}
        ?>
        <div id="container">
            <form name="upload_form" enctype="multipart/form-data" action="uploadFilesDb.php" method="POST">
                <fieldset>
                    <legend>File Management using PHP, MySQL</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>
        </div>        
    </body>
</html>

<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>

MySQL table creation script

CREATE TABLE `files` (
  `id` int(10) unsigned 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 files to mysql using php

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

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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