converting BLOB to jpeg MySQL/PHP

Soldato
Joined
10 Apr 2006
Posts
7,890
Location
North West
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 :D
 
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);
?>
 
Last edited:
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 ?
 
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. :)
 
Last edited:
how sad am i? :eek: i actually installed mysql and googled this and got it working.... :p

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;
}
?>
 
Last edited:
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 :)
 
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? :)
 
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.
 
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? :p
 
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.
 
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 :D will tell him what you said though :p
 
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. :)
 
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
 
tsinc80697 said:
Well the database was designed by someone doing their masters in software engineering
So no real-world experience then :p

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

Also, marc2003, use mysql_real_escape_string() instead of addslashes() :)
 
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'];
?>
 
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. :p

i shall now google those 2 functions to see what they are/do. :)
 
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. :D

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. :)
 
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
 
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)";
 
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">
<?
}}
?>
 
Back
Top Bottom