Upload Images With MySQL

Table of content

• Intro
• Upload form
  • Enctype attribute
  • File select
• PHP Part
  • Finding files
  • Saving files to database
  • Restore data from MySQL
• Conclusion

IntroTop

Ok, let’s continue with tutorial requests. This tutorial will cover uploading images to MySQL database. It will not be classic tutorial where I write everything and go through each step, but it will be more like some tips.

Upload formTop

First we need upload form. There are few extra parts that you don’t have when working with normal contact form.

Enctype attributeTop

First thing you’ll need in your form is enctype attribute in your form tag and its value must be multipart/form-data.

Why?

Well it would be best if you read this site.

File selectTop

Next we need a place where we’ll select our file. For that purpose we have input tag with file as value of type attribute.

<input type="file" id="fieldId" name="fieldName" />

So those are changes we need in our html form. Next let’s go to PHP part. And remember it must be POST request.

PHP PartTop

Now we’ll cover PHP part.

Finding filesTop

First thing you need to know is that you do not access your file through $_POST, but through $_FILES. That array has following structure.

array
  'nameOfField' =>
    array
      'name'     => Name of file you sent (e.g. image.jpeg)
      'type'     => Mime type (e.g. image/jpeg)
      'size'     => Size (e.g. 2010023)
      'tmp_name' => Name of file on your server (used to move file or delete it)
      'error'    => If there was any error then this is where to look fore (0 means no error)

In case you sent an array of files then structure is like this.

array
  'nameOfField' =>
    array
      'name' =>
        array
          0 => Name of first file
          1 => Name of second file
          ...
      'type' =>
        array
          0 => Type of first file
          1 => Type of second file
          ...
      'size' =>
        array
          0 => Size of first file
          1 => Size of second file
          ...
      'tmp_name' =>
        array
          0 => Name of first file on server
          1 => Name of second file on server
          ...
      'error' =>
        array
          0 => Error for first file
          1 => Error for second file
          ...

Note that you have to check each file for errors because it can happen that few files failed to upload.

Saving files to databaseTop

After we have checked each file for errors and they are safe to save, we can save them. How can we do that. Well first we need database and it should look something like this.

CREATE  TABLE  `tableName` (
    `id` INT NOT  NULL  AUTO_INCREMENT  PRIMARY  KEY ,
    `name` VARCHAR( 32  )  NOT  NULL ,
    `type` VARCHAR( 16  )  NOT  NULL ,
    `size` INT NOT NULL,
    `data`  BINARY  NOT  NULL
) ENGINE  =  MYISAM ;

We will save id, name of our file (tell you why when we get to download section), type (also will tell you later about it), size of our file and its content. As you can see, we have data to be binary because they are not plain text and need to be handled on a different way.

When we have that we can save our file using simple INSERT SQL statement like this.

$sql = sprintf(
    "INSERT INTO `tableName` (`name`, `type`, `size`, `data`)  VALUES (\"%s\", \"%s\", %d, \"%s\")",
    mysql_real_escape_string($_FILES["filedName"]["name"]),
    mysql_real_escape_string($_FILES["fieldname"]["type"]),
    $_FILES["fieldName"]["size"],
    mysql_real_escape_string(file_get_contents($_FILES["fieldName"]["tmp_name"]))
);

Note that we still use mysql_real_escape_string because it can happen that some byte represents ” or some other MySQL special characters. Also this PHP code is not very good because we don’t check for errors when opening file but it gives general idea how this works.

Now that we have our data save, we need to restore it .

Restore data from MySQLTop

We can restore our image with simple SELECT statement. Let’s say we know ID of our image, than we can do this.

$sql = "SELECT `name`, `type`, `size`, `data` FROM `tableName` WHERE `id` = $id";

$image = mysql_fetch_object(mysql_query($sql));

Now we have our image in $image variable (also note that this is not save because we need to check if query was success) and we can echo it to browser. Since we use header() function we need to make sure that we haven’t sent something to browser before. We display our image like this.

header("Content-length: " . $image->size);
header("Content-type: " . $image->type);
header("Content-Disposition: attachment; filename=" . $image->name);
echo $image->data;

die();

What does all that mean?

• Content-length tells browser how big is our file
• Content-type tells browser what kind of type is our file (so he can choose appropriate action)
• Content-Disposition: attachment; filename tells browser that he should save file as some name we have echoed. If don’t pass that, it will display it in browser
• echo $image->data is where we print content of our image
• die() is not needed but that way we make sure that only image is sent to client

ConclusionTop

So this is how you would save images using MySQL. Hope you liked it.

• 21.05.2010 -- Multi-Language Site (28)
  It's been a while since I last posted something because I was very busy. So let's do something usefu...
• 07.04.2010 -- Edit XML (29)
  I had an idea about creating tutorial that will cover manipulating XML in PHP. The idea was to creat...
• 23.12.2009 -- Learning Resources (0)
  Reader Satish requested a list of tutorials where he could learn about PHP, MySQL and jQuery. Since ...
• 11.08.2009 -- Sql Queries Cache (0)
  This will be intermediate tutorial about caching SQL query results. We will use interface to make su...
• 16.07.2010 -- Packt Special Offer (0)
  Packt Publishing has new offer for this hot summer. Have a someones birthday soon and don't know ...
• 02.01.2010 -- Enable E-mail In PHP – Win (11)
  This will be a quick tutorial that will show you how to enable e-mail function in PHP on Windows....
• 25.11.2009 -- Implementing Bitwise Permissions (1)
  Reader Freddy requested a tutorial about implementing bitwise permissions in real application. This ...