Download file from Mysql

RidgeandGable · September 1, 2014

Hiya guys

After getting everything else working how I expected, I'm sort of struggling on the last step. Downloading

I have an upload.php file that allows me to upload a file to Mysql, the fields available are:

upid - Primary
id - Need to link this to the logged on user id

name
type
size

content

The upload works perfectly

Can anyone help with implmenting it to the profile.php (1st page after login)

On profile page I have:

Welcome "username" from Session
Dynamic Table display his user id, username and password at the moment, this will be changed as not needed tho. I am using the sessions MM_Username to pass from the login

The table for Login looks like:

id - primary

username

password

I assume that if I can copy the ID from login and put it in ID in Upload and add colums to dynamic table to show the upload file, will this make that file only available to logged in user?

Cheers

RidgeandGable · September 2, 2014

Just an update, I managed to get everything linking and now the logged in user can see his own files but, there is an option to download the files and rather than display just his files, he gets the option to dwnload all files

Heres the script for the download part

<?php
        $con = mysql_connect('localhost', 'username', 'password') or die(mysql_error());
        $db = mysql_select_db('company', $con);
        $query = "SELECT username, name FROM upload";
        $result = mysql_query($query) or die('Error, query failed');
        if (mysql_num_rows($result) == 0) {
            echo "Database is empty <br>";
        } else {
            while (list($id, $name) = mysql_fetch_array($result)) {
                ?>
                <a href="download.php?username=<?php echo urlencode($username); ?>"
                   ><?php echo urlencode($name); ?></a> <br>
                <?php
            }
        }
        mysql_close();
        ?>
    </body>
</html>
           <?php
           if (isset($_GET['username'])) {
               $con = mysql_connect('localhost', 'username', 'password') or die(mysql_error());
               $db = mysql_select_db('company', $con);
               $username = $_GET['username'];
               $query = "SELECT name, type, size, content " .
                       "FROM upload WHERE username = '$username'";
               $result = mysql_query($query) or die('Error, query failed');
               list($username, $name, $type, $size, $content) = mysql_fetch_array($result);
               header("Content-link: $username");
			   header("Content-length: $size");
               header("Content-type: $type");
               header("Content-Disposition: attachment; filename=$name");
               ob_clean();
               flush();
               echo $content;
               mysql_close();
               exit;
           }
           ?>;</td>

Above is what I have amended to try and make it, original code below

<html>
    <head>
        <title>Download File From MySQL Database</title>
        <meta http-equiv="Content-Type" content="text/html; 
              charset=iso-8859-1">
    </head>
    <body>
        <?php
        $con = mysql_connect('localhost', 'username', 'password') or die(mysql_error());
        $db = mysql_select_db('test', $con);
        $query = "SELECT id, name FROM upload";
        $result = mysql_query($query) or die('Error, query failed');
        if (mysql_num_rows($result) == 0) {
            echo "Database is empty <br>";
        } else {
            while (list($id, $name) = mysql_fetch_array($result)) {
                ?>
                <a href="download.php?id=<?php echo urlencode($id); ?>"
                   ><?php echo urlencode($name); ?></a> <br>
                <?php
            }
        }
        mysql_close();
        ?>
    </body>
