Below you'll find the complete example script. It combines all the elements given above with some simple code that will list the files in the database and allow them to be deleted. As always, this file is available in the code archive.
As you look at the code, you may notice two spots where I've used this command:
header('location: '. $_SERVER['PHP_SELF']);
As described above, the header function sends an HTTP header to the browser. We've seen the content-type, content-length, and content-disposition headers already. The above line sends a location header, which redirects the browser to the specified URL. As it specifies $_SERVER['PHP_SELF'] as the URL, this line redirects the browser to the same page.
“What's the point of that?” you might wonder. Well, I use this line after the script adds or deletes a file in the database. In such cases, the action—add or delete—is specified by a query string in the page's URL. If the script simply took the requested action and immediately displayed the updated list of files, the user could inadvertently repeat that action by refreshing the page! To avoid this kind of error, we instead redirect the browser after it completes each action to a URL that's identical in every way, except that it doesn't contain a query string ($_SERVER['PHP_SELF']). Thus, the user can refresh the file list that's produced with no ill effects.
<?php
$dbcnx = mysql_connect('localhost', 'root', 'mypasswd');
mysql_select_db('dbName');
$action = '';
if (isset($_GET['action'])) $action = $_GET['action'];
if (($action == 'view' or $action == 'dnld')
and isset($_GET['id'])) {
$id = $_GET['id'];
// User is retrieving a file
$sql = "SELECT FileName, MimeType, FileData
FROM filestore WHERE ID = '$id'";
$result = @mysql_query($sql);
if (!$result) die('Database error: ' . mysql_error());
$file = mysql_fetch_array($result);
if (!$file)
die('File with given ID not found in database!');
$filename = $file['FileName'];
$mimetype = $file['MimeType'];
$filedata = $file['FileData'];
$disposition = 'inline';
if ($action == 'dnld') {
$disposition = 'attachment';
if (strpos($_SERVER['HTTP_USER_AGENT'],'MSIE 5') or
strpos($_SERVER['HTTP_USER_AGENT'],'Opera 7'))
$mimetype = 'application/x-download';
}
header("content-disposition: $disposition; filename=$filename");
header("content-type: $mimetype");
header('content-length: ' . strlen($filedata));
echo($filedata);
exit();
} elseif ($action == 'del' and isset($_GET['id'])) {
$id = $_GET['id'];
// User is deleting a file
$sql = "DELETE FROM filestore WHERE ID = '$id'";
$ok = @mysql_query($sql);
if (!$ok) die('Database error: ' . mysql_error());
header('location: ' . $_SERVER['PHP_SELF']);
exit();
} elseif ($action == 'ulfile') {
// Bail out if the file isn't really an upload.
if (!is_uploaded_file($_files['uploadfile']['tmp_name']))
die('There was no file uploaded!');
$uploadfile = $_files['uploadfile']['tmp_name'];
$uploadname = $_files['uploadfile']['name'];
$uploadtype = $_files['uploadfile']['type'];
$uploaddesc = $_POST['desc'];
// Open file for binary reading ('rb')
$tempfile = fopen($uploadfile,'rb');
// Read the entire file into memory using PHP's
// filesize function to get the file size.
$filedata = fread($tempfile,filesize($uploadfile));
// Prepare for database insert by adding backslashes
// before special characters.
$filedata = addslashes($filedata);
// Create the SQL query.
$sql = "INSERT INTO filestore SET
FileName = '$uploadname',
MimeType = '$uploadtype',
Description = '$uploaddesc',
FileData = '$filedata'";
// Perform the insert.
$ok = @mysql_query($sql);
if (!$ok) die('Database error storing file: ' .
mysql_error());
header('location: ' . $_SERVER['PHP_SELF']);
exit();
}
// Default page view: lists stored files
$sql = 'SELECT ID, FileName, MimeType, Description
FROM filestore';
$filelist = @mysql_query($sql);
if (!$filelist) die('Database error: ' . mysql_error());
?>
<l>
<head>
<title> PHP/MySQL File Repository </title>
</head>
<body>
<h1>PHP/MySQL File Repository</h1>
<form action="'PHP_SELF']?>?action=ulfile"
method="post" enctype="multipart/form-data">
<p>Upload File:<br />
<input type="file" name="uploadfile" /></p>
<p>File Description:<br />
<input type="text" name="desc" maxlength="255" /></p>
<p><input type="submit" name="go" value="Upload" /></p>
</form>
<p>The following files are stored in the database:</p>
<table width="85%" border="0" cellpadding="0"
cellspacing="0">
<tr>
<th align="left">Filename</th>
<th align="left">Type</th>
<th align="left">Description</th>
</tr>
<?php
if (mysql_num_rows($filelist) > 0) {
while ($f = mysql_fetch_array($filelist)) {
?>
<tr valign="top">
<td nowrap>
<a href="'PHP_SELF']?>?action=view&id=<?=$f['ID']?>"
><?=$f['FileName']?></a>
</td>
<td nowrap><?=$f['MimeType']?></td>
<td><?=$f['Description']?></td>
<td nowrap>
[<a href="'PHP_SELF']?>?action=dnld&id=<?=$f['ID']?>
">Download</a> |
<a href="'PHP_SELF']?>?action=del&id=<?=$f['ID']?>"
onClick="return confirm('Delete this file?');"
>Delete</a>]
</td>
</tr>
<?php
}
} else {
?>
<tr><td colspan="3" align="center">No Files!</td></tr>
<?php
}
?>
</table>
</body>
<l>
This example demonstrates all the techniques you need in order to juggle binary files with PHP and MySQL, and I invite you to think of some creative uses of this code. Consider, for example, a file archive where users must provide a user name and password before they are allowed to view or download the files. If a user enters an incorrect user name/password combination, your script can display an error page instead of sending the file data. Another possibility would be a script that sends different files depending on the details provided by the form.