File upload/download on MySQL database

Well… It is already a well discussed topic. But when I was first trying to find out how to do this, I really got stuck. Actully it took me a lot of time to find out the key code segment for the purpose.

So, here is what I’ve learned:

The table:

Suppose the structure of the MySQL table (say ‘file_table‘) is like this:

It has three fileds:

  • id : the primary key, integer type with auto increment feature.
  • content: the content of file, blob type.
  • type: varchar type, the type of the file (e.g. doc, pdf etc.).

Upload:

I think you can create a HTML form with a input with file type. Its pretty simple. Just use this code to make a input field be able to upload file:

<input id="file_upload" name="file_upload" type="file" />

Now the backend coding. I’m using PHP for this.

You can get the temp file name (the name server assigned to your uploaded file) using this:

$tmpName = $_FILES['file_upload']['tmp_name'];

Now following code segment reads the content of the file to a variable:

$fp = fopen($tmpName, 'r');
$file_size =  filesize($tmpName);
$content = fread($fp, $file_size);
$content = addslashes($content);
fclose($fp);


if(!get_magic_quotes_gpc())
{
$fileName = addslashes($fileName);
}

To get the file type, we use this segment of code:

$type = get_file_extension($tmpName);

function get_file_extension($str)
{
$i = strrpos($str,".");
if (!$i) { return ""; }
$l = strlen($str) - $i;
$ext = substr($str,$i+1,$l);
return $ext;
}

Now use this query to insert the file to database:

$query = 'INSERT INTO file_table VALUES (" ' . $content . ' ", " ' . $type . ' ")';
$result = @mysql_query($query) or die(mysql_error());

That’s it. We’ve uploaded our file to the database.

Download:

We’ve uploaded the file. Now we need to download it sometime. Thats not that hard actually.

We’ll follow these stpes:

  • Get the id of desired file
  • Fetch the file from database on the basis of the id
  • Make the file ready for download

Say, we get the id from URL. Then following code will make the file available for download:

$id    = $_GET['id'];
$query = "SELECT content, type FROM file_table WHERE id = '$id' ";

$result = mysql_query($query) or die(mysql_error());
list($file, $ftype) = mysql_fetch_array($result);

header("Content-length: $filesize");
header("Content-type: $ftype");
header("Content-Disposition: attachment; filename = FILENAME.$ftype");
echo $file;

Here we set the header with the file information. Make sure, nothing else is printed on the page before the header() call. Otherwise it will show an error and terminate. You can place your desired file name at the place of ‘FILENAME‘. Another thing is, connection with the database server is to be made before any database operation. I think you know the function:

mysql_connect();

If not can check this post: Basic PHP

Published by Ashiqur Rahman

Programmer and photography enthusiast.

Join the Conversation

3 Comments

  1. if(mysql_num_rows($result) < 0)
     {
      echo “Database is empty <br>”;
     }
    else
     {
      while(list($id, $name) = mysql_fetch_array($result))
      {
       //echo $id;
       //echo $name;
      ?>
    //problem occred area   
    <a href=’download.php?id=”<?php $id; ?>” ‘> <?php echo $name;?> </a> <br>
      <?php
       echo $id;
       echo $name;
      }
     }
    mysql_close();

    please help me

  2. Hi,
    You can get the id from any means. Say select all the ids from the table and display as a hyperlink.

    In your above code, what is the problem you are facing?

    I found some mistakes that you made:

    In this code segment:

    if(mysql_num_rows($result) < 0)
     {
     echo “Database is empty <br>”;
     }
    

    you should use:

    if(mysql_num_rows($result) < 1)

    Again at the
    <a href=’download.php?id=”<?php $id; ?>” ‘> <?php echo $name;?> </a>
    line I guess you’ve missed an ‘echo’.

    It should be:
    <a href=’download.php?id=”<?php echo $id; ?>” ‘> <?php echo $name;?> </a>

    Hope that will help.

Leave a comment

Your email address will not be published. Required fields are marked *