Sometimes, it’s more convenient to save images in a database than as files. MySQL and PHP make it very easy to do this. I will describe how to save images in a MySQL database.

• Setting up the database

The difference between any regular text or integer fields and a field that needs to save an image is the amount of data that is needed to be held in the field. MySQL uses special fields to hold large amounts of data. These fields are known as blobs (Blob).

Here is the BLOB definition from the MySQL site:

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB differ only in the maximum length of the values they can hold.

For more information about MySQL Blobs check out: Reference: Blob

Use the next syntax to create a basic table that will hold the images:

CREATE TABLE Images (
PicNum int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Image BLOB
);

• Setting the upload script

What we need now is the PHP script that will get the file and insert it into the database. The next script does just that. In the script, I’m assuming that the name of the file field is “Picture”.

<?
If($Picture != "none") {
  $PSize = filesize($Picture);
  $mysqlPicture = addslashes(fread(fopen($Picture, "r"), $PSize));
 
  mysql_connect($host,$username,$password) 
                       or die("Unable to connect to server");
  @mysql_select_db($db) 
                           or die("An error occured when selecting database");
  mysql_query("INSERT INTO Images (Image) VALUES ('$mysqlPicture')") 
                   or die("Query failed");
}
else {
  echo "Failure during upload.";
}
?>

This is all that is needed to enter the image into the database. Note that in some cases you might get an error when you try to insert the image into the database. In such a case you should check the maximum packet size allowed by your MySQL version. It might be too small and you will see an error about this in the MySQL error log.

What we did in the above file is:

1. Check if a file is chosen.
2. addslashes() to the picture stream to avoide errors in MySQL.
3. Connect to MySQL, choose the database and insert the image.

• Size does matter

The theoretical limit in MySQL 4.0 is 2G, however each blob requires generally to have 3 copies of it in the memory (stored in various buffers) so you need a lot of memory, if you have large BLOBs stored in MySQL. This is the reason, why the theoretical limit can be reached only on 64bit systems. The Practical limits are around some hundreds of megs per BLOB.

While logged into MySQL, we should modify the max_allowed_packet system variable. This variable determines how large of a packet (i.e. a single row) can be sent to the MySQL server. By default, the server will only accept a maximum size of 1 meg from our client application. If you do not intend to exceed 1 meg, this should be fine. If you do intend to exceed 1 meg in your file transfers, this number has to be increased.

• Conclusion

It’s often suggested to save path to a file in database and not the file itself. Rethink everything before storing large data in your database - it can save lots of memory.

On the other hand this is extremely useful for downloading files of different types from a database, or even displaying images stored in Blob fields.

When a web browser is pointed to a directory on your website which does not have an index.htm(l) in it, the files in that directory can be listed on a web page.

It’s typical for most server setup that directory listing is disabled. You can overwrite apache’s options with .htaccess file.
Just Change:

Options +Indexes

If you do use this option, be very careful that you do not put any unintentional or compromising files in this directory. And if you guessed it by the plus sign before Indexes, you can throw in a minus sign (Options -Indexes) to prevent directory listing entirely.

If you want the directory contents to be listed, but only if they were HTML pages and not images:

IndexIgnore *.gif *.jpg

I used this for years without problems but now I wanted to have a better look for the list.

My idea was a PHP script that takes the file structure of a specified folder and outputs it into a stylable format. It should simply lists the different folders as links, but not in the standard (boring) format. I want to be able to put it in my template.

Solution is a PHP script that has a definied path (or takes the path where the script is located) and then uses opendir function to generate an array. When iterating the array a difference has to be made if the accessed item is a file or directory.
Additionally the filesize of each item is detected.

<?php
$dir = opendir (dirname(__FILE__));
$exclude = array("index.php", ".", "..");
 
while($fn = readdir($dn)) {
	if ($fn == $exclude[0] || $fn == $exclude[1] || $fn == $exclude[2]) 
            continue;
	$list[] = $fn;
}
 
sort($list);
 
 
foreach ($list as $item)
{
  if (is_dir($item)){
	$dir .= "<img src='/img/dir.png'>&nbsp;<a href='$item'>$item</a><br>";
  }else{
	$size= filesize($item);
	$m = 'bytes';
	if ($size > 1024) {
		$size=round($size/1024,2);
		$m = 'KB';
	} elseif ($size > 1024*1024){
		$size = round(($size/1024)/1024,2);
		$m = 'MB';
	}
	$a .= "<img src='/img/file.png'>&nbsp;<a href='$item'>$item</a> - $size ($m)<br>";
  }
  echo $dir . $a;
}
 
closedir($dir);
?>

This works well but it it has no great advantage over the standard directory listing. The script can be put into a nice styled page that is not as boring as the standard. It would have costed me some time to extract the filetype out of the filename and then load an appropriate image for it.

To save time my choice is a script that generates a well formed table, listing the contents of a specified directory and sub-directories. Has image tags associated with certain file types and the ability to generate thumbnails to preview image files.

Directory Listing Script by Evoluted

A different styled solutions is PHP Directory Listing on freshmeat.net. It displays the content of a directory and automatically creates thumbnails. Caching is done for JPEG, GIF, and PNG files.

A stylish directory listing continuously improves the user-friend user friendliness a lot.