images in mysql db

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

AH HA!

Post by yamokosk »

FINALLY! I got it working! Cactus, cheers. Thanks for all the help!

Wayne - I don't think i need to use stripslashes. One because its working without it, and two because encode64 turns the binary into a string of normal characters. So theres no need to add slashes.

So I went looking to see what "image/pjpeg" was and found out that its a progressive jpg (kinda like a interlaced gif). Only newer browsers support pjpegs.

Anyways, while looking up pjpegs, I found someone on another forum who had the same problems I was having. Someone mentioned to him to use:

Code: Select all

ob_end_clean();
ob_start();
right before the header call. I guess that clears out the output buffer before you call the header function. And thats all I needed! It works perfectly now.

I will post the final version of the code just in case anyone else has this problem and stumbles upon this posting... maybe I will even make a class dedicated to handling images in databases... so much to do and so little time.
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Excellent, glad you sorted it for yourself, it feels better that way :)

Regards,
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

The final, working code

Post by yamokosk »

Ok, here it is... in all its glory. The final working version.

First, my image database is formed by two different tables. One table contains the filename, filesize, filetype, a description, and a key to reference this image by. The other table stores that image_id, a file_id, and the actual image data. The file_id is used so that images can be chopped up into smaller bits when inserted into the database. This is nice because some DB servers limit how large data packets can be.

So here is the function I used to put images into the DB:

Code: Select all

function add_image($article_id, $description, $file) {
	$db = DB::connect(CMS_DSN);
	if (DB::isError($db))
		die ($db->getMessage());
	
	if ($file['size'] > MAX_FILE_SIZE) {
		return "ERROR: File is larger than max allowed.";
	}
	
	$sql = "INSERT INTO cms_images (article_id, description, filename, filesize, filetype) 
			VALUES ('" . $article_id . "', '" . $description . "', '" . $file['name'] . "', '" . $file['size'] . "', '" . $file['type'] . "')";
			
	$result = $db->query($sql);
	if (DB::isError($result))    
		die ($result->getMessage());
	
	$id = mysql_insert_id($db->connection);
	
	$fp = fopen($file['tmp_name'],"r");
	
	while (!feof($fp)){
		$data = base64_encode(fread($fp, MAX_SQL));
		
		$sql = "INSERT INTO cms_imagedata (image_id, data) 
				VALUES ('" . $id . "', '" . $data . "')";
				
		$result = $db->query($sql);
		if (DB::isError($result))    
			die ($result->getMessage());
	}
	fclose($fp);
	$db->disconnect();
}
Now to view the images, I put the following in a HTML file:

Code: Select all

<img src="a/path/to/image.php?id=??">
And image.php looks like this:

Code: Select all

require_once('DB.php');
require_once('db_connections.php');

$fetchmode = DB_FETCHMODE_ASSOC;
$images = array();
$image_id = isset($HTTP_GET_VARS['id']) ? $HTTP_GET_VARS['id'] : NULL;

$db = DB::connect(CMS_DSN);
if (DB::isError($db))
	die ($db->getMessage());
	
$sql = "SELECT data
		FROM cms_imagedata
		WHERE image_id = '" . $image_id . "'
		ORDER BY file_id DESC";

$result = $db->query($sql);
if (DB::isError($result))    
	die ($result->getMessage());

while ($row = $result->fetchRow($fetchmode)) {
	$images['filedata'] .= $row['data'];
}	

$sql = "SELECT filetype
		FROM cms_images
		WHERE image_id = '" . $image_id . "'";

if (is_array($row = $db->getRow($sql)))
	list($filetype) = $row;

$db->disconnect();

ob_end_clean();
ob_start();
header("Content-type: " . $filetype);
header( "Content-Disposition: inline");
echo base64_decode($images['filedata']);
And thats it!
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Well done, and forgot to mention, nice use of the PEAR libs, people should really give the libs a try if you have access to them.

Regards,
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

tnx

Post by yamokosk »

Yea the libs are really easy to use. Although if you notice, I am kind of cheating with this in the function to insert images:

Code: Select all

$id = mysql_insert_id($db->connection);
I have not done a lot of research on this yet, but from what I understand, PEAR DB does not and will never have a function similar to mysql_insert_id. But this is only temporary for me. I am going to try and achieve the same thing with PEAR DB and then it will be fully portable to any DB.
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

PEAR DB is a great abstraction layer for DB conectivity, it's never meant to replace or package up all the database functions supported by PHP.

What you have done is completely valid, (as you know) the only issue would be is you decided to switch to PostGres (for example), you would have to go through your code and change these (mysql) references.

:)

Regards,
Post Reply