Home arrow MySQL arrow Page 5 - Blobbing Data With PHP and MySQL
MYSQL

Blobbing Data With PHP and MySQL


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.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 339
February 05, 2002
TABLE OF CONTENTS:
  1. · Blobbing Data With PHP and MySQL
  2. · Creating the database
  3. · Adding blobs to the database
  4. · The grabfile.php script
  5. · Displaying the files
  6. · The downloadfile.php script
  7. · Conclusion

print this article
SEARCH DEVARTICLES

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:

// Database connection variables

$dbServer = "localhost";

$dbDatabase = "myFiles";

$dbUser = "admin";

$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 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.

?>

<a href="http://www.devarticles.com"><img border="0" src="http://www.devarticles.com/dlogo.gif"></a>

<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["blobTitle"]; ?>

</font>

</td>



<td width="33%" bgcolor="#FFDCA8" height="21">

<p style="margin-left: 10">

<font face="Verdana" size="1">

<?php echo $row["blobType"]; ?>

</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["blobId"]; ?>">

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 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.

Take a look at the hyperlink for each table row:

<a href="downloadfile.php?blobId=<?php echo $row["blobId"]; ?>">

Download now

</a>


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:

The showfiles.php script after ive uploaded several files
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials