converting BLOB to jpeg MySQL/PHP

tsinc80697 · 4 May 2007 at 22:28

Second post in a few days, keep getting stuck on things

Quick run over of tables im using for this:

Package, Picture, package_picture.

Package contains all the data and Package_ID.
Picture contains the BLOB and Picture_ID.
Package_Picture contains Picture_ID & Package_ID.

Package_ID links the 3 tables together.

I have package.php which queries the db and pulls out the relevant data, but I need it to pull out the image in "Picture".

In package.php I have:

PHP:

$imageID = $_GET['id'];
<img src="image_test.php?id=<?php print $imageID; ?>" >

Gets the ID of the package currently being viewed and posts the image_test.php for the image.

In image_test.php I have:

PHP:

<?php
header("Content-Type: image/jpg");
require("functions.php"); //Gets all the functions
dbConnect(); //Connects to the database
$imageid = $_GET['id'];
if ($imageid) {
		$sql = "SELECT image, description, package_ID FROM picture, package, package_picture WHERE package_ID = {$_GET['id']}";
    $result = mysql_query($sql);
    $data = mysql_result($result, 0, "bin_data");
    $type = mysql_result($result, 0, "filetype");
    $bytes = $row['image'];
    print $bytes;
}
?>

Its probably something daft that I have missed out, but its really irritating me and I cant find what it is!

Thanks anyone

marc2003 · 4 May 2007 at 23:41

what is $bytes? if it's the path to a .jpg file you would use this code.

Code:

<?php
header("Content-Type: image/jpg");
require("functions.php"); //Gets all the functions
dbConnect(); //Connects to the database
$imageid = $_GET['id'];
if ($imageid) {
    $sql = "SELECT image, description, package_ID FROM picture, package, package_picture WHERE package_ID = {$_GET['id']}";
    $result = mysql_query($sql);
    $data = mysql_result($result, 0, "bin_data");
    $type = mysql_result($result, 0, "filetype");
    $bytes = $row['image'];
    $im = imagecreatefromjpeg($bytes);
} else {
    //display widthxheight pixels blank image if id is false
    $im = imagecreatetruecolor(10,10); 
}
Imagejpeg($im);
Imagedestroy($im);
?>

tsinc80697 · 5 May 2007 at 00:11

If i use that code it outputs

<img src="image_test.php?id=3" >

In the source but nothing on the page but its obviously me doing something wrong.

The $bytes was taken from an example I found somewhere.

The picture.image holds the BLOB data that has a picture.picture_ID alongside it, the picture_ID is linked to the specific package by package_package_ID.

i thought I can just substitute the actual img src path with a php file an it would output the image or do I have wrong of the stick ?

marc2003 · 5 May 2007 at 00:23

oops i've just confused things. i didn't know what blobs were and thought you were dealing with jpg files on a disk. i've just had a quick google and blobs seem to be raw picture data from a database?

tsinc80697 said:
i thought I can just substitute the actual img src path with a php file an it would output the image

yup that's right. as you probaby seen all over the forums, people are using php scripts to display their sigs. what you're doing is no different.

marc2003 · 5 May 2007 at 01:45

how sad am i? :eek:

i actually installed mysql and googled this and got it working....

first of all using a database named 'test' i ran this query to create my table

Code:

CREATE TABLE upload (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
type VARCHAR(30) NOT NULL,
size INT NOT NULL,
content MEDIUMBLOB NOT NULL,
PRIMARY KEY(id)
);

next is upload.php where you can upload a jpg file and it's stored in the database as a BLOB.

Code:

<?
if(isset($_POST['upload']))
{
		$fileName = $_FILES['userfile']['name'];
		$tmpName  = $_FILES['userfile']['tmp_name'];
		$fileSize = $_FILES['userfile']['size'];
		$fileType = $_FILES['userfile']['type'];
		
		$fp = fopen($tmpName, 'r');
		$content = fread($fp, $fileSize);
		$content = addslashes($content);
		fclose($fp);

		$conn = mysql_connect('localhost', 'root', 'ocuk') or die ('Error connecting to mysql');
		mysql_select_db('test');
		
		$query = "INSERT INTO upload (name, size, type, content ) ".
		         "VALUES ('$fileName', '$fileSize', '$fileType', '$content')";

		mysql_query($query) or die('Error, query failed');					
		
		mysql_close($conn);
		
		echo "<br>File $fileName uploaded<br>";
}		
?>
<form action="" method="post" enctype="multipart/form-data" name="uploadform">
<input name="MAX_FILE_SIZE" type="hidden" value="2000000">
<input name="userfile" type="file">
<input name="upload" type="submit" class="box" value="Upload">
</form>
<a href="list.php">view uploaded images</a></p>

list.php which querys the database and returns displays all the images...

Code:

<?
$conn = mysql_connect('localhost', 'root', 'ocuk') or die ('Error connecting to mysql');
mysql_select_db('test');
$query  = "SELECT id, name FROM upload";
$result = mysql_query($query) or die('Error, query failed');
if(mysql_num_rows($result) == 0) {
	echo "Database is empty <br>";
} else {
	while(list($id, $name) = mysql_fetch_array($result)) {
		echo '<p><img src="image.php?id='.$id.'"></p>';
	}
}
mysql_close($conn);
?>

last is image.php which actually gets the image and outputs as a jpg

Code:

<?
if(isset($_GET['id']))
{
	$conn = mysql_connect('localhost', 'root', 'ocuk') or die ('Error connecting to mysql');
	mysql_select_db('test');
	$id      = $_GET['id'];
	$query   = "SELECT name, type, size, content FROM upload WHERE id = '$id'";
	$result  = mysql_query($query) or die('Error, query failed');
	list($name, $type, $size, $content) = mysql_fetch_array($result);
	header("Content-type: image/jpg");
	echo $content;
	mysql_close($conn);
	exit;
}
?>

tsinc80697 · 5 May 2007 at 12:56

thanks a lot, ill try it now

looked through loads of examples on the net but they all were linked with an upload feature and I couldnt get this to work as its using multiple tables.

The ID gets pulled ok it just wouldn't display the image, ill try it after i get a shower

marc2003 · 5 May 2007 at 13:26

well i guess the reason why all the tutorials are image uploaders is that it's the best way to get a jpg into a database. how else would you do it apart from hard referencing a link to a jpg file in your script? where did you get your blob data from in your database?

tsinc80697 · 5 May 2007 at 14:05

Thats what I dont know, I didnt create the database, someone else did and they have people inserting data into it, so I've never worked with BLOBs before thats why its awkward lol.

marc2003 · 5 May 2007 at 14:19

don't suppose you're running the database on windows? using mysql administrator you can view/verify the contents of the blob field like this....

screenshot

i guess there mysql tools on other os'es that do similar stuff?

AS_Platinum · 5 May 2007 at 18:22

on a semi-side note, you do realise the storage of images in BLOB format in the database is bad practice, store the filenames/location instead. Backing up a database with lots of images is a bit of a pig with large amounts of data, let alone restoring a backup.

tsinc80697 · 5 May 2007 at 18:51

Well the database was designed by someone doing their masters in software engineering, Im just doing the php side of things and learning as i go along so his decision for all the db things

will tell him what you said though

marc2003 · 5 May 2007 at 22:06

retrieving images from the database appears to be fairly cpu intensive too. i was only mucking about on my own pc as i have abyss webserver/php/mysql installed and you can definitely see the difference between that and just working with jpg files on disk.

tuckenator · 6 May 2007 at 02:44

I am working on the same script as above and was wondering how would you go about retrieving images from the server that are relevant to the user logged in at the time?

Tucks

Beansprout · 6 May 2007 at 02:47

tsinc80697 said:
Well the database was designed by someone doing their masters in software engineering

So no real-world experience then

Or too much experience working with large corporates with too much spare server power

Also, marc2003, use mysql_real_escape_string() instead of addslashes()

Dj_Jestar · 6 May 2007 at 03:25

you want to send the BLOB data to the client so they can view it?

Code:

<?php
header('Content-type: image/jpeg');

$result = mysql_query('SELECT * FROM table');
$row = mysql_fetch_assoc($result);

echo $row['image_data'];
?>

marc2003 · 6 May 2007 at 07:48

Beansprout said:
Also, marc2003, use mysql_real_escape_string() instead of addslashes()

i know nothing. i'm a complete php noob. i didn't create that, i googled it.

i shall now google those 2 functions to see what they are/do.

marc2003 · 6 May 2007 at 08:18

tuckenator said:
I am working on the same script as above and was wondering how would you go about retrieving images from the server that are relevant to the user logged in at the time?

Tucks

well you'd have to have another field in the db for the username which would be submitted when the picture is uploaded. then using list.php above the query would be something like

Code:

$query  = "SELECT id, name FROM upload WHERE user ='$user' ";

??best wait for the experts, like i say, i is noob.

oh and i see you've found your way to my webpage for your lastfm sig. it really shouldn't have been left public. i should have put a note on the front page that i can't guarantee 100% uptime for that. i'm running it on my home pc. and although i do leave it on pretty much 24/7 there are periods when it can be down for a few hours or more. feel free to carry on using it though.

tuckenator · 6 May 2007 at 13:10

got it working cheers marc, and cheers for the php sig, ha, didin't know it was running off ** pc :eek:

.

don't suppose anyone can help me with uploading the current date with the file upload? i have already got 'date' in my database with the type set to 'date'.

Cheers tucks

marc2003 · 6 May 2007 at 16:59

use the mysql function "current_date" in the query

Code:

$query = "INSERT INTO upload (name, size, type, content, date ) 
VALUES ('$fileName', '$fileSize', '$fileType', '$content', current_date)";

tuckenator · 6 May 2007 at 18:41

Cheers again, any idea why this image isnt displaying from the mysql database?

PHP:

<?
if(isset($_GET['id']))
{
include 'db.php';
   // Process signup submission 
   dbConnect('epicfx00_sms');

$id      = $_GET['id'];
$query   = "SELECT name, type, size, content FROM userimage WHERE userid = '$userid'";
$result  = mysql_query($query) or die('Error, query failed');
list($name, $type, $size, $content) = mysql_fetch_array($result);

header("Content-Disposition: attachment; filename=$name");
header("Content-length: $size");
header("Content-type: $type");
echo $content;

exit;
}
?>
<?
include 'db.php';
dbConnect('epicfx00_sms'); 

$query  = "SELECT id, name FROM userimage WHERE userid = '$userid'";
$result = mysql_query($query) or die('Error, query failed');
if(mysql_num_rows($result) == 0)
{
echo "You have no profile image to display<br>";
} 
else
{
while(list($id, $name) = mysql_fetch_array($result))
{
?>
<img src="download.php?id=<?= $id;?>" width="80" height="80">
<?
}}
?>