[FAQ] How do I upload files to a DB and view them?

mdshare · April 6, 2008

The following outlines all the necessary steps needed to upload a file to a DB and then view the same file using a browser. I've used MySQL here, but this can work with any DB.

1.) Set up the MySQL table

CREATE TABLE `files` (
   `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
   `file` MEDIUMBLOB NOT NULL ,
   `mime` VARCHAR( 50 ) NOT NULL ,
   PRIMARY KEY ( `id` )
);

2.) Write HTML/PHP

Basically only two fields are required to upload a file, a 'file' input field, and a 'MAX_FILE_SIZE' hidden field. The latter required, and will prevent the user from trying to upload a file that is too large on the client-side. This can be easily circumvented by the user and should be accompanied by server-side file validation. It accepts a file size in Bytes, I have set this to accept a file of 10000 bytes, or roughly 10KB. One attribute *must* also appear in the form tag to trigger the browser to upload data, and that is: enctype='multipart/form-data'.

The following should be pretty straight forward:

<?php

   //upload2db.php

   if (!isset($_POST['do_action']))
   {
       echo "<html>\n",
            "    <head>\n",
            "        <title>UPLOAD TO DATABASE</title>\n",
            "    </head>\n",
            "    <body>\n",
            "        <form action='{$_SERVER['PHP_SELF']}' method='post' enctype='multipart/form-data'>\n",
            "            <input type='file' name='userfile' />\n",
            "            <input type='hidden' name='MAX_FILE_SIZE' value='10000' />\n",
            "            <input type='submit' name='do_action' value='Upload' />\n",
            "        </form>\n",
            "    </body>\n",
            "</html>";
   }
   else
   {
       // You may also use if (is_uploaded_file($_FILES['userfile']['tmp_name']))
       // IMO using if isset is an identical test

       if (isset($_FILES['userfile']['tmp_name']))
       {
           // In this line I'm examining the file size and the MIME type of the file
           // to verify that the file is in the acceptable size range and is a jpeg
           // image.  MIME type testing isn't foolproof, it is possible to spoof this.
           // The size testing, however, is not spoofable.

           if (($_FILES['userfile']['size'] <= 10000) && ($_FILES['userfile']['type'] == 'image/jpeg' || $_FILES['userfile']['type'] == 'image/pjpeg'))
           {
               // Make a database connection here!
               $link = mysql_connect('localhost', 'user', 'pass');
               mysql_select_db('test', $link);

               // file_get_contents() PHP >= 4.3.0
               if (function_exists('file_get_contents'))
               {
                   $file = addslashes(file_get_contents($_FILES['userfile']['tmp_name']));
               }
               else
               {
                   // If using PHP < 4.3.0 use the following:
                   $file = addslashes(fread(fopen($_FILES['userfile']['tmp_name'], 'r'), filesize($_FILES['userfile']['tmp_name'])));
               }

               if (!mysql_query("INSERT INTO `files` VALUES(null, '{$file}', '{$_FILES['userfile']['type']}')", $link))
               {
                   // do database error reporting here...
                   echo 'Upload failed: Unable to insert image into database.';
               }
               else
               {
                   // Show a link to the image and display the image.

                   // This function retrieves the last value set for the auto-increment field
                   $id = mysql_insert_id();

                   echo "Upload successful! [url='viewdbfile.php?id={$id}']Click here to view the file![/url]

\n";
                   echo $_FILES['userfile']['name'].":\n
";
                   echo "[img=viewdbfile.php?id={$id}]
\n";
               }
           }
           else
           {
               echo 'Upload failed: File must be a JPEG file type and 10KB or less in size';
           }
       }
       else
       {
           echo 'Upload failed: A valid file has not been uploaded!';
       }
   }
?>

The following is the viewer script for the database stored file. This script will do everything necessary to mimick the file. For instance, if the file is an image the call to header will trigger the browser to treat the contents as an image using the Content-type header and a valid MIME type. The MIME type of the file is sort of a universal method of easily identifying file contents.

<?php
   // viewdbfile.php

   // if passing the ID via GET
   if (isset($_GET['id']))
   {
       $id = $_GET['id'];
   }

   $link = mysql_connect('localhost', 'user', 'pass');
   mysql_select_db('test', $link);

   // Make SELECT query
   $data = mysql_fetch_array(mysql_query("SELECT `file`, `mime` FROM `files` WHERE `id` = '{$id}'", $link), MYSQL_ASSOC);

   // Set the content type header
   header('Content-type: '.$data['mime']);

   // Also notice that I am not stripping the slashes,
   // Doing so may corrupt data in certain file types,
   // while you may need to do so for others.
   echo $data['file'];
?>

Also have a look at:

http://www.php.net/manual/en/features.f ... ost-method

Which explains the $_FILES superglobal and the information available in it.

Beware that a DB may not be the appropriate place for your files. Often times it is better to place your files in the normal file system. Large files in your DB can create potential bottlenecks (e.g. significantly effect DB preformance). A DB is fine for small files, but consider that DB storage of files creates extra steps in the retrieval of a file. Instead of pulling directly from the file system a DB query must be made, and then the server-side language must also interpret and output the file. Instead of the HTTP server simply querying the file system and outputting the file. Arguably marginal in some cases, but becomes more and more substantial as usage grows.

Read this FAQ for storing in the file system:

http://criminalexistence.com/ceforums/i ... ic=18418.0

Sign In

[FAQ] How do I upload files to a DB and view them?

Recommended Posts

mdshare

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Hosting

Marketplace