It seems that the latest and greatest use for databases is 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 Mitchell shows us how to create a binary file repository using PHP and MySQL that can store and retrieve several different file types.
Blobbing Data With PHP and MySQL - Displaying the files (Page 5 of 7 )
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 showfiles.php and enter the code described below into it:
or die("Couldn't connect to database $dbDatabase");
$dbQuery = "SELECT blobId, blobTitle, blobType ";
$dbQuery .= "FROM myBlobs ";
$dbQuery .= "ORDER BY blobTitle 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 blobData 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.
We use a while loop to output a new table row for each record in the myBlobs table. The mysql_fetch_array function allows us to refer to each field by its name. We refer to the blobId field as $row["blobId"], for example.
The link is to a file named downloadfile.php, which accepts one query string argument called blobId. This file will return the binary data field blobData from our myBlobs table, and actually stream the data for that file to the user. We will create this script in a minute.
When I've uploaded five files using the uploadfile.php script, the output from the showfiles.php file look like this in my browser: