Page 1 of 1
How to get images from MySQL
Posted: Wed May 12, 2004 5:48 pm
by pickle
Hi All,
In the near future I am going to be starting on an online student picture application. This application will display the names and pictures of the students registered in a given course. Due to government restrictions, only certain people will be able to see certain pictures. So.., I was thinking that by storing the images in a 'blob' format in MySQL might be better than storing the images in a directory, with the location stored in MySQL.
The problem I am having is that of all the examples I've seen, pulling images out of MySQL is a one-at-a-time process. By that I mean I can only view one image at a time, and I can only view that image. What I want to be able to do is have an HTML page with any number of images on it, but have those images loaded out of MySQL. Is this possible or is my dream to remain just a dream

.
Thanks!!
Posted: Wed May 12, 2004 6:18 pm
by qads
I have fixed number of sites where they had images in the database, they all not only were slow, huge in size, but also they lost some images every week/month or so.
maybe you should look at the alternative methods, for example, store the images outside of the root, which is no big deal if your client owns the server.
if they dont, then u can put the images in a dir, put in a httaccess file and block direct access to them, and randomize the names of images. (never tried that myself heh).
Posted: Thu May 13, 2004 9:35 am
by pickle
Ya, the client does own the server, and ya, my initial thought was to store the images outside the root. For interest's sake, I wanted to see if I could clean it up a little, so to speak, by not having all those images hanging around. Thanks.
Posted: Thu May 13, 2004 2:36 pm
by WaldoMonster
Add an image to your MySQL database.
The field where you add the image to must be a BLOB!
Code: Select all
<?php
// +-------------------------------------------------------------------------+
// | ReadBin |
// +-------------------------------------------------------------------------+
function ReadBin($file)
{
$filehandle = fopen($file, 'rb');
$data = fread($filehandle, filesize($file));
$data = addslashes($data);
fclose($filehandle);
return($data);
}
$image = ReadBin('your_image_file.jpg');
// conect to your database...
mysql_query("INSERT INTO bitmap (image, image_id) VALUES('$image', '1'");
?>
Add this line somewhere in your HTML or PHP page:
Code: Select all
<img src="image.php?image_id=1" alt="" border="0">
The image.php file must look something like:
Code: Select all
<?php
// Don't add any line breaks before the php codes!!!
// Connect to your database...
$image_id = $_GET('image_id');
$query = mysql_query("SELECT image FROM bitmap WHERE image_id = '$image_id'");
$bitmap = mysql_fetch_array($query);
header('Content-length: ' . strlen($bitmap['image']));
header('Content-type: image/jpeg');
echo $bitmap['image'];
?>
I hope this will help you.
It can bet that there are some small syntax errors, because it is rewritten from a much larger program.
Posted: Thu May 13, 2004 2:50 pm
by pickle
Thanks for the help. After discussion with someone else on this forum, I have managed to wrap my mind around the concept. I had trouble understanding how the header's can be set for the image when no headers can be sent after output (Different HTTP connection). Thanks again.
Posted: Thu May 13, 2004 4:37 pm
by PrObLeM
thats a coo idea....never would have tought about it that way...
Posted: Thu May 13, 2004 5:27 pm
by WaldoMonster
I use it myself to resample the images to 3 different formats for cd covers.
And use this to add the image to an HTML or PHP page.
Code: Select all
<img src="image.php?id=b730pcoz8q&size=50" alt="" width="50" height="50" border="0">
It is also possible to make the image intelligent.
Send a "HTTP/1.1 304 Not changed" header when the image is the same as in the browser cache. I use the filemtime to make u unique id to compare the etag.
This code only works with Apache, because Apache is the only server that can use the PHP getallheaders() command.
Code: Select all
<?php
require_once('include/initialize.php');
require_once('include/mysql_connect.php');
require_once('include/global.php');
$album_id = Get('album_id');
$size = Get('size');
$query = mysql_query("SELECT image$size AS image, filemtime FROM bitmap WHERE album_id = '$album_id'");
$bitmap = mysql_fetch_array($query);
$etag = '"' . base_convert($bitmap['filemtime'], 10, 36) . '"';
header('Cache-Control: max-age=30, must-revalidate');
if (function_exists('getallheaders') AND $headers = getallheaders() AND $headers['If-None-Match'] == $etag)
{
header('HTTP/1.1 304 Not changed');
exit();
}
header('ETag: ' . $etag);
header('Content-length: ' . strlen($bitmap['image']));
header('Content-type: image/jpeg');
echo $bitmap['image'];
?>
Posted: Thu May 13, 2004 5:43 pm
by WaldoMonster
I have not too much bandwidth, but for a few days you can see a working version online:
<delete url>
Posted: Thu May 13, 2004 5:46 pm
by pickle
Very cool. Nice choice of music too

