Download file from DB not working

[dapcigar]

I was able to upload the files to the DB but now am trying to retrieve it from the table but it's not working.

 

my code below

<?php                              
while($row = mysql_fetch_array($query1))
{
	 (list($id, $name) = mysql_fetch_array($query1));


echo"<tr>";
    
    echo"<td> $row[category]</td>";                
    echo"<td> $row[description]</td>"; 
	 echo"<td> $row[amount]</td>";
	 	?>
    <td> <a href="approved_req.php?id=<?php echo urlencode($id);?>"
><?php echo urlencode($name);?></a> </td>
<?php
	echo"<td> $row[status]</td>"; 
	?>
    <td><a class="btn btn-success" <?php echo"  href='invoice.php?id=$row[id]'> Pay </a></td>";?></a>
    
    
	  
  
     <?php  
    echo"</tr>";
}
  // }
   ?>
  </table>
  
   <?php 
if(isset($_GET['id'])) 
{
// if id is set then get the file with the id from database
//$con = mysql_connect('localhost', 'root', '') or die(mysql_error());
//$db = mysql_select_db('test', $con);
$id    = $_GET['id'];
$query = "SELECT name, type, size, content " .
         "FROM requisition WHERE id = '$id'";
$result = mysql_query($query) or die('Error, query failed');
list($name, $type, $size, $content) = mysql_fetch_array($result);
header("Content-length: $size");
header("Content-type: $type");
header("Content-Disposition: attachment; filename=$name");
ob_clean();
flush();
echo $content;
mysql_close();
exit;
}
?>  
 

Thanks in advance

 

[ginerjm]

YOu are confusing yourself and me. You start a loop to retrieve each row of your query results, but then you grab the next row into $id & $name. So you have data pieces from two separate rows each time thru this loop. Is that what you want?

[Psycho]

As ginerjm stated, this is likely your problem

while($row = mysql_fetch_array($query1))
{

     (list($id, $name) = mysql_fetch_array($query1));

The while() loop condition gets the next record from the result set ($query1 - odd name for a result). Then the first line in the code vlock for the loop attempts to get another record from the result set (without doing anything with the results from the record retrieved in the while() condition). The result is that only every other record would actually be processed: 1, 3, 5, 7, etc.

 

Also, use fetch_array() with list is a poor idea in my opinion. The reason is that fetch_array() returns both numerically indexed and named indexes of the results. You don't show the query used, but if you ever changes the SELECT part of the query or if you change the DB structure (if you are using *), the code will break. There is no need to create variables of $id and $name since you will likely only use those values in the loop once or twice. Just use the $row array value created in the while() loop and get rid of that other line.

 

Some other comments:

- Don't use urlencode() for content that is not part of a url. If it is displayed on the page it should use htmlentities or htmlspecialchars

- Add a \n at the end of echo'd lines in the output. It will put a linebreak in the output and make it much, much easier to read the HTML output and find/debug errors

- Try not to go in and out of PHP/HTML  too much. You have some invalid output probably because you couldn't "see" it due to that. (one of the hyperlinks has two closing tags). plus, you didn't use urlencode on the second hyperlink for the 'id' value on the query string

- Separate the logic (PHP) from the output/content (HTML). When processing the logic, put the output into variables and then just echo the variables in the HTML. Ideally you should have separate files for the processing code and the output code. But, to start, just put the processing code at the top of the page, then put the output at the bottom.

- There is output before you have code that will send headers(). You can't send headers if you have ever sent output for the browser (such as an echo). The code to output the list of records comes before the code to send the file - which is why you need the objflush() is why you are having to use ob_clean() and flush(). Why put the code to create teh page output before the code to send the file? That logic should be reversed. No need to run code to generate output if there is a chance it won't be used.

- The query to get the content is using the GET value directly in the query (open to SQL injection)

- There is no handling of the code to get the file if there are no results from the query

 

Here is something closer to what I would do

<?php
 
if(isset($_GET['id'])) 
{
    // if id is set then get the file with the id from database
    //$con = mysql_connect('localhost', 'root', '') or die(mysql_error());
    //$db = mysql_select_db('test', $con);
    $id    = intval($_GET['id']);
    $query = "SELECT name, type, size, content
              FROM requisition WHERE id = '$id'
              LIMIT 1";
    $result = mysql_query($query) or die('Error, query failed');
    if(!mysql_num_rows($result))
    {
        echo "File not found";
    }
    else
    {
        list($name, $type, $size, $content) = mysql_fetch_array($result);
        header("Content-length: {$row['size']}");
        header("Content-type: {$row['type']}");
        header("Content-Disposition: attachment; filename={$row['name']}");
        echo $content;
        mysql_close();
    }
    exit;
}
 
//Put code here to run the query to get the list of data
 
$tableOutput = '';
while($row = mysql_fetch_assoc($query1))
{
    $idURL      = urlencode($row['id']);
    $nameHTML   = htmlentities($row['name']);
    $statusHTML = htmlentities($row['status']);
 
    $tableOutput .= "<tr>\n";
    $tableOutput .= "<td> $row[category]</td>\n";                
    $tableOutput .= "<td> $row[description]</td>\n"; 
    $tableOutput .= "<td> $row[amount]</td>\n";
    $tableOutput .= "<td><a href='approved_req.php?id={$idURL}'>{$nameHTML)}</a></td>\n";
    $tableOutput .= "<td> {$statusHTML}</td>\n";
    $tableOutput .= "<td><a class='btn btn-success' href='invoice.php?id={$idURL}'> Pay </a></td>\n";
    $tableOutput .= "</tr>\n";
}
 
 
?>
<html>
<head></head>
<body>
 
<table>
    <?php echo $tableOutput; ?>
</table>
 
</body>

Edited June 16, 2014 by Psycho

[dapcigar]

thanks guys.. highly appreciated