Storing Images in a Database

This tutorial teaches you how to upload images into a MySQL database using PHP. Even though it sounds complicated, it is fairly simple and has many practical applications. One example of an application would be Forum User Images. Take user avatars for example. Its impractical to upload avatar files to one common folder, because chances are sooner or later two users will have the same name for an avatar, and either one avatar will be overwritten, or the other not accepted, causing trouble. Image Databasing solves this problem by inserting the image data into its own unique row in a table, each assigned with an ID number instead of a filename. Images can then be called from the database and be view using one PHP file for all images. How are they inserted into the database? By converting the data to base64. If you’re confused, please bear with me, you will understand it soon.

There will be 3 PHP files in this tutorial:

readdir.php – this puts all the images in a folder into the database
image.php – the actual image script that displays the imag
view.php – an example file that shows you how to call the image
Base 64 is an encoding for binary data as described as a means of encoding email bodies in the IETF’s MIME RFC. Base64 consists only of 64 encoding characters (A-Z, a-z, 0-9, +, /) that are a subset of US-ASCII. You can find out more here.

PHP includes 2 base64 functions – base64_decode() and base64_encode(). You can find out more about each one here and here respectively.

Creating the Image Database

First, create a MySQL database called base64imgdb (this is the name that will be used throughout the tutorial). Alternatively, you can use an existing database and just add the tables.

Second, create a table called images with two rows. Name the first one imgid, and give it the parameters TYPE: INT EXTRA: auto_increment, and check the circle under Primary. Name the second sixfourdata, and make it TYPE: LONGTEXT. Here is the SQL code you can execute to create the table:

constant connection to the MySQL database instead of creating multiple ones. Here is the PHP code, you will need to change the username and password parts:

mysql_connect("localhost", "username", "password");

if (!$dbcnx)
{
echo( "<p>connection to database server failed!</p>");
exit();
}

if (! @mysql_select_db("base64imgdb") )
{
echo( "<p>Image Database Not Available!</p >" );
exit();
}
?>

Next we need to open the directory, where

readdir.php file is located:

opendir($path) or die("Unable to open directory $path");

This is the hardest part of the script: sorting the image types, reading the data using fopen, converting it using base64_encode , and then inserting it into the table.

readdir($dir_handle))
{
$filetyp = substr($file, -3);
if ($filetyp == 'gif' OR $filetyp == 'jpg')
{
$handle = fopen($path . "/" . $file,'r');
$file_content = fread($handle,filesize($path . "/" . $file));
fclose($handle);

$encoded = chunk_split(base64_encode($file_content));
$sql = "INSERT INTO images SET sixfourdata='$encoded'";

@mysql_query($sql);
}
}
?>

This is the last and final part of the readdir.php: closing the directory and stating the proccess is complete:

closedir($dir_handle);
echo("complete");
?>

The Image Reader IMAGE.PHP

This file may be the hardest file to understand whenever you see how simple view.php is, but bear with me, your patience will pay off. This file takes a request, requests the row in the table, decodes the data, and presents itself as an image. First, we have to connect to the database again:

mysql_connect("localhost", "username", "password");
if (!$dbcnx)
{
echo( "connection to database server failed!");
exit();
}
if (! @mysql_select_db("base64imgdb") )
{
echo( "Image Database Not Available!" );
exit();
}
?>

Now we need to find out which row its requesting, which is done using image.php?img=x

mysql_query("SELECT * FROM images WHERE imgid=" . $img . "");

if (!$result)
{
echo("Error performing query: " . mysql_error() . "");
exit();
}

while ( $row = mysql_fetch_array($result) )
{
$imgid = $row["imgid"];
$encodeddata = $row["sixfourdata"];
}
?>

Now here is the last and most confusing part of the file:

base64_decode($encodeddata);
?>

Now let me explain this. All this does is decodes the base64-encoded image data, end echos it. Thats it, nothing else.

Okay, so you made it this far already. This is now the easiest to copy and paste but hardest part to understand, where image.php?img=1 matches with whatever row the image is on, for example if its row 357 then you would need to put image.php?img=357:

readdir.php

mysql_connect("localhost", "username", "password");

if (!$dbcnx)
{
echo( "connection to database server failed!" );
exit();
}

if (! @mysql_select_db("base64imgdb") )
{
echo( "Image Database Not Available!" );
exit();
}

$path = "./";

$dir_handle = @opendir($path) or die("Unable to open directory $path");

while ($file = readdir($dir_handle))
{
$filetyp = substr($file, -3);
if ($filetyp == 'gif' OR $filetyp == 'jpg')
{
$handle = fopen($file,'r');
$file_content = fread($handle,filesize($file));
fclose($handle);
$encoded = chunk_split(base64_encode($file_content));
$sql = "INSERT INTO images SET sixfourdata='$encoded'";
@mysql_query($sql);
}
}

closedir($dir_handle);
echo("complete");
?>

image.php

mysql_connect("localhost", "username", "password");

if (!$dbcnx)
{
echo( "connection to database server failed!" );
exit();
}

if (! @mysql_select_db("base64imgdb") )
{
echo( "Image Database Not Available!" );
exit();
}

$img = $_REQUEST["img"];
$result = @mysql_query("SELECT * FROM images WHERE imgid=" . $img . "");

if (!$result)
{
echo("Error performing query: " . mysql_error() . "");
exit();
}

while ( $row = mysql_fetch_array($result) )
{
$imgid = $row["imgid"];
$encodeddata = $row["sixfourdata"];
}

echo base64_decode($encodeddata);
?>

view.php

<html>

<body>

</body>

</html>

About the author

Written by Darren W..

Darren Hedlund is a freelance Web developer, writer, and data analyst. Darren has a degree in Computer Information Science and has spent the last 15 years developing application and environments from hand held, windows, web, virtual science, gaming, artificial intelligence and graphics design.

Darren's coding knowledge ranges from C+, Visual Basic, .NET, PHP, JSP, REXX, KIXX, and many others. His graphical and environmental knowledge stems in Macromedia Flash, 3D studio Max, Curious Labs Poser, Adobe Photoshop, and many others. Darren works in many platforms ranging from database, visual design, and, system development.

Website

If you found this post useful you may also want to check these out: