Pulling image from a blob in mysql

LordRogaine

I'm trying to pull an image from a blob field in mysql. I have no problem inserting it. The problem is when I want to call up the image I keep getting binary code instead of the image. I'm confused as hell. Here is the part of the script I am using that calls out the image. I am using two tables to store all my info of the image. The "filedata" table is the table that contains the image itself. Any help as to why this is happening is greatly appreciated. Go slow for me please, I'm a newbie.

<?
// Download script.. streams data from a mysql database, thru the webserver to a client browser

if (isset($_GET["id"])) {

$Storage_IP = "172.21.5.100";
$Storage_Port = 3306;
$Storage_User = "root";
$Storage_Passwd = "secret";
$Storage_DB = "storage1";

$connectto = $Storage_IP . ":" . $Storage_Port;

if (!$linkid = @mysql_connect($connectto, $Storage_User, $Storage_Passwd)) {
die("Unable to connect to storage server!");
}

if (!mysql_select_db($Storage_DB, $linkid)) {
die("Unable to connect to storage database!");
}

$nodelist = array();

// Pull file meta-data
$SQL = "select * from file where id = " . $_GET["id"];
if (!$RES = mysql_query($SQL, $linkid)) {
die("Failure to retrive file metadata");
}

if (mysql_num_rows($RES) != 1) {
die("Not a valid file id!");
}

$FileObj = mysql_fetch_object($RES);

// Pull the list of file inodes
$SQL = "SELECT id FROM filedata WHERE masterid = " . $_GET["id"] . " order by id";

if (!$RES = mysql_query($SQL, $linkid)) {
die("Failure to retrive list of file inodes");
}

while ($CUR = mysql_fetch_object($RES)) {
$nodelist[] = $CUR->id;
}

// Send down the header to the client
Header ( "Content-Type: $FileObj->datatype" );
Header ( "Content-Length: " . $FileObj->size );
Header ( "Content-Disposition: attachment; filename=$FileObj->name" );

// Loop thru and stream the nodes 1 by 1

for ($Z = 0 ; $Z < count($nodelist) ; $Z++) {
$SQL = "select filedata from filedata where id = " . $nodelist[$Z];

if (!$RESX = mysql_query($SQL, $linkid)) { 
  die("Failure to retrive file node data"); 
} 

$DataObj = mysql_fetch_object($RESX); 
echo $DataObj->filedata;

}
}
?>

goa103

Hi,

 // Pull the list of file inodes 
$SQL = "SELECT id FROM filedata WHERE masterid = " . $_GET["id"] . " order by id"; 

if (!$RES = mysql_query($SQL, $linkid)) { 
die("Failure to retrive list of file inodes"); 
}

Why do you have multiple nodes for a single file ?

 while ($CUR = mysql_fetch_object($RES)) { 
$nodelist[] = $CUR->id; 
}

Why do you convert your DB table to an array ?

$SQL = "select filedata from filedata where id = " . $nodelist[$Z];

The "filedata" table is the table that contains the image itself

So filedata doesn't just contain the image, it also contains nodes, and... filedata. The table filedata has a filedata field? What about the masterid and things like that ? Couldn't you just use a Images table with 2 fields: Id + Data ?

Instead of converting your query to a nodelist array, simply use a while loop with a mysql_fetch_array or mysql_fetch_object call.

 if (!$RESX = mysql_query($SQL, $linkid)) { 
die("Failure to retrive file node data"); 
}

Better way:

$RESX = @mysql_query ($SQL, $linkid)) or die("Failure to retrive file node data");

@ hides mysql_query error output, die if query returns FALSE. It's kind of equivalent to your if script. Checkout the PHP Manual for the example about using the mysql_query function.

Last but not least, to output an image, you don't need to create a header, simply embed your script in a <IMG SRC="myscript.php"> HTML tag. It will create it for you.

Anyway I wonder why you don't store your images as image files. As MySQL is not an object oriented DB, I don't think it's meant to store files as BLOBs. What do you think ?