</html>
           <?php
           if (isset($_GET['id'])) {
               $con = mysql_connect('localhost', 'username', password') or die(mysql_error());
               $db = mysql_select_db('test', $con);
               $id = $_GET['id'];
               $query = "SELECT name, type, size, content " .
                       "FROM upload 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;
           }
           ?>

I have a session available called MM_Username which pulls the logged in user to filter everything else.

Tbl Login:
ID - Primary
Username
Password

Tbl Upload
upid - Primary
ID - For Login

username
name

type

size

content

A viewing can be seen at http://scotair.noip.me/new.php alex is password and username

If you look, Alex has actually got 3 files available, whilst the download at the side has 4 files (all files from mysql) file axismapping.dat belongs to another user with ID 3 and Username Fugo

Ch0cu3r · September 2, 2014

In profile.php you'd just fetch the records that match the users username from the uploads table. You'd then output the details for each file returned by the query, eg filename and filesize. You'd make the filename a link to your download script. The link will contain a file id query string parameter set to the upid of the file.

Basic example code for profile.php for showing the users uploaded files

echo '<h2>Uploaded Files</h2>';

// get the files belonging to the logged in user
$result = mysql_query('SELECT upid, filename, size FROM upload WHERE username=\''.mysql_real_escape_string($_SESSION['MM_Username'].'\'');

// check query did execute without errors
if($result)
{
    // output each file
    while($row = mysql_fetch_assoc($result))
    {
       // set a link to download.php passing the files upid as a query string parameter
       echo '<a href="download.php?upid='.$row['upid'].'">'.$row['filename'] - $row['size'].'</a><br />';
    }
// query did not execute, log or show error message
} else {
    trigger_error('Cannot get users files from database: ' . mysql_error());
}

.

Now in download.php you'd run a query fetch the row where the upid field matches $_GET['upid'] in the uploads table. If the query returns a row you'd present the file for download. Basic download.php example code

<?php
session_start();

// very basic check to see if user is logged in
if(!isset($_SESSION['MM_Username']))
{
    // kill the script display warning.
    die('Unauthorised accessed. You must be logged in to access this file');
}

// Has a file id been passed? 
if(isset($_GET['upid']) && ctype_digit($_GET['upid']))
{
    // fetch the file where the upid matches
    $result = mysql_query('SELECT filename, type, size, content FROM upload WHERE upid='.intval($_GET['upid']));

    // query executed ok
    if($result)
    {
         // get the files details
         list($filename, $type, $size, $content) = mysql_fetch_row($result);

         // present file for download
         header("Content-length: $size");
         header("Content-type: $type");
         header("Content-Disposition: attachment; filename=$filename");
         echo $content;
         exit;
    }
}

Edited September 2, 2014 by Ch0cu3r

mac_gyver · September 2, 2014

in your download.php code, you would also need to verify that the requested file belongs to the logged in user.

RidgeandGable · September 2, 2014

Cheers I added your code for profile.php and get an error Parse error: syntax error, unexpected ';' in C:\xampp\htdocs\ridge\success.php on line 64

Ch0cu3r · September 2, 2014

Change the end of line 64 from ); to ));

I left off the closing parenthesis for the mysql_query function.

EDIT: Make sure you have applied the changes to download.php suggested by mac_gyver too

Edited September 2, 2014 by Ch0cu3r

RidgeandGable · September 2, 2014

Yip that got rid of that error now I get 1 more error, probably something I missed

Notice: Cannot get users files from database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''alex\'' at line 1 in C:\xampp\htdocs\ridge\success.php on line 81

I created a new page rather than copying over other codes etc to reduce any errors, this is the new page for profile, new name Success.php

