Storing Images And Other Files In MySQL BLOB FieldsStoring Images And Other Files In MySQL BLOB Fields

Occasionally, there'll be an instance where images and other files would be better stored within a database record instead of somewhere on the server. This is done by storing the file in a "blog" field of a MySQL table using PHP. I'll show you one example that I use when this method needs to be employed.

The process goes like this: 
User uploads a file from an HTML form.File is posted to our PHP file where the image data is stored in the database.Files are recalled using a separate PHP file and the header() function.Okay, first we'll create our files table without our database. This is just an example, change it however you like, but the FileName, FileType, FileSize, and FileContents are the most important fields in this case.
<code>mysql_query("CREATE TABLE `yourdatabase`.`files` (</code>`FileID` INT NOT NULL AUTO_INCREMENT,
`FileName` VARCHAR( 100 ) NOT NULL,
`FileType` VARCHAR( 100 ) NOT NULL,
`FileSize` VARCHAR( 32 ) NOT NULL,
`FileContents` BLOB NOT NULL,
`DateUploaded` DATETIME NOT NULL,
PRIMARY KEY ( `FileID` )
) ENGINE = MYISAM;");
Now, once the user has selected the file to upload, and has submitted the form, the post data including the file, will be sent to the page with the following script.
if(isset($_FILES["file"])) {
if($_FILES["file"]["error"] > 0) {
echo $_FILES["file"]["error"];
die();
}
$name = addslashes($_FILES["file"]["name"]);
$type = addslashes($_FILES["file"]["type"]);
$size = addslashes($_FILES["file"]["size"]);
$file = base64_encode(file_get_contents($_FILES["file"]["tmp_name"]));
$date = date("Y-m-d H:i:s");
$insert = mysql_query("INSERT INTO files (FileName, FileType, FileSize, FileContents, DateUploaded) VALUES ('$name', '$type', '$size', '$file', '$date')");
if($insert) {
echo "File Uploaded Successfully!";
}
else {
echo "File Upload Error!";
}
}
There's something you should notice about this little script we wrote. Located in the $file variable, you can see the base64_encode() function being used. It's a good idea to encode the file contents to keep the file from corrupting when it's loaded into the database, making it impossible to reopen the file.
Now to recall the file from the database we'll want to create a file that has a $_GET variable with the ID of the record in the database:
if(isset($_GET['id'])) {
$id = addslashes($_GET['id']);
$filecontents = mysql_query("SELECT * FROM files WHERE FileID = '$id'");
$file = mysql_fetch_assoc($filecontents);
$name = str_replace(" ", "_", $file["FileName"]);
$type = $file["FileType"];
$content = base64_decode($file["FileContents"]);
header("Content-type: ".$type);
header("Content-Disposition: attachment; Filename=".$name);
echo $content;
die();
}

With this segment, we're taking the name of the file and taking out any spaces to make sure parts after spaces aren't getting cut off. Then we're decoding the base64 encoding. We're setting the header Content-type to be the file type we stored in the database, and naming the attachment with the original name of the file. We then echo the content of the file and use the die() function to keep anything else from being displayed on the page. The result is the file being displayed or a "File Download" message box from the browser if it's a file that cannot be displayed in the browser.
One last thing I'd like to share is for storing images in the MySQL database and then having them display in a HTML img tag. What we'll do is simply have our normal img tag and use the PHP file as the image src.
<img src="document.php?id=1234">
by Matt J Watson
References and Bibliography
I hope this helps you guys next time you need to implement something like this. Have questions? Let me know: you can also reach me at http://www.webdesignamigo.com/
Rated:NR/0 Votes
4 Views
Add To My Article Reading List
Add To My Article Reading List
Print Article
Print
More Article By Matt J Watson
More Article by Matt J Watson
Share
More Articles From MySQL
More Articles From MySQL
Related Articles and Readings
Beginners Guide to Music File Formats By: Gary Hendricks
Are you confused by the various types of music file formats out there? Most of you would have heard of the popular MP3 format, but are you aware there are other alternative digital music formats like WAV, WMA, RA and MIDI? Some of these give better sound quality than MP3 ...
Which File Type is Best for Your Graphics Sorting Through the Alphabet Soup of Jpgs Tifs and Pngs By: Jeremy Tuber
.jpg, .gif, .png, .eps, .pdf, .tif; file format options or a bad accident involving alphabet soup? Every time a graphic file is saved on the computer, the program saves the artwork with a specific file format. Some formats are high resolution, some are low; some are specific to ...
File Types For Icons Explained By: Juan Pablo Sarmiento
An application is not ready for use unless you have the proper icons for using into the application. Having the right icons for your application is not difficult if you have the help of a specialist or a specialized company to design the icons. The icon designer takes care of ...
The four core elements of secure file sharing By: Emil Walker
As file sharing with file hosting services becomes more commonplace, it is important to understand the four core elements of secure file sharing that are necessary when you send large files online. ...
Better Have File Cabinets Than Clutter By: Robert Thatcher
If you have tried locating a misplaced piece of paper for hours and have not succeeded or if you have scoured the stack of files locating one single document and was frustrated, this article is for you.The most common error for misplacing an object is dropping it at a place ...
The information provided in this article and/or the comments is the sole responsibility of their respective authors and does not necessarily reflect the opinion of ezinepost.com. ezinepost.com  does not endorse any article and/or comments published by our web users unless otherwise noted. 

Member Panel

login to submit articles and more

StatisticsEZINEPOST.COM

  • » Active Categories: 419
  • » Active Articles:252603
  • » Active Authors:31917
  • » Active Members: 38237
  • » Statistics Updated:
    - Tue Sep 1st, 2020 09:28AM EST