TUTORIAL: Upload Files To Database

[Redcore]

I noticed a different tutorial about uploading files to the server but I wanted to do one with more security stuff and with a different method. I personally like uploading files to a database better as it makes file management easier (delete, move, better control of who can download it, and you can store more information along with the file, like WHO uploaded it) plus people cannot execute files in the database as they can on your server (at least I have yet to hear of a case like this). I personally use MySQL, but there shouldn't be much of a difference between it and other databases. If you already have experience with PHP/MySQL, this should be real painless and suprisingly easy! If you don't, it's still much easier to grasp than uploading to a server (the 'tmp' folder stuff tends to throw people).

I'm going to do this tutorial as simply as I can - but you can add as much as you want...I'll talk a little bit about things you can do when I get to those parts!

NOTE: At the end of this tutorial is the resource documents if you want to just upload the completed documents to your server and go from there (that's how I do it too!).

We're going to use this to upload PICTURES.

You should have...
A) A server with PHP enabled
B) A MySQL database
C) Your connection settings (to connect to the database)

First off, let's prepare a connection file that we can include in ever page that needs to connect to the database.

1) CREATE THE CONNECTION SETTINGS FILE
We make an external PHP file and then include it so we don't have to paste the same thing in multiple pages or edit a bunch of pages if our connection settings change. This is a BIG deal if you have a huge site and suddenly have to change your password or something.

connect.php

PHP Code:

<?php
$server = ""; // aka "localhost" or "69.249.73.42"
$username = ""; // aka "ChuckNorris"
$password = ""; // aka "cankickyourass"
$database = ""; // where you want your new table to go

mysql_connect("$server", "$username", "$password") or die(mysql_error());
mysql_select_db("$database") or die(mysql_error());
?>

2) CREATE THE DATABASE TABLE "FILES"
A) fid - file id, set at auto-increment so the database will automatically know to go to the next ID number. I typically name my ID's with whatever is being added to the table...like for users, I use 'uid', etc.
B) name - whatever the file is named.
C) type - MIME type.
D) content - the actual file content.

You can add other fields if you want to tag more information onto the file record. On one of my sites, I have fields to track the category it's listed under, the person that uploaded it, and the date it was uploaded. To keep it simple, we're just going to do the basic stuff.

You might be a little confused asto how the file is physically stored to a database. What happens is the file's content is put into the content field through the BLOB (Binary Large Object) type. It's a part of the database now. The BLOB type we're going to use is MEDIUMBLOB which will allow up to 16 MB files. If you're thinking "HOLY COW! I don't want people to be uploading 16 MB files!" stress not my friend. We're going to throttle the file size and type later.

You can add this table in two ways...
A) a MySQL GUI (aka phpMyAdmin):
Go into phpMyAdmin > your database > click the "SQL" tab > paste this:

CREATE TABLE `files` (
`fid` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'unique id',
`name` VARCHAR( 30 ) NOT NULL COMMENT 'file name',
`type` VARCHAR( 30 ) NOT NULL COMMENT 'MIME type',
`size` INT( 11 ) NOT NULL COMMENT 'file size',
`content` MEDIUMBLOB NOT NULL COMMENT 'actual file'
) ENGINE = MYISAM COMMENT = 'Uploaded files'

B) or through a PHP file on your server:
addtable.php

PHP Code:

<?php
// connect to the database
include "connect.php";

$addtable = "CREATE TABLE `files` (
`fid` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'unique id',
`name` VARCHAR( 30 ) NOT NULL COMMENT 'file name',
`type` VARCHAR( 30 ) NOT NULL COMMENT 'MIME type',
`size` INT( 11 ) NOT NULL COMMENT 'file size',
`content` MEDIUMBLOB NOT NULL COMMENT 'actual file'
) ENGINE = MYISAM COMMENT = 'Uploaded files';";

mysql_query($addtable) or die(mysql_error());
echo "Successfully added table!";
?>

note: after you run this, delete it from your server!


Great, now we have a table setup and ready for our files! Now it's time to build our form.

3) CREATE UPLOAD FORM
Easy stuff!

form.html

Upload your file to the database...
<form action="upload.php" method="post" enctype="multipart/form-data" name="uploadform">
        <input type="hidden" name="MAX_FILE_SIZE" value="350000">
        <input name="picture" type="file" id="picture" size="50">
	<input name="upload" type="submit" id="upload" value="Upload Picture!">
</form>

Notice "enctype="multipart/form-data"" - this is vital to uploading the file...without it, the file will not upload. Also take note of the "MAX_FILE_SIZE" - it is set to 350000 bytes, which is 350KB. This is merely our first throttle to check how big the file is. This does not work on all browsers, so we'll have to double check that and other stuff in the next step in the process.

4) CREATE UPLOAD SCRIPT
Alright, roll up your sleeves...and let's get down to the nitty gritty!

A) First we need to make sure this file is to our requirements and kill it if it is not! Since it's a picture, we'll be checking the file size, type, and parameters. I'll try to note everything clearly within the code.

PHP Code:

<?php
// if something was posted, start the process...
if(isset($_POST['upload']))
{

// define the posted file into variables
$name = $_FILES['picture']['name'];
$tmp_name = $_FILES['picture']['tmp_name'];
$type = $_FILES['picture']['type'];
$size = $_FILES['picture']['size'];

// get the width & height of the file (we don't need the other stuff)
list($width, $height, $typeb, $attr) = getimagesize($tmp_name);
    
// if width is over 600 px or height is over 500 px, kill it    
if($width>600 || $height>500)
{
echo $name . "'s dimensions exceed the 600x500 pixel limit.";
echo ?> <a href="form.html">Click here</a> to try again. <? ;
die();
}

// if the mime type is anything other than what we specify below, kill it    
if(!(
$type=='image/jpeg' ||
$type=='image/png' ||
$type=='image/gif'
)) {
echo $type .  " is not an acceptable format.";
echo ?> <a href="form.html">Click here</a> to try again. <? ;
die();
}

// if the file size is larger than 350 KB, kill it
if($size>'350000') {
echo $name . " is over 350KB. Please make it smaller.";
echo ?> <a href="form.html">Click here</a> to try again. <? ;
die();
}

If this checks out, then the file is good to upload!

B) Now we extract the data from the file.

PHP Code:

// if your server has magic quotes turned off, add slashes manually
if(!get_magic_quotes_gpc()){
$name = addslashes($name);
}

// open up the file and extract the data/content from it
$extract = fopen($tmp_name, 'r');
$content = fread($extract, $size);
$content = addslashes($content);
fclose($extract); 


Now that we've dealt with the name and content variables, let's store this stuff into the database!

C) Store into the database.

PHP Code:

// connect to the database
include "connect.php";

// the query that will add this to the database
$addfile = "INSERT INTO files (name, size, type, content ) ".
           "VALUES ('$name', '$size', '$type', '$content')";

mysql_query($addfile) or die(mysql_error());

// get the last inserted ID if we're going to display this image next
$inserted_fid = mysql_insert_id();

mysql_close(); 


D) 3 ways to wrap this up...

1) Simply tell the user it is uploaded.

PHP Code:

echo "Successfully uploaded your picture!";
 
// we still have to close the original IF statement. If there was nothing posted, kill the page.
}else{die("No uploaded file present");
}
?> 


2) Head back to the form (where you could display "FILE UPLOADED" or whatever you want if you turn it into a PHP file and send it back with the proper variable).

PHP Code:

header( "Location: form.html");

// we still have to close the original IF statement. If there was nothing posted, kill the page.
}else{die("No uploaded file present");
}
?> 


3) Show the image right in this page

PHP Code:

// display the image
?>
<div align="center">
    <strong><? echo $name; ?><br>
    </strong><img name="<? echo $name; ?>" src="getpicture.php?fid=<? echo $inserted_fid; ?>" alt="Unable to view image #<? echo $inserted_fid; ?>">
    <br>
    <a href="form.html">upload more images</a>
</div>
<?
// we still have to close the original IF statement. If there was nothing posted, kill the page.
}else{die("No uploaded file present");
}
?>

Okay, so this page is done!

5) CREATE PAGE TO GET THE PICTURE
We're not QUITE done yet though! We have to build a page that extracts the data from the database, otherwise it's just stored there and we can't do anything with it...much less display our image.

PHP Code:

<?
if(isset($_GET['fid']))
{
// connect to the database
include "connect.php";

// query the server for the picture
$fid = $_GET['fid'];
$query = "SELECT * FROM files WHERE fid = '$fid'";
$result  = mysql_query($query) or die(mysql_error());

// define results into variables
$name=mysql_result($result,0,"name");
$size=mysql_result($result,0,"size");
$type=mysql_result($result,0,"type");
$content=mysql_result($result,0,"content");

// give our picture the proper headers...otherwise our page will be confused
header("Content-Disposition: attachment; filename=$name");
header("Content-length: $size");
header("Content-type: $type");
echo $content;

mysql_close();
}else{
die("No file ID given...");
}

?>

Now anytime you post the image tag...

<img src="getpicture.php?fid=1">

...it will display the image, as long as you give it the proper FID number.

THAT'S IT!

You can now upload pictures (and with some modification to this code, upload other files too) to your database and view them!

RESOURCES:
If you just skipped to the bottom and just want to install this on your server and go from there, here are the source documents...
connect.txt - save as connect.php
addtable.txt - save as addtable.php
form.txt - save as form.html
upload.txt - save as upload.php - NOTE: I used "wrap up" #3 above
getpicture.txt - save as getpicture.php

[jamiemac2005]

Hey Redcore, just thought i'd show my appreciation, i like the way this works, very nice tutorial aswell.

Cheers,
Jamey