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:

Next: The downloadfile.php script >>
 

More MySQL Articles
More By Mitchell Harper

Comments On: Blobbing Data With PHP and MySQL

>>> Be the FIRST to comment on this article!