Fetching files from MySQL - linking problem

predator

I have a page that is supposed to fetch all my powerpoint-files that I have in my database and echo out a link for everyone of them.

How should I do this?

MY DB STRUCTURE

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(4)       |      | PRI | NULL    | auto_increment |
| name      | varchar(50)  | YES  |     | NULL    |                |
| file_desc | varchar(200) | YES  |     | NULL    |                |
| file_type | varchar(60)  | YES  |     | NULL    |                |
| file_size | varchar(50)  | YES  |     | NULL    |                |
| file_data | longblob     | YES  |     | NULL    |                |
| kid       | int(4)       | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

THIS IS HOW MY PAGE LOOKS LIKE

<?
require("dbmanager.php");
$query = "SELECT * FROM powerpoint"; 
$result = @mysql_query($query); 
$data = @mysql_result($result,0,"file_data"); 
$type = @mysql_result($result,0,"file_type"); 
$id = @mysql_result($result,0,"id");
$file_name = @mysql_result($result,0,"name"); 
Header( "Content-type: $type"); 
echo $data; 
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
</body>
</html>

I would assume I'd need a for query or so and perhaps echo "<a href=\"$self?id=$id\">$name</a>"; or something similar... but I'm not sure...

thanks for taking your time!

douceur

I think something like this might work for what you're saying:

<?
$sql = "SELECT * FROM powerpoint"; 
$result = mysql_query($sql); 
$data = @mysql_fetch_array($result); 
?>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?
foreach ($data as $ppfile) {
  echo "<a href=\"{$_SERVER['PHP_SELF']}?id={$ppfile['$id']}\">{$ppfile['name']}</a><br>";
}
?>
</body>
</html>

DigitalExpl0it

the power point is being inserted into the database correctly right?

predator

douceur,

I pasted your code... but it got really weird. Is "your" way possible when storing the files binary?

When I run it, it echoes out "1
1
c
c
t
t
a
a
1
1
Ð
Ð
1
1"
all are linked but lead nowhere...

DigitalExpl0it
yeah I think so (was also assisted by another poster here at phpbuilder). I store the files binary (in this case it is more effective).
The page that does the inserting looks like this.

<?
require("dbmanager.php");
?>
<HTML>
<HEAD><TITLE>Storing powerpoints</TITLE>
<SCRIPT LANGUAGE="JavaScript" TYPE="text/JavaScript">
function koll()
	{
		if (document.getElementById('form_data').value.length<=4)
		{
		alert('Du måste välja en fil!');
		return false;
		}
		return true;
	}
</SCRIPT>
</HEAD>
<BODY>
<br>
<?php

//INSERTING THE DATA
if ($submit) {
    $kid = $_POST['kategori'];
    $data = addslashes(fread(fopen($form_data, "r"), filesize($form_data)));

if ($form_data_type=="application/vnd.ms-powerpoint"){

$result=MYSQL_QUERY("INSERT INTO powerpoint (name,file_desc,file_type,file_size,file_data,kid) ".
    "VALUES ('$form_data_name','$form_description','$form_data_type','$form_data_size','$data', '$kid')");

$id= mysql_insert_id();
print "<p>Du har lagt in en presentation i databasen med id: <b>$id</b><br><br><a href=\"javascript:history.go(-1)\"><-Tillbaka</a>";

}else{ 
echo "<p>Du kan endast lägga in powerpoints<br><br><a href=\"javascript:history.go(-1)\"><-Tillbaka</a></p>";
} 


} else {


//THE FORM WHERE I SELECT A FILE TO BE UPLOADED   

?><form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data" onsubmit="return koll()">
    Kort beskrivning av filen:<br>
    <input type="text" name="form_description"  size="40">
    <INPUT TYPE="hidden" name="MAX_FILE_SIZE" value="1000000">
    <br>Filen du vill ska laddas up:<br>
    <input type="file" name="form_data"  size="40"><br>
	  Kategori:<br>
  <select name="kategori" id="kategori">   

  <?
    $kat = "SELECT * FROM kategori ORDER BY kategori ASC";
	$result2 = mysql_query($kat,$db_link); 
	$rows = mysql_num_rows($result2); 

$lID = 0;
$lname ="";


	for($index = 0; $index< $rows; $index++) { 
    $lID = mysql_result($result2,$index,"kid"); 
    $lname = mysql_result($result2,$index,"kategori");



   ?>
  <option value="<? echo "$lID";?>"><? echo"$lname";

 }?>

 </option>
 </select>
      <p><input type="submit" name="submit" value="submit">
    </form>

<?php
}
?>

</BODY>
</HTML>

douceur

Welp, I don't know if this was the source of the problem or not, but I see an error in my code. The one line should be:

echo "<a href=\"{$_SERVER['PHP_SELF']}?id={$ppfile['id']}\">{$ppfile['name']}</a><br>";

Let me know if that makes any difference.

predator

no, it did not make a difference.
What strikes me as somewhat odd is that I have only two files in the db and yet that code echoes out like 10-15 lines ... with weird characters. Something has to be wrong... hmmmm

douceur

You should just try this to see what it gives you:

<?
$sql = "SELECT * FROM powerpoint";
$result = mysql_query($sql);
$data = mysql_fetch_array($result);

echo '<pre>';
print_r ($data);
echo '</pre>';
?>

predator

it gives me the following

Array
(
    [0] => 1
    [id] => 1
    [1] => chris_grattan.ppt
    [name] => chris_grattan.ppt
    [2] => test-fil
    [file_desc] => test-fil
    [3] => application/vnd.ms-powerpoint
    [file_type] => application/vnd.ms-powerpoint
    [4] => 14336
    [file_size] => 14336
    [5] => ALOT OF BINARY DATA HERE
    [6] => 1
    [kid] => 1
)

seems correct to me

douceur

Oh..... Duh, duh, duh... Obvious mistake in my code..

<?
$sql = "SELECT * FROM powerpoint"; 
$result = mysql_query($sql); 
while($data = mysql_fetch_array($result)) {
  echo "<a href=\"{$_SERVER['PHP_SELF']}?id={$data['id']}\">{$data['name']}</a><br>";
}
?>

Let me know if that works a bit better. 🙂

predator

oh yes. It now prints the two powerpoints and I think they are correctly linked.

What is weird however... is that when I click on the link it loads the page but nothing happens (it does not open the powerpoint presentation). And if I right click and chose "Save target" it saves it as a .htm and not a .ppt .

douceur

Just as it should. lol. That's where the {$_SERVER['PHP_SELF']} comes in. It is taking you to exactly the same page. I thought that was what you wanted when in your original code, you included a '$self' variable.

I'm not exactly sure how to extract the file from the database... This might work:

<?
if ($id) {
  $sql = "SELECT * FROM powerpoint WHERE `id` = '$id'";
  $result = mysql_query($sql); 
  $data = mysql_fetch_array($result);
  Header("Content-type: {$data['file_type']}");
  echo $data['file_data'];
}

$sql = "SELECT * FROM powerpoint"; 
$result = mysql_query($sql); 
while($data = mysql_fetch_array($result)) {
  echo "<a href=\"{$_SERVER['PHP_SELF']}?id={$data['id']}\">{$data['name']}</a><br>";
}
?>

predator

excellent! you have helped me (a newbie :-) out a lot.
Thanks again!

douceur

Glad I could be of service. 🙂