MYSQL image database

1

Simple question.

[SIZE=3]** If you are creating a large image database do you:**[/SIZE]

A. Upload the image into MYSQL ?

**B. **Upload the image to a folder and store only the address in MYSQL ?

**A=> **MYSQL does have a table size limit. (though perhpas larger than I need to worry about)

**A=> **Will not have to worry about images with the same name as you would not be using that as the key.

**B=> **Will save MYSQL from having to pull images from database.

**B=> **Will have to save each image with a different name.

Ok these are my first and only thoughts on the subject. Was hoping someone knows which is the better method and why ?

Any tips much appreciated.

2

You can normally compress it a bit if you store it in the database using some encryption algorithm. It’ll take a tiny bit less space on your server, and accessing will be a bit more dynamic.

It might take some extra code, but it can be worth it.

3

I would definitely suggest files and folders with links in the db. It’s much cleaner in my opinion, and really, the naming files differently can be handled in php, and probably most any other language, automatically (temp names are given, and I’m sure you can do something with this).

It just seems much better…

4

Not that it makes much difference but I am also going to have to resize and create thumbnails, and perhaps water mark each image with PHP before I either store it in MYSQL or save it in a folder.

5

It actually really slow to keep binary in your database, I do not recommend doing it. I would definately recommend you store the images on your server and put a path fieled in your database. It’s a little hard to keep track of, but it’s much faster.

Edit: after reading your last post i should tell you, i am really familiar with doing this, i even have a some code for you to play with:

<?php

	$MAXIMUM_FILESIZE = 1024 * 1500; // 200KB
	$MAXIMUM_FILE_COUNT = 1000; // keep maximum 1000 files on server
	$filename = $_GET['filename'];
	$galleryname = $_GET['gallery'];
	
	if ($_FILES['Filedata']['size'] <= $MAXIMUM_FILESIZE) {
		unset($_FILES['Filedata']['name']);
		$_FILES['Filedata']['name'] = $filename.".jpg";
		mkdir("./gallery/".$galleryname."/");
		mkdir("./gallery/".$galleryname."/full/");
		mkdir("./gallery/".$galleryname."/thumbs/");
		move_uploaded_file($_FILES['Filedata']['tmp_name'], "./temporary/".$_FILES['Filedata']['name']);
		rename("./temporary/".$_FILES['Filedata']['name'], "./gallery/".$galleryname."/full/".$_FILES['Filedata']['name']);		
	};
		$fullsize = "./gallery/".$galleryname."/full/".$_FILES['Filedata']['name'];
		$thumbsize = "./gallery/".$galleryname."/thumbs/".$_FILES['Filedata']['name'];
		// The file
		$filein = $fullsize; // File in
		$fileout = $thumbsize; // Fileout - optional 
		
		$imagethumbsize_w = 95; // thumbnail size (area cropped in middle of image)
		$imagethumbsize_h = 64; // thumbnail size (area cropped in middle of image)
		resize_then_crop( $filein,$fileout,$imagethumbsize_w,
		$imagethumbsize_h,/*rgb*/"255","255","255");
		
		function resize_then_crop( $filein,$fileout,$imagethumbsize_w,$imagethumbsize_h,$red,$green,$blue){
		
		// Get new dimensions
		list($width, $height) = getimagesize($filein);
		$new_width = $width * $percent;
		$new_height = $height * $percent;
		
		   if(preg_match("/.jpg/i", "$filein"))
		   {
			   $format = 'image/jpeg';
		   };
		   if (preg_match("/.gif/i", "$filein"))
		   {
			   $format = 'image/gif';
		   };
		   if(preg_match("/.png/i", "$filein"))
		   {
			   $format = 'image/png';
		   };
		   
			   switch($format)
			   {
				   case 'image/jpeg':
				   $image = imagecreatefromjpeg($filein);
				   break;
				   case 'image/gif';
				   $image = imagecreatefromgif($filein);
				   break;
				   case 'image/png':
				   $image = imagecreatefrompng($filein);
				   break;
			   };
		
		$width = $imagethumbsize_w ;
		$height = $imagethumbsize_h ;
		list($width_orig, $height_orig) = getimagesize($filein);
		
		if ($width_orig < $height_orig) {
		  $height = ($imagethumbsize_w / $width_orig) * $height_orig;
		} else {
		   $width = ($imagethumbsize_h / $height_orig) * $width_orig;
		};
		
		if ($width < $imagethumbsize_w)
		//if the width is smaller than supplied thumbnail size 
		{
		$width = $imagethumbsize_w;
		$height = ($imagethumbsize_w/ $width_orig) * $height_orig;;
		};
		
		if ($height < $imagethumbsize_h)
		//if the height is smaller than supplied thumbnail size 
		{
		$height = $imagethumbsize_h;
		$width = ($imagethumbsize_h / $height_orig) * $width_orig;
		}
		
		$thumb = imagecreatetruecolor($width , $height);  
		$bgcolor = imagecolorallocate($thumb, $red, $green, $blue);  
		ImageFilledRectangle($thumb, 0, 0, $width, $height, $bgcolor);
		imagealphablending($thumb, true);
		
		imagecopyresampled($thumb, $image, 0, 0, 0, 0,
		$width, $height, $width_orig, $height_orig);
		$thumb2 = imagecreatetruecolor($imagethumbsize_w , $imagethumbsize_h);
		// true color for best quality
		$bgcolor = imagecolorallocate($thumb2, $red, $green, $blue);  
		ImageFilledRectangle($thumb2, 0, 0,
		$imagethumbsize_w , $imagethumbsize_h , $white);
		imagealphablending($thumb2, true);
		
		$w1 =($width/2) - ($imagethumbsize_w/2);
		$h1 = ($height/2) - ($imagethumbsize_h/2);
		
		imagecopyresampled($thumb2, $thumb, 0,0, $w1, $h1,
		$imagethumbsize_w , $imagethumbsize_h ,$imagethumbsize_w, $imagethumbsize_h);
		
		// Output
		//header('Content-type: image/gif');
		//imagegif($thumb); //output to browser first image when testing
		
		if ($fileout !="")imagegif($thumb2, $fileout); //write to file
		header('Content-type: image/gif');
		imagegif($thumb2); //output to browser
		};
//------start thumbnailer
   $thumbsize=300;
   $imgfile = $fullsize;
   header('Content-type: image/jpeg');
   list($width, $height) = getimagesize($imgfile);
   $imgratio=$width/$height;
   if ($imgratio>1){
     $newwidth = $thumbsize;
     $newheight = $thumbsize/$imgratio;}
   else{
     $newheight = $thumbsize;
     $newwidth = $thumbsize*$imgratio;}
   $thumb = ImageCreateTrueColor($newwidth,$newheight);
   $source = imagecreatefromjpeg($imgfile);
   imagecopyresampled($thumb, $source, 0, 0, 0, 0, $newwidth, $newheight, $width, $height);
imagejpeg($thumb,$fullsize,100);
?>
6

i’ve never stored images in a mysql db. seemed a bit heavy and dirty. anyone know what the path looks like when you store an image in a db (ie when you right click on the image and goto properties)?

7

Thanks for the Posts guys.

And thanks for that piece of code Defective I will definitely try to put it to good use.

If anyone else has anything to add to the subject please do.

8

It seemed like something like that should be true… I just couldn’t say it definitively. :thumb:

9

i once used mysql to store images, never again its just a pain in the *** i would always use paths

10

Well I think that the votes are in and Paths are the way.

11

Of course, the fun way is to store the image in the database :slight_smile:

(Anything that requires more PHP is fun :D)