Retrieving files from MySQL when displayed in a table

[Jayno77]

Hi there

I am relatively new to PHP and MySQL and would be really grateful if someone could assist me with my problem!

Here goes:

I have created a MySQL table which allows users to upload files. What I would like to do is to display the contents of the MySQL table in table format in PHP which I have managed to achieve with some help from these forums: here is the code:

===========================================================
<?PHP
$db = mysql_connect("MYSQL SERVER","MY USER NAME","MY PASSWORD");
mysql_select_db("DATABASE NAME");
$query = "SELECT * FROM pictures ORDER BY id ";
$result = mysql_query($query) or die ("There are no resurces in this section");
$numofrows = mysql_num_rows($result);
?>

<?PHP
echo "<TABLE BORDER=\"1\" cellpadding=\"3\" >";

echo "<TR bgcolor=\"lightblue\"><TD>Title</TD><TD>Description</TD><TD>Submitted by</TD></TR>\n";

for($i = 0; $i < $numofrows; $i++) {

$row = mysql_fetch_array($result);
if($i % 2) {

echo "<TR bgcolor=\"white\">\n";

} else {
echo "<TR bgcolor=\"white\">\n";

}

echo "<TD>".$row['title']."</TD><TD>".$row['description']."</TD><TD>".$row['name']."</TD></Tr>\n";

echo "</TR>\n";
}

echo "</TABLE>\n";
?>
===========================================================

What I am looking to achieve is to link the 'TITLE' to the actual file so that it can be retrieved in the visitors browser. I can do this when the results are not displayed in a table but cannot figure out how to do it when they are!

I did this by using this bit of code:

===========================================================
{
$sql = "SELECT filetype,filedata FROM pictures ";
$sql .= "WHERE id=${_REQUEST['id']};";
$query = mysql_query($sql);
$results = mysql_fetch_array($query);
header("Content-Type: " . $results['filetype']);
echo base64_decode($results['filedata']);
} else
echo "No Picture Specified.";
?>
===========================================================

and linking it to my ORIGINAL display page which was as follows:

===========================================================
$sql = "SELECT id,title,description,name FROM pictures";
$query = mysql_query($sql) or die("No Pictures");
while ($results = mysql_fetch_array($query))
{
echo "Resource: ${results["title"]}<br>Posted by: ${results["name"]}";
echo "<br>Description: ${results["description"]}<br>";
echo "<a href=CLL Download.php?id=${results["id"]}>View</a>";
echo "<br><br>";
}
?>
===========================================================

How can I get the same result when using a table to display the data?

Any help would be gratefully received and I apologise if I've put too much info in this msg - not sure what you would need

Many thanks
Jayno77

[mcibor]

What I would recommend is to not store the files in db. Store them in some folder and in the db just description, and url

$result = mysql_query("SELECT url, description, type FROM table");
while($row = mysql_fetch_assoc($result)){
if($row['type'] == 'img') echo "<a href=\"" .$row['url']. "\"><img src=\"" .$row['url']. "\" alt=\"" .$row['desctiption']. "\"></a>";
else echo "<a href=\"" .$row['url']. "\">" .$row['description']. "</a>";
}

hope this helps
Michal Cibor

[Jayno77]

Thanks MCIBOR

After a bit (or should I say a LOT) of tweaking and delving on the net I have managed to sort my code so that there is a link in the PHP table to the file stored in the database.

Thank you very much for your speedy response though - I was thinking I was going to have to resort to storing the files in a folder on the server but luckily have it sorted.

Cheers
Jayno77