How to get images from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

How to get images from MySQL

Post 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 :D .

Thanks!!
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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).
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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

&lt;img src="image.php?image_id=1" alt="" border="0"&gt;
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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
PrObLeM
Forum Contributor
Posts: 418
Joined: Sun Mar 07, 2004 2:30 pm
Location: Mesa, AZ
Contact:

Post by PrObLeM »

thats a coo idea....never would have tought about it that way...
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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

&lt;img src="image.php?id=b730pcoz8q&amp;size=50" alt="" width="50" height="50" border="0"&gt;
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'];
?>
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post by WaldoMonster »

I have not too much bandwidth, but for a few days you can see a working version online:
<delete url>
Last edited by WaldoMonster on Sun May 23, 2004 5:50 pm, edited 1 time in total.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Very cool. Nice choice of music too 8)
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
PrObLeM
Forum Contributor
Posts: 418
Joined: Sun Mar 07, 2004 2:30 pm
Location: Mesa, AZ
Contact:

Post by PrObLeM »

EDIT: nevermind i got it answered in #php
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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!!
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Maybe if I read the sticky I would have solved my problem quicker. :oops: 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply