2007-06-09 13:48:46
Storing images in a MySQL database
I've been working with a friend on an e-business site lately. I had an idea of storing the images in the database instead of the filesystem. I had never done that before, so I didn't know how to do it or if there were any downfalls or limitations. So I started researching and coding and found some interesting things along the way.First, let me say that the solution you'll soon see is not optimal, by any means. It actually sucks cause it makes a database call for each image, but it's a start.
The first interesting thing that I found was that using "mysql_fetch_row" and then trying to access the resource as if you used "mysql_fetch_array" isn't a good thing. It doesn't work. I must have spent two hours last night racking my brain until I gave up and went to bed. Then this morning I realized what a boneheaded move I made. Even the best make extremely stupid mistakes once in a while...
The second interesting thing I found was that the BLOB data type sucks. It can only hold 64K of binary data. How did I find this? The hard way, of course. After I fixed my stupid mistake from above, my images began to appear on the page. I quickly noticed this...
The image was cut off. What gives? So I viewed the properties of the image to see if anything looked odd...
Wow. I didn't know that image was a perfect 64K. I went to the MySQL Query Browser, since I'm also storing the size of the image, and found that the image should be 110K. Ok, I have an idea. The first page I found from Google showed me the problem. BLOB has a max size of 64K. I do have two other options though. MEDIUMBLOB has a max of 16M. LONGBLOG has a max of 4G. I don't have any images that are four gigabytes, so the MEDIUMBLOB will do just fine.
So without further ado, here's the code for the image upload/display page (index.php) and the image viewer page (view.php).
<?php
$CONN = mysql_connect("localhost","root","password") or die(mysql_error());
mysql_select_db("imagetest");
if($_SERVER['REQUEST_METHOD'] == "POST")
{
if(is_uploaded_file($_FILES['userfile']['tmp_name']))
{
$type = $_FILES['userfile']['type'];
$size = $_FILES['userfile']['size'];
$data = addslashes(file_get_contents($_FILES['userfile']['tmp_name']));
$name = $_FILES['userfile']['name'];
$sql = "insert into image (type,data,size,name) values ('";
$sql .= $type . "','" . $data . "','" . $size . "','" . $name . "');";
$result = mysql_query($sql);
if($result == "1")
{
header("Location: index.php");
}
else
{
echo "mysql_query failed\n";
}
}
else
{
echo "is_uploaded_file failed\n";
}
}
else
{
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
<head>
<title>Image Test</title>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
</head>
<body>
<form enctype="multipart/form-data" action="index.php" method="post">
<input name="userfile" type="file" />
<input type="submit" value="Submit" />
</form>
<br /><br />
<?php
$sql = "select id from image order by id desc";
$result = mysql_query($sql);
if(mysql_num_rows($result) == "0")
{
echo "No images in database...\n";
}
else
{
while($row = mysql_fetch_array($result))
{
echo "<img src=\"view.php?id=" . $row['id'] . "\" /><br />\n";
}
}
?>
</body>
</html>
<?php
}
mysql_close($CONN);
?>
<?php
$CONN = mysql_connect("localhost","root","password") or die(mysql_error());
mysql_select_db("imagetest");
$id = intval($_GET['id']);
$sql = "select type,data from image where id='" . $id . "';";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
header("Content-type: " . $row['type']);
echo $row['data'];
break;
}
mysql_close($CONN);
?>
So there ya go, a very inefficient way of storing and retrieving images with a database. Now, storing the image isn't inefficient, retrieving it is since each image requires a database call. I need to figure out how to grab all specified images with one database call...Back