Posted: Thu May 13, 2004 5:59 pm
by PrObLeM
EDIT: nevermind i got it answered in #php
Posted: Mon May 17, 2004 2:56 pm
by pickle
Ok, new problem. I've got a file upload and db insertion thing working fine (I think - that may be the problem), but when I output the images, they are either cut off or broken. 1 image is about 300K, a couple are <100K, and one is < 1K (that one isn't being truncated). I've set my MAX_FILE_SIZE to be 900000 (900K). Anyone have an idea of what the problem would be? Below is the code.
Upload file
Code: Select all
<?PHP
echo <<<UPLOAD
<html>
<head>
</head>
<body>
<form enctype = "multipart/form-data" action = "$PHP_SELF" method = "POST">
<input type = "hidden" name = "MAX_FILE_SIZE" value = "900000">
<input name = "userfile" type = "file" />
<input type = "submit" name = "upload_submit" value = "Upload">
</form>
</body>
</html>
UPLOAD;
//read as binary
$file = $_FILES['userfile']['tmp_name'];
$filehandle = fopen($file,'rb');
$data = fread($filehandle,filesize($file));
$data = addslashes($data);
fclose($filehandle);
//insert into db
$conn = mysql_connect('XXX','XXX','XXX');
$db = mysql_select_db('XXX',$conn);
$filetype = $_FILES['userfile']['type'];
$query = <<<SQL
INSERT
INTO
temp_table
VALUES
('',
'$data',
'$filetype')
SQL;
$result = mysql_query($query,$conn) or die(mysql_error());
?>
Index page (The viewing page)
Code: Select all
<?PHP
$conn = mysql_connect('XXX','XXX','XXX');
$db = mysql_select_db('XXX',$conn);
$query = <<<SQL
SELECT
id
FROM
temp_table
SQL;
$result = mysql_query($query,$conn);
while($row = mysql_fetch_assoc($result))
{
echo <<<IMAGE
<img src = "temp_image.php?image_id=$row[id]" border = "1">
IMAGE;
}
?>
The image.php file (the one that reads the data from the db and outputs it).
Code: Select all
<?PHP
$image_id = $_GET['image_id'];
$conn = mysql_connect('XXX','XXX','XXX');
$db = mysql_select_db('XXX',$conn);
$query = <<<SQL
SELECT
*
FROM
temp_table
WHERE
id = '$image_id'
SQL;
$result = mysql_query($query,$conn);
$row = mysql_fetch_assoc($result);
header('Content-length: '.strlen($row[image]));
header("Content-type: .$row[mime]");
echo $row[image];
?>
Any help would be appreciated. Thanks for the help so far!!
Posted: Mon May 17, 2004 3:08 pm
by pickle
Maybe if I read the
sticky I would have solved my problem quicker.

I searched and found
viewtopic.php?t=19498. The problem was that blob is too small of a field - it can only be 65K. Upping it to mediumblob gives me a size of 1.6MB to work with. Beautiful.