<?php require_once('Connections/new.php'); ?>
<?php if (!isset($_SESSION)) {
  session_start();
}
 ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_Recordset1 = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Recordset1 = $_SESSION['MM_Username'];
}
mysql_select_db($database_new, $new);
$query_Recordset1 = sprintf("SELECT * FROM login WHERE username = %s", GetSQLValueString($colname_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $new) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
 <p>Hello <?php echo $_SESSION['MM_Username']?> !</p>
 <p> </p>
 <p> </p>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
<?PHP
echo '<h2>Uploaded Files</h2>';

// get the files belonging to the logged in user
$result = mysql_query('SELECT upid, filename, size FROM upload WHERE username=\''.mysql_real_escape_string($_SESSION['MM_Username'].'\''));

// check query did execute without errors
if($result)
{
    // output each file
    while($row = mysql_fetch_assoc($result))
    {
       // set a link to download.php passing the files upid as a query string parameter
       echo '<a href="download.php?upid='.$row['upid'].'">'.$row['filename'] - $row['size'].'</a><br />';
    }
// query did not execute, log or show error message
} else {
    trigger_error('Cannot get users files from database: ' . mysql_error());
}
?>

Edited September 2, 2014 by RidgeandGable

Ch0cu3r · September 2, 2014

Ha classic.. I can be such a tool sometimes.

Code should now be error free

echo '<h2>Uploaded Files</h2>';

// get the files belonging to the logged in user
$result = mysql_query('SELECT upid, filename, size FROM upload WHERE username=\''.mysql_real_escape_string($_SESSION['MM_Username']).'\'');

// check query did execute without errors
if($result)
{
    // output each file
    while($row = mysql_fetch_assoc($result))
    {
       // set a link to download.php passing the files upid as a query string parameter
       echo '<a href="?upid='.$row['upid'].'">'.$row['filename'].' - '.$row['size'].'</a><br />';
    }
// query did not execute, log or show error message
} else {
    trigger_error('Cannot get users files from database: ' . mysql_error());
}

RidgeandGable · September 2, 2014

wow thats working great

I added to your line: echo '<a href="download1.php?upid='.$row['upid'].'">'.$row['name'].' - '.$row['size'].'</a><br />'; as nothing happend when I clicked the link before

Now when I click the link the URL shows http://scotair.noip.me/download1.php?upid=1 with a blank page

Ch0cu3r · September 2, 2014

I only gave you example code for download.php. It is untested. If it is not working your need to start to debugging it.

RidgeandGable · September 2, 2014

Hmm ok, not sure where to start as there is no error message on this one lol. I will google querys tho and see what I can pickup, are you around tonight ?

jazzman1 · September 2, 2014

Turn php errors on at the top of download.php file. Can you show us how does query look like? Are you still using the following:

 $query = "SELECT name, type, size, content " .
                       "FROM upload WHERE username = '$username'";

RidgeandGable · September 2, 2014

Hi

The basic example from Ch0cu3r has been left untouched at the moment

// fetch the file where the upid matches
    $result = mysql_query('SELECT name, type, size, content FROM upload WHERE upid='.intval($_GET['upid']));

RidgeandGable · September 2, 2014

I added <?PHP error_reporting() ?> at the top of Download.php and still get no errors. All error Reporting is enabled on Xampp

mentalist · September 2, 2014

I added <?PHP error_reporting() ?> at the top of Download.php and still get no errors. All error Reporting is enabled on Xampp

You should pass a level, see manual...

http://php.net/manual/en/function.error-reporting.php

RidgeandGable · September 2, 2014

ok got an error - Parse error: syntax error, unexpected 'MM_Username' (T_STRING) in C:\xampp\htdocs\ridge\download1.php on line 7

RidgeandGable · September 2, 2014

Hang on that errow was due to the error reporting code

RidgeandGable · September 2, 2014

nope no errors at all

RidgeandGable · September 2, 2014

Strange, I got it working all by myself

Instead of having to click on the filename in Success.php and then that directs to Download.php, if I just paste the contents of download.php into the bottom of success.php it downloads the file however, whatever file I download appears to be corrupt.

Letter1.PDF when try to open the file it says - Failed to Load PDF Document. Adobe PDF Reader
Download Formget.jpg - This is not a valid bitmap / jpeg file

RidgeandGable · September 2, 2014

I just uploaded a txt file called text.txt and the file only had my name in it, no other text, after I downloaded the file, I opened it and found this

Hello alex !</p>

<h2>Uploaded Files</h2><a href="?upid=11">A4 Invoice CREDIT.pdf - 25059</a><br /><a href="?upid=12">test.txt - 7</a><br /><br />

<b>Notice</b>: A session had already been started - ignoring session_start() in <b>C:\xampp\htdocs\ridge\success.php</b> on line <b>31</b><br />

Harry

Any ideas ?

jazzman1 · September 3, 2014

I've got the following:

Hello alex !</p>
<h2>Uploaded Files</h2><a href="?upid=7">images.jpg - 16156</a><br /><a href="?upid=11">A4 Invoice CREDIT.pdf - 25059</a><br /><a href="?upid=12">test.txt - 7</a><br /><a href="?upid=13">test.txt - 7</a><br /><a href="?upid=14">test.txt - 7</a><br />Harry

Is this what you expected to be?

mac_gyver · September 3, 2014

if I just paste the contents of download.php into the bottom of success.php

the http response for the download request must only consist of the header statements and the content of the file you want to download.

what you are seeing in the downloaded file is the content of the file and the html that's being output on your success.php page, making the file invalid.

RidgeandGable · September 3, 2014

Ah ok so the downloading setion must be a page. When its on its own i cant get it working, no files to see and no error messages

Ch0cu3r · September 3, 2014

Ah ok so the downloading setion must be a page. When its on its own i cant get it working, no files to see and no error messages

A couple notes for download.php I have omitted the code for connecting to mysql so you must add the code you use for connecting to mysql before the first if statement. I have also omitted the code for outputting an error if the query fails.

This is why I think you are getting a blank white page. Adding the missing pieces of code should get download.php to function.

RidgeandGable · September 3, 2014

lol omg I did think about a connection to the database but thought nah, won't be as simple as adding <?php require_once('/Connections/new.php'); ?> so I left it out, changed the select statement from select name etc to select * incase I was missing a spelling error or something.

Added <?php require_once('/Connections/new.php'); ?> and working with valid files

Thank you so so much for all your help guys expc, Ch0cu3r

I'll be in touch when I start the picture gallery lol

However, no the code is there and working, I should be able to use all this as a reference to fall back on.

I have downloaded & printed a book for Mysqli and PDO, so I'll be looking into changing to PDO I think for any future upgrades but will see how everything runs for a month or two first

Thanks again

Sign In

Download file from Mysql

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

jazzman1

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information