Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP FAQs
| Search | Today's Posts | Mark Forums Read
PHP FAQs This moderated forum is where our PHP experts answer some of the questions they see asked most frequently in the other PHP forums. You cannot post other messages here, use the other PHP forums for that.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP FAQs section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 3rd, 2004, 07:45 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default Q. How do I upload files to a DB and view them?

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 isn't really required, but 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:

Code:
<?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! <a href='viewdbfile.php?id={$id}' target='_blank'>Click here to view the file!</a><br /><br />\n";
                    echo $_FILES['userfile']['name'].":\n<br />";
                    echo "<img src='viewdbfile.php?id={$id}' style='border: 1px solid black; display: block; margin: auto;' /><br />\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.

Code:
<?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/feature...ad.post-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://p2p.wrox.com/topic.asp?TOPIC_ID=12104


Similar Threads
Thread Thread Starter Forum Replies Last Post
upload multiple files in db with persits aspjpeg dann2 Classic ASP Basics 0 May 10th, 2007 11:20 PM
cant upload files akber ASP.NET 1.0 and 1.1 Basics 3 November 6th, 2006 03:06 AM
Table View and DB Changes rander3328 SQL Server 2000 3 September 16th, 2004 11:18 AM
upload files joeore PHP Databases 9 February 15th, 2004 05:51 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.