PHP, MySQL and images stored in database

Srdjan Pejic

Hello,

I have a problem that I have not been able to solve, even after
searching the web. I have stored couple of images in the MySQL database
and I am trying to get them displayed on a web page with PHP.

Now, before we go any further, I want to make it clear that I am very
aware of the current debate about storing images in the database. I
understand both sides, however this is something I want to do for fun,
as well as good learning.

Now that that is out of the way, I will outline my problem. I deposited
the images into MySQL using phpMyAdmin, which of course wrote the images
in using hex. I, then, wrote a simple script(SELECT * from webImages)
that seems to pull the images out of the database, but the it displays
them in the BINARY format. My question to you is, what do I need to
change in script in order to get the images displayed as images?
The relevant code and database design are below.

Table design is this:

(id INT(3) NOT NULL auto_increment PRIMARY KEY,
thumb blob NOT NULL,
image mediumblob NOT NULL) Type=MyISAM;

The code is the plain code for getting anything out of the database,
error checking omitted:

$conn = mysql_connect($host, $user, $pass);
mysql_select_db($somedb, $conn);

$query = "SELECT * FROM webImages";
$result = mysql_query($query, $conn);

echo "<table width=\"50%\" align=\"center\">";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>$row[0]</td>";
echo "<td>$row[1]</td>";
echo "<td>$row[2]</td>";
}
echo "</table>";
mysql_free($result);
mysql_close($conn);

Last thing, I did try to use base64_decode() function, but I ended not
getting anything displayed.
Thanks in advance,
Srdjan

Jul 17 '05 #1

Nemon

You can't do that... You'd be outputting the binary data right
into a text based web page....

If you output an image from a PHP script then thats all you can
output, you can mix image(binary) and html(text).

// Connect to database.
// Fetch image data.
// Close database connection.
header( "Content-Type: image/jpeg" ); // If it was a jpeg
echo $row[1];

Actually i lied, you "can" output both image data and HTML from
one script, in one go. You need to encode the image on a <img
src="data:image/jpeg;base64," . $image_encoded > ...

So in your case.

<?
$conn = mysql_connect($host, $user, $pass);
mysql_select_db($somedb, $conn);

$query = "SELECT * FROM webImages";
$result = mysql_query($query, $conn);

echo "<table width=\"50%\" align=\"center\">";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>$row[0]</td>";
echo "<td><IMG SRC=\"data:image/jpeg;base64," . base64
_encode($row[1]) . "\"></td>";
echo "<td><IMG SRC=\"data:image/jpeg;base64," . base64
_encode($row[2]) . "\"></td>";
}
echo "</table>";
mysql_free($result);
mysql_close($conn);
?>

But why you would want to do that is beyond me. Im really not
sure what the benifit is. It could infact be a very bad idea.

----------------------------------------------
Posted with NewsLeecher v2.0 Beta 4
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
----------------------------------------------

Jul 17 '05 #2

Bent Stigsen

Srdjan Pejic wrote:
[snip][color=blue]
> ... My question to you is, what do I need to
> change in script in order to get the images displayed as images?[/color]

write a script that takes an imageId as parameter, selects image from
DB, sends a content-type header, and finally sends the raw imagedata.

In your html, you would have an image-reference like:
<img src="fetchImage.php?imageId=123">

[color=blue]
> The relevant code and database design are below.
> Table design is this:
>
> (id INT(3) NOT NULL auto_increment PRIMARY KEY,
> thumb blob NOT NULL,
> image mediumblob NOT NULL) Type=MyISAM;[/color]

add a "type" column, which you get from $_FILES[]['type'] and send to
the browser as:
Header("Content-Type: $type");

It is important, but not vital if you only store jpg/gif/png images.
Atleast IExplorer seems to autodetect the type on images.

Perhaps you also would save the original filename, and/or a short
description.

[color=blue]
> The code is the plain code for getting anything out of the database,
> error checking omitted:
>
> $conn = mysql_connect($host, $user, $pass);
> mysql_select_db($somedb, $conn);
>
> $query = "SELECT * FROM webImages";[/color]
SELECT id FROM webImages[color=blue]
> $result = mysql_query($query, $conn);
>
> echo "<table width=\"50%\" align=\"center\">";
> while($row = mysql_fetch_array($result))
> {
> echo "<tr>";
> echo "<td>$row[0]</td>";[/color]

"<a href=\"fetchImage.php?imageId={$row[0]}\"><img
src=\"fetchImage.php?imageId={$row[0]}&thumb=1\"></a>"

or whatever. Once you got the script to retrieve the image, you just
treat it as any other file on the server.


/Bent

Jul 17 '05 #3

Srdjan Pejic

Thank you both for replying. To answer your question, Nemon, the reason
I am doing it purely for research. I wanted to find an alternative
solution to storing images for a website and I thought that storing them
in the database would make things easier. I am still undecided whether
this is a good solution or not. I am going to try both approaches to see
which one works best, both ease of programming and performance wise. In
the end, I just may stick with what seems to be the most common solution
to this problem and that is storing the filepath to an image instead.
Again thanks for all your help.

Srdjan

Jul 17 '05 #4