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  And PHP

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`)
);

< ?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>';
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="http://mistonline.in/wp/php-mysql-example-image-gallery-blob-storage/">{$title}</a> – ";
        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>

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) and change variables ($db_host, $db_user, $db_pwd, $database, $table) to your MySQL / database settings.

Php mysql example image gallery blob storage, 6.6 out of 10 based on 18 ratings

Incoming search terms:

• php image gallery tutorial (24)
• php displaying several blob images on same page (21)
• php photo gallery tutorial (13)
• php mysql image gallery tutorial (12)
• how to upload image in mysql using php (8)
• how to display blob picture in javascript gallery (6)
• php image gallery script tutorial (6)
• php mysql example (6)
• image table with mysql from fpdf in php (5)
• mysql blob tutorial (5)
• mysql image storage tutorial (5)
• php mysql photo gallery tutorial (5)
• mysql image storage perl (4)
• php mysql interview example (4)
• php mysql images tutorial (4)
• php mysql blog scripts (4)
• php photo gallery tutorial with mysql (4)
• php update blob with gif (3)
• table with mysql in fpdf with while loop (3)
• php mysql blog source code (3)
• php blob image display (3)
• php blob example with jpg file size limit (3)
• image gallery php tutorial (3)
• php mysql examples (3)
• how to display image and text from mysql database (3)
• display blob mysql php (3)
• php nestedsortable (3)
• FPDF image mysql blob (3)
• mysql image tutorial (3)
• Inserting and displaying images in MySQL using PHP (3)
• php mysql image blob storage (2)
• storing image php mysql (2)
• CURL BLOB MYSQL (2)
• php mysql gallery tutorial (2)
• delete image from mysql database using php (2)
• php send image from blob to js function (2)
• php curl onchange (2)
• gallery php mysql tutorial (2)
• php display blob images and informations mysql (2)
• image gallery script in php with example (2)

You will also be interested in ,

• Mysql backup via cron using php and email the file
• Tag cloud using php, mysql and ajax with filter
• Remote Mysql Connection From CPANEL And Connect To MYSQL Database From Your Local Webserver or Other External Web Server
• Copy mysql column in varchar to type date
• Total Number Of Rows In MYSQL
• Simple Mysql table with AUTO_INCREMENT attribute
• Connect to mysql using perl in xampp
• Set xampp:- Apache Mysql Filezilla Mercury Tomcat as a windows service and get xampp to start automatically on boot up
• Count distinct records or values and display it using mysql and php
• Get filename and file extension using php