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.
mysql_query("CREATE TABLE `yourdatabase`.`files` (`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.
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/