So there are some issues about what the filedata and file tables really are. Why do you need a nodelist ?... Things like that 🙂

LordRogaine

thankyou for getting back to me and pointing some things out. This is new to me so it may take a little bit ti digest it all. I tried putting the <img= src > tags around my script and I still have the same problem with the image coming up in binary code? Is there a setting I may have to change or something? Thanks again for the help.

goa103

Don't worry I am not in a hurry 🙂. Just digest and ask the questions you have in mind.

Here is a script with an example of how to use the IMG HTML tag to output a picture:

image.html.php:

<HTML>
<BODY>
<?PHP

echo ("Bonjour mon barre graph!");

?>
<IMG SRC="image.img.php">

Au revoir mon barre graph!
</BODY>
</HTML>

image.img.php:

<?
// echo "Bonjour";
$image = ImageCreate(550,220);

$fond = ImageColorAllocate($image,208,216,213);
$coul_axes = ImageColorAllocate($image,11,62,43);
$coul_lignes = ImageColorAllocate($image,227,235,232);
$coul_legendes = ImageColorAllocate($image,11,62,43);
$coul_barres = ImageColorAllocate($image,42,124,94);
$coul_orange = ImageColorAllocate($image,207,140,53);

// les axes
imageline ($image, 30,30,30,190,$coul_axes);
imageline ($image,30,190,530,190,$coul_axes);

// les flêches au bout des axes
$tab_fleche_ord = array (30,30,26,34,34,34);
$tab_fleche_abs = array (530,190,526,186,526,194);

imagefilledpolygon ($image, $tab_fleche_ord, 3, $coul_axes);
imagefilledpolygon ($image, $tab_fleche_abs, 3, $coul_axes);

// Légendes
imagestring ($image,2,10,10,"Nbr de pages", $coul_legendes);
imagestring ($image,2,515,200,"Heure", $coul_legendes);

// une barre

for ($i=1; $i<=24; $i++) {
//imagestring ($image ,2,18+(20 *$i),190,$i,$coul_legendes);
imagefilledrectangle ($image,($i*20) + 15, 50, ($i*20) + 30,189,$coul_barres);
}

//$im=Imagepng($image);

imagepng($image);

?>

image.html.php is a PHP/HTML file, it has a IMG tag that "calls" the image.img.php file. The image.img.php simply generates a flow chart (sorry for the French words and comments).

It's the only way I found to insert PHP generated pictures in a HTML page.

LordRogaine

I must be doing something wrong. I took your examples from here and I still get the binary code. I appreciate you taking the time with this. Is this setup wrong?

<img src="<? echo "$DataObj->filedata>"; ?>">

goa103

Hi,

Does my example work ?

<img src="<? echo "$DataObj->filedata>"; ?>">

It's wrong, the IMG tag is in a HTML/PHP file, the $DataObj->filedata is in a PHP script called by the IMG tag:

file.html.php:
...<img src="file.img.php">...

file.img.php:
...echo ($DataObj->filedata);...

See ?

See you next post 🙂
JM

LordRogaine

lol this is getting embarrassing for me. I still get binary code no matter what I do. Someone mentioned something about

HTTPD. CONF

php_admin_value magic_quotes_runtime OFF

does this ring a bell to you? This is new to me but someone said something about turning the magic quotes OFF to get the image.

Thanks again for the help!

goa103

hi again,

sounds like Apache config to me 🙂

anyway I think we have a problem. RED ALERT

new php config by default set magic_quote to ON, it means that when a form is submitted, strings are escaped: " -> \", ' -> \'.
And I am sure a binary Blobby image you insert... contains things like " and ' ! so your image is kind of escaped when it's inserted in the database, after the form is posted!

So the solution would be to disable the magic_quote before inserting your image:
set_magic_quotes_runtime (0);

This way your image is inserted as this but but but... You can't insert something in your db until it's escaped, because your insert SQL statement probably looks like:
INSERT INTO Images (Data) VALUES ('$ImageData')

So you have to escape your $ImageData variable.

