PHP MySQL example: image gallery (blob-storage)

PHP MySQL example: image gallery (blob-storage)
abstract 
 
This is a simple example of
photo-gallery script, which uses
MySQL table (
BLOB field) to store images. Trivial password-protection, uploading and deleting images are supported. For Apache-version of PHP there is advanced browser-caching support (using If-Modified-Since header).
compatible 
 

PHP 4.3.0 or higher

PHP 5

There are three notable parts of the script:

main page generation --
generates HTML code for the list of uploaded photos, forms for photo deletion and uploading

image uploading --
processes POST request: checks password, uploads photo to database or deletes it

image showing --
Fetches image information from MySQL database and sends image do browser.If PHP is installed as mod_php (for Apache), does If-Modified-Since HTTP header checking.

Image gallery example uses following table to store all of its data:
source code: MySQL/SQL

 
CREATE TABLE `ae_gallery` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(64) character SET utf8 NOT NULL,
  `ext` varchar(8) character SET utf8 NOT NULL,
  `image_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `data` mediumblob NOT NULL,
  PRIMARY KEY  (`id`) 
);
 

You can use any name for this table, just change $table variable at the begining of the image gallery code.
We use following functions in this example:MySQL

mysql_connect - connects to MySQL server

mysql_select_db - select database

mysql_query - send query

mysql_fetch_row - get current row from result table

mysql_real_escape_string - escaping string to use it in MySQL query

mysql_num_fields - get number of rows

PHP

get_magic_quotes_gpc - checking if PHP add slashes before quotes in input parameters

stripslashes - remove odd slashes

trim - remove unnecessary spaces in the beginning and ending of string

getimagesize - return image information as an array. Third element of array -- image type.

file_get_contents - loads whole file into memory

php_sapi_name - returns the name of PHP Server API

apache_request_headers - gets some special header information from Apache

strtotime - convert textual representation of time to integer (number of seconds since 1970)

header - sends HTTP header to browser

You can get help on this functions typing "php function-name"in any good search engine
Before using following example create sql-table (execute CREATE TABLE query above) andchange variables ($db_host, $db_user, $db_pwd, $database, $table) to your MySQL/database settings.
source code: php

<?php $db_host = 'localhost'; // don't forget to change  $db_user = 'mysql-user';  $db_pwd = 'mysql-password'; $database = 'test'; $table = 'ae_gallery'; // use the same name as SQL table $password = '123'; // simple upload restriction, // to disallow uploading to everyone if (!mysql_connect($db_host, $db_user, $db_pwd))     die("Can't connect to database"); if (!mysql_select_db($database))     die("Can't select database"); // This function makes usage of // $_GET, $_POST, etc... variables // completly safe in SQL queries function sql_safe($s) {     if (get_magic_quotes_gpc())         $s = stripslashes($s);     return mysql_real_escape_string($s); } // If user pressed submit in one of the forms if ($_SERVER['REQUEST_METHOD'] == 'POST') {     // cleaning title field     $title = trim(sql_safe($_POST['title']));     if ($title == '') // if title is not set         $title = '(empty title)';// use (empty title) string     if ($_POST['password'] != $password)  // cheking passwors         $msg = 'Error: wrong upload password';     else     {         if (isset($_FILES['photo']))         {             @list(, , $imtype, ) = getimagesize($_FILES['photo']['tmp_name']);             // Get image type.             // We use @ to omit errors             if ($imtype == 3) // cheking image type                 $ext="png";   // to use it later in HTTP headers             elseif ($imtype == 2)                 $ext="jpeg";             elseif ($imtype == 1)                 $ext="gif";             else                 $msg = 'Error: unknown file format';             if (!isset($msg)) // If there was no error             {                 $data = file_get_contents($_FILES['photo']['tmp_name']);                 $data = mysql_real_escape_string($data);                 // Preparing data to be used in MySQL query                 mysql_query("INSERT INTO {$table}                                 SET ext='$ext', title='$title',                                     data='$data'");                 $msg = 'Success: image uploaded';             }         }         elseif (isset($_GET['title']))      // isset(..title) needed             $msg = 'Error: file not loaded';// to make sure we've using                                             // upload form, not form                                             // for deletion         if (isset($_POST['del'])) // If used selected some photo to delete         {                         // in 'uploaded images form';             $id = intval($_POST['del']);             mysql_query("DELETE FROM {$table} WHERE id=$id");             $msg = 'Photo deleted';         }     } } elseif (isset($_GET['show'])) {     $id = intval($_GET['show']);     $result = mysql_query("SELECT ext, UNIX_TIMESTAMP(image_time), data                              FROM {$table}                             WHERE id=$id LIMIT 1");     if (mysql_num_rows($result) == 0)         die('no image');     list($ext, $image_time, $data) = mysql_fetch_row($result);     $send_304 = false;     if (php_sapi_name() == 'apache') {         // if our web server is apache         // we get check HTTP         // If-Modified-Since header         // and do not send image         // if there is a cached version         $ar = apache_request_headers();         if (isset($ar['If-Modified-Since']) && // If-Modified-Since should exists             ($ar['If-Modified-Since'] != '') && // not empty             (strtotime($ar['If-Modified-Since']) >= $image_time)) // and grater than             $send_304 = true;                                     // image_time     }     if ($send_304)     {         // Sending 304 response to browser         // "Browser, your cached version of image is OK         // we're not sending anything new to you"         header('Last-Modified: '.gmdate('D, d M Y H:i:s', $ts).' GMT', true, 304);         exit(); // bye-bye     }     // outputing Last-Modified header     header('Last-Modified: '.gmdate('D, d M Y H:i:s', $image_time).' GMT',             true, 200);     // Set expiration time +1 year     // We do not have any photo re-uploading     // so, browser may cache this photo for quite a long time     header('Expires: '.gmdate('D, d M Y H:i:s',  $image_time + 86400*365).' GMT',             true, 200);     // outputing HTTP headers     header('Content-Length: '.strlen($data));     header("Content-type: image/{$ext}");     // outputing image     echo $data;     exit(); } ?> <html><head>
<title>MySQL Blob Image Gallery Example</title>
</head>
<body>
<?php if (isset($msg)) // this is special section for                  // outputing message { ?> <p style="font-weight: bold;"><?=$msg?> <br>
<a href="<?=$PHP_SELF?>">reload page</a>
<!-- I've added reloading link, because
     refreshing POST queries is not good idea -->
</p>
<?php } ?> <h1>Blob image gallery</h1>
<h2>Uploaded images:</h2>
<form action="<?=$PHP_SELF?>" method="post">
<!-- This form is used for image deletion -->

<?php $result = mysql_query("SELECT id, image_time, title FROM {$table} ORDER BY id DESC"); if (mysql_num_rows($result) == 0) // table is empty     echo '<ul><li>No images loaded</li></ul>'; else {     echo '<ul>';     while(list($id, $image_time, $title) = mysql_fetch_row($result))     {         // outputing list         echo "<li><input type='radio' name='del' value='{$id}'>";         echo "<a href='{$PHP_SELF}?show={$id}'>{$title}</a> &ndash; ";         echo "<small>{$image_time}</small></li>";     }     echo '</ul>';     echo '<label for="password">Password:</label><br>';     echo '<input type="password" name="password" id="password"><br><br>';     echo '<input type="submit" value="Delete selected">'; } ?> 
</form>
<h2>Upload new image:</h2>
<form action="<?=$PHP_SELF?>" method="POST" enctype="multipart/form-data">
<label for="title">Title:</label><br>
<input type="text" name="title" id="title" size="64"><br><br>

<label for="photo">Photo:</label><br>
<input type="file" name="photo" id="photo"><br><br>

<label for="password">Password:</label><br>
<input type="password" name="password" id="password"><br><br>

<input type="submit" value="upload">
</form>
</body>
</html>

warning 
 

By default, PHP does not allow uploading files larger than 2Mb. Uploading of large photos fail if you will not change upload-max-filesize and post-max-size directives.

Don't forget to change default password, or use some other authentication method.

tested 
 

FreeBSD 5.2::PHP 5.1.4

Linux CentOS 4.0::PHP 4.4.4  

次のいくつかは、24 MB未満のファイルを保存するのに役立つかもしれません.storeを変更します.php 3,MAX_FILE_SIZEの値は2400000に変更されました.あなたのPHPの設定を修正して、普通の情況の下で、PHPは2 MBより小さいファイルだけを許可して、あなたはmax_filesize(php.ini)の値は2400000に変更するMYSQLのパケットサイズ制限を取り除き、一般的にはMYSQLが1 MB未満のパケットである.以下のパラメータでMYSQL/usr/local/bin/safeを再起動する必要があります.mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24 Mまだエラーが発生している場合:タイムアウトエラーの可能性があります.遅い接続で大きなファイルを保存すると、PHPのデフォルトの時間は30秒に制限されます.max_をexecution_time(php.ini)の値を-1に変更