Rendering files from MySQL Database into the browser

Howdy! I haven’t posted anything on this blog for more than 3 weeks now. I’m really sorry especially to those who are subscribed to this blog via email. I was just too busy with my projects and school that’s why I wasn’t able to post anything new.

Okay enough with my life story. Let’s proceed to the main topic. In this tutorial I’m going to show you how you can read files that are saved on the database and then render it into the browser.

I’m going to make this quick because I’m really super sleepy now.

 

Rendering Images

Rendering images is really simple, you just have to use the traditional image tags in html:

<img src="renderer.php?id=2"/>

But wait, why are we linking to a php file? Good question. Images, audio files, video files, documents are actually stored in the database in a gibberish form. Don’t believe me? Here’s a screenshot taken from Heidi Sql when opening a file that is saved on the database.

image

That is binary data, you cannot really make sense out of it unless you have a brain of a computer.

Okay so what does this renderer.php contain?

As you have seen a few seconds ago we supplied an id on the url and that’ what were going to use as a basis for what were going to fetch from the database. This is actually an approach for the naïve and lazy, I don’t recommend using $_GET and strings from the url as a basis for your database queries. It’s dangerous because people are getting smarter and smarter everyday there’s almost always a security hole on almost every application.

First include the database configuration, then assign the query to a variable called $id then perform the query for fetching the file in the database.

require_once('zenoir_config.php'); //database config $id = $_GET['id']; //don’t do this in real life, for example only $file = $db->get_row("SELECT * FROM tbl_files

WHERE file_id='$id'"); //fetch data

Now we got all the values that we need:

$filename = $file->filename;
$data = $file->file_data; //binary gibberish
$mime = get_mime_type($filename); //mime type of the file

The first two variables are self-explanatory we need the data and the filename. But what is this mime type thingy? If I am to describe it on my own, mime types are like instructions to the browser on what type of data it’s going to render. If the browser can’t render that specific data then the default action taken by the browser is to store it on the local machine or the computer of the user that is accessing the website. Here’s an extensive explanation from Wikipedia about mime types:

MIME

Here’s the code for getting the mime type of a specific file. I actually got this piece of code from someone at php.net in the comments section. Yep! comments section is really a gold mine.

function get_mime_type($filename, $mimePath = 'yeah'){ 
   $fileext = substr(strrchr($filename, '.'), 1); 
   if (empty($fileext)) return (false); 
   $regex = "/^([\w\+\-\.\/]+)\s+(\w+\s)*($fileext\s)/i"; 
   $lines = file("$mimePath/mime.types"); 
   foreach($lines as $line) { 
      if (substr($line, 0, 1) == '#') continue; // skip comments 
      $line = rtrim($line) . " "; 
      if (!preg_match($regex, $line, $matches)) continue; // no match to the extension 
      return ($matches[1]); 
   } 
   return (false); // no match at all 
} 

The function above uses a file called mime.types, which can be found on the following location if you’re on Wamp:

D:\wamp\bin\apache\Apache2.2.17\conf

But if you’re on Xampp it’s in:

C:\xampp\apache\conf

Never mind the Drive letters, its dependent on where you installed the Xampp or Wamp.

In the example I placed the mime.types file on the yeah folder:

image

Oh yes, I still haven’t told the story of this get_mime_types function. What it does is just to get the file extension of the file which is determined from the filename. This isn’t actually an accurate method of determining the true mime type of a file. What if someone naughty changed the extension of a php script to .jpg. The function will actually label the php script as an image and not a script which can do nasty stuff on the server. A good practice for this is when you plan to upload files on the file system of the server be sure to upload the files on a non-web accessible folder so that the naughty guys will not be able to execute it. 

After determining the mime type all you have to do now is to include the header information and then output the actual data.

header('Content-Type: ' . $mime); 
header('Content-Disposition: attachment; filename='.$filename);

echo $data;

In the case of an image this data will be rendered as an ordinary image in the browser such as this one:

image

 

Rendering Other Files

Rendering other file types is still a problem at the time of writing of this article. How I wish the following tags were already existing:

<document src="msword.doc"/> <!—doc files—>
<spreadsheet src="worksheet.xlsx"/> <!—spreadsheets—>
<presentation src="awesome.ppt"/> <!—powerpoint—>
<code src="php.js"/> <!—for rendering source code with highlighting etc.—>

Don’t copy and past those code, those are just my dream. I hope it would be possible to render just about any type of file that makes sense in the browser.

Good news is you can actually store mp3, ogg, mp4, and webm files on the database and render them using either the html5 video or audio tags. But remember that not every browser supports this awesomeness (IE I’m looking at you).

 

Conclusion

That’s it for this tutorial. Rendering image files that are stored in the database is actually easy, but other file types are not.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s