Dead End

For me the only "solution" is to stripslashes your $ImageData when you SELECT it from your db, this way \" -> "... it unescapes your image.

That's why MySQL db is not meant for blobs or images... Objects. It's a very light relational db and it doesn't know what an Object, so an image is. That's why I just use "files" to store my "images". And it perfectly works. Moreover it's faster because I don't have to store it anywhere nor to get it from the db, I just use a HTML IMG tag and the image is displayed! And voilà

So what do you think? Let's go back to these good old image files? .jpg, .gif, .png...

Sorry my magic wand didn't work that time,
JM

goa103

by the way I still wonder if my scripts worked??? if it doesn't work, it means your PHP config is nuts 🙂

what's your PHP version?

LordRogaine

this is driving me crazy!! lol I'm using version 4.2.2 I tried using stipslashes but that doesn't seem to work either. Perhaps I'm usin the stripslashes wrong? I'm confused.

goa103

Why don't you help youself by answering my question first? I can't help you if you don't do anything 🙁

Good luck!
JM

LordRogaine

I tried your script and I get the same problem

LordRogaine

this is what I get when I try your script.

‰PNG IHDR&Ü¶vw#PLTEÐØÕ >+ãëè >+|^{ÏŒ5â%‹"lIDATxœíÔMNÛPFÑ} wx'Oò:èþ×Tÿ„¸U†uŸ]32G/¾ÝÌÌÌÌÌÌìZ·1>ßÙí¶<3¹(Ñf²þ.[OcL{¬·Ó§ëjw’íHŒeývŒëìgü×[M¯”5–e1-ã¸.Gr?“O±}¶ÛéÄ§;g«Éã5»ûlÛþ²÷~]î Œã»÷—wìÞ×|ÇþuoÐÉÏñJ»'ÄÌÌÌÎÝûÙðz{›Ï~‚×Û<;(ßö6ÏÊ·$JF¤cÒ1é˜tL:&“ŽIÇ¤cÒ1é˜tL:&“îýöã×±Ÿ)&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&-&ÿ›ÉŸ:63©É“ƒrY“'e¾ìž½Q®¹gçäªsL:Ç¤sLÌÌÌÌ^{yÓúc_Ç¤ÛL–±~NÓXãìÇy‰MË²¬×˜œ™c›Æeó9ûq^{bwû§mûÐX˜}}

goa103

Hi,

Well it seems that you have a problem with your PHP installation or system. Mine is PHP 4.1+ + GD (of course), IE6 under Windows 2K.

Anyway I play around and found a solution to your problem. The following script is a working example of a MySQL handled gallery.

SQL:

#
# Table structure for table `images`
#

CREATE TABLE images (
  Id int(11) NOT NULL auto_increment,
  Image mediumblob NOT NULL,
  Thumb blob NOT NULL,
  PRIMARY KEY  (Id)
) TYPE=MyISAM COMMENT='Images and thumbs';

index.html.php:
- form to upload image and miniature
- gallery: generated from blobs of a MySQL table

<html>
<head>
<title>Pulling image from a blob in mysql</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<?php 

mysql_connect () or die (mysql_error ());
mysql_select_db ("TestTask9344") or die (mysql_error ());

// Upload ?

if (isset ($_POST ['Submit']))
{
	$Image = addslashes (GelFileGetContents ($_FILES ['Image']['tmp_name']));
	$Thumb = addslashes (GelFileGetContents ($_FILES ['Thumb']['tmp_name']));

@mysql_query ("INSERT INTO Images (Image, Thumb) VALUES ('$Image', '$Thumb')") or die (mysql_error ());
}


/**
GelFileGetContents
Reads entire file into a string
Note: This function is binary-safe.

@param FileName
File name

@return string
Returns a binary-safe string
*/


function GelFileGetContents ($FileName)
{
	$FilePointer = fopen ($FileName, "rb") or die ("can't Open file or URL $FileName");
	$Contents = fread ($FilePointer, filesize ($FileName));
	fclose ($FilePointer);

return ($Contents);
}


