Blobbing Data With PHP and MySQL


Blobbing Data With PHP and MySQL

Databases can be used for storing large amounts of binary data, known as BLOB’s. These BLOB’s can store just about any type of data imaginable, including MS Word documents, GIF/JPEG images, PDF files, MP3’s, etc. In this article we will see how to create a binary file repository using PHP and MySQL that can store and retrieve several different file types.

In this article we’re going to take a look at how to create a binary file repository using PHP and MySQL that can store several different file types. We will look at how to store each files binary data in a database, and then how to retrieve it… all using PHP.

To test the examples in this article, you should have access to an web server running PHP and MySQL. I will assume that you have administrative rights to create the MySQL databases, and that MySQL is on the same server as PHP.

Creating the database

Our document repository will use one database containing one table to store its data. Using the MySQL console application (c:mysqlbinmysql.exe for Win32 or /usr/bin/mysql for *nix users), create our database and switch to it with the following commands:

create database FilesRepo;
use FilesRepo;

Now let’s create the table that will actually store our BLOB’s as part of our FilesRepo database:

create table FileData
(
fileId int auto_increment not null,
fileTitle varchar(50),
fileData longfile,
fileType varchar(50),
fileSize int,
primary key(fileId),
unique id(fileId)
);

We now have one database named FilesRepo, which contains one table named FileData. The details of each field in the FileData table are shown below:

  • fileId: An integer that will provide us with a unique numerical identifier for each file (ie 1, 2, 3, etc). It is incremented and tracked internally by MySQL whenever we add a new record.
  • fileTitle: A description of each file in the table, for example “My word document about golf”, or “Picture of the sky”. Servers no real purpose, but will come in handy when we are viewing our files in through a web page later on.
  • fileData: A binary field that will hold the contents of each file that we wish to store in the database. In our table, we have used the longfile data type, which can hold up to 4,294,967,295 characters. Other binary field types include mediumfile (which can hold up to 16,777,215 bytes), file (which can hold up to 65535 characters), and tinyfile (which can hold up to 255 characters).
  • fileType: As you will see shortly, each different file type (such as .doc, .gif, .pdf, etc) has its own unique content type. When a file is uploaded via a web browser to a web server (which we will look at shortly), the browser cleverly passes that files type as part of its headers. We will be storing this type in the table so that we can choose to view only specific types of files from our FileData table. Content types are simple strings. The content type for a MS word document is “application/msword”, the content type for a GIF image is “image/gif”, etc.
  • fileSize: Field stores the size of each file we store in the database. In our table, we have used the bigint data type, which can hold up to 18,446,744,073,709,551,615 value.

Adding files to the database

Now that we’ve got our table structure sorted out, let’s use a simple PHP script to capture files from a users computer and store them in our database. Create a new PHP script named uploadfiles.php and save it into a directory that your web server can process. Enter the following code into uploadfiles.php:

<html>
<head>
<title> Upload a File </title>
</head>
<body bgcolor="#FFFFFF">
<form enctype="multipart/form-data" name="frmUploadFile" action="pufile.php" method="post">
<table border="0" cellpadding="0" cellspacing="0" bordercolor="#111111" width="100%">
<tr>
<td width="100%" bgcolor="#FF9900" height="22" colspan="2">
<p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
Upload a File</font></b></td>
</tr>
<tr>
<td width="100%" bgcolor="#FFE3BB" colspan="2">
<p style="margin-left: 10; margin-right: 10"><font face="Verdana" size="2">
<br>Please select a file from your local computer to upload to our web server
for saving in our database. This file can be of any type you like. Once you
have chosen a file, please click on the &quot;Upload this file&quot; button below.&nbsp;
&nbsp;<br>&nbsp;</font></td>
</tr>
<tr>
<td width="15%" bgcolor="#FFE3BB">
<p style="margin-left: 10"><font face="Verdana" size="2">
File Description:</font></td>
<td width="85%" bgcolor="#FFE3BB">
<input type="text" name="strDesc" size="20" maxlength="50"></td>
</tr>
<tr>
<td width="15%" bgcolor="#FFE3BB">
<p style="margin-left: 10"><font face="Verdana" size="2">File Location:</font></td>
<td width="85%" bgcolor="#FFE3BB">
<font face="Verdana" size="2">
<input type="file" name="fileUpload" size="20"></font></td>
</tr>
<tr>
<td width="33%" bgcolor="#FFE3BB">
<p style="margin-left: 10"><font face="Verdana" size="2">
<br>
<br>
&nbsp;</font></td>
<td width="67%" bgcolor="#FFE3BB">
<font face="Verdana" size="2">
<input type="submit" value="Upload this file" name="cmdSubmit"></font></td>
</tr>
</table>
</form>
</body>
</html>

In the page above, we’ve created a HTML form that allows us to send files to the server. It allows the user to choose a file to upload, and to also enter a description for that file. The only difference between the form above and any other HTML form, is that the enctype attribute of our form is set to “multipart/form-data”:

<form enctype="multipart/form-data" name="frmUploadFile" action="putfile.php" method="post">

This tells the browser that we may be sending files from the client’s machine to the server, so it should be ready to prepare the headers and stream the files to the server, amongst other things.

Our form will post its results to putfile.php, which will be responsible for adding the file to the database as a BLOB. Let’s create that file now.

The putfile.php script

Before we actually process the uploaded file as a BLOB, let’s first make sure that PHP has the capabilities to handle uploaded files. Create a new PHP script named info.php and enter the following line into it:

<?php phpinfo(); ?>

Run the script in your browser. It displays the details of your PHP configuration. There are three variables that we need to look for:

  • file_uploads: Tells PHP whether or not to accept file uploads. Must have the value “On” to accept files.
  • upload_max_filesize: Tells PHP the maximum file size that an file uploaded can be. Is specified as [Number of megabytes]M, for example 2M for two megabytes.
  • upload_tmp_dir: The directory where PHP will store uploaded files until they are copied/moved. If empty, PHP stores them in the operating systems default temporary directory.

If you need to change any of these options, you can do so in PHP’s configuration file. Anyhow, let’s get back to our putfile.php script. Create a new file named putfile.php and paste each line of code shown below into it. We will run through the code line by line:

<?php

// PutFile.php: Takes the details
// of the new file posted as part
// of the form and adds it to the
// FileData table of our FilesRepo DB.

$strDesc = $_POST["strDesc"];
$fileUpload_name = $_FILES["file"]["name"];
$fileUpload_size = $_FILES["file"]["size"];
$fileUpload_type = $_FILES["file"]["type"];

We start by declaring four variables. When we pass any form values to a PHP script, PHP automatically makes the name of that element available to us as a POST variable. So, for example, if I have a form element like this:

<input type="text" name="myName" value="Mohan Cheema">

… then if I posted this form to a PHP script using the post method, PHP would automatically create a variable named $_POST[“myName”], containing the value “Mohan Cheema”. Form values are also stored in arrays, depending on the method used to post the form (get/post). For forms posted using the get method, they are stored in the associative array, $_GET, so I would access my form element like this:

echo $_GET["myName"];

For forms posted using the post method, that forms elements are stored in the $_POST associative array:

echo $_POST["myName"];

The other four global variables that I’ve defined hold the details of our uploaded file. They are automatically created by PHP. Details of each of these variables are shown below:

  • $fileUpload: Contains the full path to the temporary file that PHP has stored our uploaded file in. On my Linux server, it looks like this: “/tmp/php29F7.tmp”.
  • $fileUpload_name: The name of the file that we have uploaded, for example “myimage.gif”.
  • $fileUpload_size: The size of the file that we have uploaded, in bytes.
  • $fileUpload_type: The content type of the file that we have uploaded, such as “image/gif” for a GIF image.

Next, we make sure that the user has entered both a file description and a file name (notice how the $fileUpload variable contains the value “none” when a file hasn’t been uploaded):

// Make sure both a description and
// file have been entered

if(empty($strDesc) || $fileUpload_name == "")

die("You must enter both a description and file");

Obviously, we will be connecting to, and querying our database to add our file to the FileData table. We define the connection details for our database as variables:

// Database connection variables

$dbServer = "localhost";
$dbDatabase = "FilesRepo";
$dbUser = "root";
$dbPass = "password";

PHP has several built-in functions that allow us to open and read files. We use the fopen and fread methods to open the uploaded file from the local directory on the web server, and then read its contents into a variable. The addslashes method escapes any apostrophises and double quotes in the file:

$fileHandle = fopen($fileUpload, "r");
$fileContent = fread($fileHandle, $fileUpload_size);
$fileContent = addslashes($fileContent);

We connect to our database using PHP’s built-in MySQL functions in combination with our database connection variables that we defined above:

$sConn = mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");

$dConn = mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");

Once connected to the MySQL database, we run an insert query to actually add the details of our uploaded file (as a file) to the FilesRepo table:

$dbQuery = "INSERT INTO FileData VALUES ";
$dbQuery .= "(0, '$strDesc', '$fileContent', '$fileUpload_type', $fileUpload_size)";

mysql_query($dbQuery) or die("Couldn't add file to database");

If the mysql_query function didn’t succeed, then we our script calls the die function, which stops the execution of our script and outputs “Couldn’t add file to database” to the clients browser. On the other hand, if the mysql_query function succeeded, then we output the details of the uploaded file to the browser:

echo "<h1>File Uploaded</h1>";
echo "The details of the uploaded file are shown below:<br><br>";
echo "<b>File name:</b> $fileUpload_name <br>";
echo "<b>File type:</b> $fileUpload_type <br>";
echo "<b>File size:</b> $fileUpload_size <br>";
echo "<b>Uploaded to:</b> $fileUpload <br><br>";
echo "<a href='uploadfile.php'>Add Another File</a>";

A file repository involves being able to both upload and download files, so let’s create a PHP script that will list all of the files in our FileData table. The script will also give us the option to download each of these files.

Displaying the files

Let’s create a PHP script that will display the details of each of our files in a HTML table with a link to download each one. Create a new file named displayfiles.php and enter the code described below into it:

// Database connection variables

$dbServer = "localhost";
$dbDatabase = "FilesRepo";
$dbUser = "root";
$dbPass = "password";

$sConn = mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");

$dConn = mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");

$dbQuery = "SELECT fileId, fileTitle, fileType ";
$dbQuery .= "FROM FileData ";
$dbQuery .= "ORDER BY fileTitle ASC";

$result = mysql_query($dbQuery) or die("Couldn't get file list");

We start of by connecting to our database and using a select query to get the id, title and type of each file in our database. Notice how I haven’t used “SELECT *” in the select query? If I did, then MySQL would return the fileData field for each record as well, which would make our query extremely slow and inefficient because we don’t actually need that field for our script.

?>

<table border="1" cellpadding="0" cellspacing="0" bordercolor="#111111" width="100%">
<tr>
<td width="34%" bgcolor="#FF9900" height="21">
<p style="margin-left: 10"><b><font size="2" face="Verdana" color="#FFFFFF">
Description</font></b></td>
<td width="33%" bgcolor="#FF9900" height="21">
<p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
Type</font></b></td>
<td width="33%" bgcolor="#FF9900" height="21">
<p style="margin-left: 10"><b><font face="Verdana" size="2" color="#FFFFFF">
File</font></b></td>
</tr>

<?php

As mentioned above, we will be displaying each file as a table row. The HTML code above creates this table.

while($row = mysql_fetch_array($result))
{
?>
<tr>
<td width="34%" bgcolor="#FFDCA8" height="21">
<p style="margin-left: 10; margin-right: 10">
<font face="Verdana" size="1">
<?php echo $row["fileTitle"]; ?>
</font>
</td>
<td width="33%" bgcolor="#FFDCA8" height="21">
<p style="margin-left: 10">
<font face="Verdana" size="1">
<?php echo $row["fileType"]; ?>
</font>
</td>
<td width="33%" bgcolor="#FFDCA8" height="21">
<p style="margin-left: 10"><font face="Verdana" size="1">
<a href="downloadfile.php?fileId=<?php echo $row["fileId"]; ?>">
Download now
</a></font>
</td>
</tr>
<?php
}
echo "</table>";
?>

We use a while loop to output a new table row for each record in the FileData table. The mysql_fetch_array function allows us to refer to each field by its name. We refer to the fileId field as $row[“fileId”], for example.

Take a look at the hyperlink for each table row:

<a href="downloadfile.php?fileId=<?php echo $row["fileId"]; ?>">
Download now
</a>

The link is to a file named downloadfile.php, which accepts one query string argument called fileId. This file will return the binary data field fileData from our FileData table, and actually stream the data for that file to the user. We will create this script in a minute.

The downloadfile.php script

As mentioned, when we click on the “Download now” link, the downloadfile.php script will be called up, passing the id of the file requested as the fileId query string value. Create a new file named downloadfile.php and enter the following code into it:

<?php

global $fileId;

if(!is_numeric($fileId))
die("Invalid fileId specified");

// Database connection variables

$dbServer = "localhost";
$dbDatabase = "FilesRepo";
$dbUser = "root";
$dbPass = "password";

$sConn = mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");

$dConn = mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");

$dbQuery = "SELECT fileType, fileSize, fileData ";
$dbQuery .= "FROM FileData ";
$dbQuery .= "WHERE fileId = $fileId";

$result = mysql_query($dbQuery) or die("Couldn't get file list");

if(mysql_num_rows($result) == 1)
{
$fileType = @mysql_result($result, 0, "fileType");
$fileSize = @mysql_result($result, 0, "fileSize");
$fileContent = @mysql_result($result, 0, "fileData");
header("Content-type: $fileType");
header("Content-length: $fileSize");
echo $fileContent;
}
else
{
echo "Record doesn't exist.";
}
?>

In the code above we use the query string variable $fileId to retrieve the fileType, fileSize and fileData fields from our FileData database. If there was a row returned from our query, then we save its type and actual data to three variables, called $fileType, $fileSize and $fileContent respectively.

We use the header function to change the type of content that we are sending to the browser. The default content type is text/html, which the browser assumes is a web page. If we didn’t include a call to the header function, then when we output our binary data it will look like garbage in the browser. Second we use fileSize to indicate the transfer-length of the file. Lastly, we use the echo command to actually output the contents of our binary file to the browser, which handles the file as it sees fit.
Conclusion
As you can see from the examples and scripts in this article, storing files as binary data in a MySQL database isn’t too hard to accomplish.

If you’re curious about BLOB’s, or in need of a script similar to the one described in this article, then feel free to take it and modify it to your hearts content.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


CommentLuv badge