thoughts,
It is fairly easy to create a server-side application to upload Word
files (or any file type) into a MySQL database table's BLOB field, as
well as to display those files on command using PHP. In fact, there
is an article on ONLamp.com called "Uploading, Saving and Downloading
Binary Data in a MySQL Database" written by Joao Prado Maia that
describes the procedure this step-by-step at
http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html Using this
sample, I will guide you through the steps necessary for your needs of
uploading, viewing, and downloading Word files.
First, look at the material on Page 1 regarding setting up the database:
http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html?page=1
This describes how to log onto the MySQL server and configure your
database and table. Since Word files can be rather large, I would
recommend that you use the MEDIUMBLOB datatype for storing Word files
(unless you anticipate CV's larger than 16MB, then you should use
LONGBLOB)
Once you have created your database and table, you can test it with
the following script, substituting "localhost", "binary_user", and
"binary_password" for the server, username, and password you will be
using:
<?php
$conn = mysql_connect("localhost", "binary_user", "binary_password")
or die("It doesn't connect!");
mysql_select_db("binary_files", $conn) or die(mysql_errno() . ": " .
mysql_error() . "<br>");
$today = date("Y-m-d", time());
$sql = "SELECT DAYOFMONTH('$today')";
$result = mysql_query($sql, $conn);
echo "The day of the month is " . mysql_result($result, 0, 0) . ".";
?>
Next, proceed to page 2 to create the Upload scripts:
http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html?page=2
Use the following two pieces of code, open_db.inc and add.php, to
create the upload script for sending Word documents to the database.
open_db.inc contains the database connection information, while
add.php contains all of the code necessary for uploading to the
database:
open_db.inc:
<?php
$db = mysql_connect("localhost", "binary_user", "binary_password");
mysql_select_db("binary_files", $db) or die(mysql_errno() . ": " .
mysql_error() . "<br>");
?>
add.php:
<?php
if ($action == "upload") {
// ok, let's get the uploaded data and insert it into the db now
include "open_db.inc";
if (isset($binFile) && $binFile != "none") {
$data = addslashes(fread(fopen($binFile, "r"), filesize($binFile)));
$strDescription = addslashes(nl2br($txtDescription));
$sql = "INSERT INTO tbl_Files ";
$sql .= "(description, bin_data, filename, filesize, filetype) ";
$sql .= "VALUES ('$strDescription', '$data', ";
$sql .= "'$binFile_name', '$binFile_size', '$binFile_type')";
$result = mysql_query($sql, $db);
mysql_free_result($result); // it's always nice to clean up!
echo "Thank you. The new file was successfully added to our database.<br><br>";
echo "<a href='main.php'>Continue</a>";
}
mysql_close();
} else {
?>
<HTML>
<BODY>
<FORM METHOD="post" ACTION="add.php" ENCTYPE="multipart/form-data">
<INPUT TYPE="hidden" NAME="MAX_FILE_SIZE" VALUE="1000000">
<INPUT TYPE="hidden" NAME="action" VALUE="upload">
<TABLE BORDER="1">
<TR>
<TD>Description: </TD>
<TD><TEXTAREA NAME="txtDescription" ROWS="10" COLS="50"></TEXTAREA></TD>
</TR>
<TR>
<TD>File: </TD>
<TD><INPUT TYPE="file" NAME="binFile"></TD>
</TR>
<TR>
<TD COLSPAN="2"><INPUT TYPE="submit" VALUE="Upload"></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
<?php
}
?>
Of course, you can change the HTML code within to provide the desired
style to this page.
Finally, go to page 3 to learn how to view and download these documents:
http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html?page=3
This contains two more scripts, main.php for displaying a list of all
files in the database, and download.php for displaying the contents of
a file. Note that these files make use of the previously created
open_db.inc file, so they must either be placed in the same directory
as these files, or the paths must be changed to point to the place
where open_db.inc is located.
main.php:
<?php
include "open_db.inc";
$sql = "SELECT * FROM tbl_Files ";
$sql .= "ORDER BY filename ASC";
$result = mysql_query($sql, $db);
$rows = mysql_num_rows($result);
echo "<table>\n";
echo " <tr>\n";
echo " <td>Filename</td>\n";
echo " <td>Type</td>\n";
echo " <td>Size</td>\n";
echo " <td>Description</td>\n";
echo " <td> </td>\n";
echo " </tr>\n";
for ($i = 0; $i < $rows; $i++) {
$data = mysql_fetch_object($result);
// since our script is very small, i'm not going to escape out to html mode here
echo " <tr>\n";
echo " <td>$data->filename</td>\n";
echo " <td>$data->filetype</td>\n";
echo " <td>$data->filesize</td>\n";
echo " <td>" . stripslashes($data->description) . "</td>\n";
echo " <td>( <a href='download.php?id=$data->id_files'>Download</a> )</td>\n";
echo " </tr>\n";
}
mysql_free_result($result);
mysql_close($db);
?>
<?php
if ($id_files) {
include "open_db.inc";
$sql = "SELECT bin_data, filetype, filename, filesize FROM tbl_Files
WHERE id_files=$id_files";
$result = @mysql_query($sql, $db);
$data = @mysql_result($result, 0, "bin_data");
$name = @mysql_result($result, 0, "filename");
$size = @mysql_result($result, 0, "filesize");
$type = @mysql_result($result, 0, "filetype");
header("Content-type: $type");
header("Content-length: $size");
header("Content-Disposition: attachment; filename=$name");
header("Content-Description: PHP Generated Data");
echo $data;
}
?>
Source cited:
-------------
OnLamp PHP Dev Center:
http://www.onlamp.com/php/
Google search terms used:
-------------------------
PHP Mysql upload binary database |