?>
<body>
<h1>Pulling image from a blob in mysql</h1>
<h2>Upload</h2>
<form action="<?= $_SERVER ['PHP_SELF'] ?>" method="post" enctype="multipart/form-data" name="formUpload" id="formUpload">
	<table border="1">
		<tr> 
			<td>Image:</td>
			<td><input name="Image" type="file" id="Image"></td>
		</tr>
		<tr> 
			<td>Thumb:</td>
			<td><input name="Thumb" type="file" id="Thumb"></td>
		</tr>
		<tr> 
			<td colspan="2"><div align="center">
					<input type="submit" name="Submit" value="Upload">
				</div></td>
		</tr>
	</table>
</form>
<h2>Gallery </h2>
<table width="100" border="1">
	<tr> 
		<th>Id</th>
		<th>Image</th>
	</tr>
	<?php 

$Images = @mysql_query ("SELECT Id, Thumb FROM Images") or die (mysql_error ());

while ($Image = mysql_fetch_array ($Images))
{
	?><tr> 
		<td><?= $Image ['Id'] ?></td>

	<td><a href="index.html.php"><img src="thumb.img.php?Id=<?= $Image ['Id'] ?>" border="0"></a></td>
	</tr><?php 
}

?>
</table>
<p align="center">&copy; 2002 . All Rights Reserved.</p>
</body>
</html>

thumb.img.php:
- Output an image (don't need a header, it's added by the img tag... maybe you have to add a header call on some configs: header ("Content-Type : image/jpeg")😉

<?php 

mysql_connect () or die (mysql_error ());
mysql_select_db ("TestTask9344") or die (mysql_error ());

$Image = @mysql_query ("SELECT Thumb FROM Images WHERE Id=" . $_GET ['Id']) or die (mysql_error ());

echo (mysql_result ($Image, 0));

?>

References:
http://www.faqts.com/knowledge_base/index.phtml/fid/876

I talked about it with some people and in my opinion MySQL is not meant at all to handle a gallery by itself. Using BLOBs to store images is non-sense to me:
- addslashes: can kneel down your web server! imagine a addslashes on a classic 500Ko PNG picture!
- instead of a simple <img src="image.jpg">, you have to: SELECT, QUERY, output...

Of course it's always better to use an object where it's meant to be, it depends of the workflow. But I think using a MySQL database to handle the gallery information, filesnames, fielnames, categories, author names... and simple image files... is better.

I would really like to know about some real use cases of BLOBs.

The scripts I gave you won't probably work with your config I am sorry but maybe It will help someone else. The only thing to do is to test your installation, does the GD lib work ? what's your web browser ? what's your PHP version...

LordRogaine

GD lib? I'm a newbie, I have no idea how to tell you if this is working or not. I use internet explorer and I'm using version 4.2.2. I was able to check the settings of magic quotes and it already had the setting of "OFF". I guess that wasn't the problem. I wonder how yours is set. Anyway, thanks for all the help. If ever you're down in Miami I'll be glad to buy you a beer. Thanks again for all the help.

goa103

Hi,

Well GD is the Image library for PHP. It's a set of functions to handle images. Check that a gd.dll is included in your php.ini file (no 😉. But I don't really know what to say now... If the simple script I gave you doesn't work... Did you try the samples from the PHP Manual ? How did you install your PHP distrib ?

Thanks for the beer but I live in France
JM

Duey

why would you want to get an image from a blob anyway??? storing images/files in database is a big no no you should learn this quick before it becomes a habit!!!! getting blob data from a mysql database can be very very slow and a good web designer does not do it, so i say give up now while you still can!

LordRogaine

I didn't set the configs for the php. Someone else is hosting my site on a remote server and they set it all up for me. I checked and the GD lib is set up and enabled. If you ran out of ideas of what may be the problem that's okay. I really do appreciate you taking the time out to help. Thanks!!!!!!

LordRogaine

Duey

hmmm, that looks like a double post!