Good Morning!

I guess I will start with a little background and then elaborate on the problem.

I run a website for a car dealership with many different locations. The site runs relatively well and all information is stored in a MySQL database except for the image information. Due to the number of cars in inventory the image directory on the server quickly becomes bloated and I am looking for a better way to manage this. I would also like to be able to upload the images through the form that the vehicles are added to the site.

I have racked my brain for hours for what should be a simple answer. I want to be able to upload files to the server through the form rather than the ftp client, but I would like some manageable way to store the files.

My thoughts have been:
make a directory based on the stock-number input and upload the images into that directory. Then when I go to recall the data run a count of the images in that directory to know how many to load. But then I run into the problem of not knowing exactly what the files are called.

I'd also like to be able to automatically generate thumbnails based on a specific size. The images that are uploaded by default are 640 x 480 px and are JPG in format. I currently just run a batch in photoshop to reduce all the image into the correct size thumbnails and store them into the thumbnail directory.

So I guess my problem is 2 fold:
What kind of structure should I upload the files into and how to name them.
And how is the best way to find out how many photos there are and retrieve them?

And I guess I have one other issue I thought of when writing the notes below, whats the best way to delete the images when they are no longer need?


**** Other notes ****

  1. Images are currently named as stocknumber-picture number. The photos are in order from 1 to 6 so they show up in the correct positions.
  2. Removing photos of sold cars is currently a pain because I have to go through and manually write a script to delete them from the server.
  3. The database always keeps a copy of the original stocknumber. This is so whenever it is transferred I do not have to rename the pictures.

I realize the answer is probably simple and staring me right in the face, but I'm tired of being at a loss.

Thanks!

Member Avatar for brewbuff

Files in the stock-number folder should be called 1.jpg through 6.jpg. You are on the right track. Here is a function to do the thumbnails.

function createthumb($oldname,$newname,$new_w,$new_h) {
  	$src_img=imagecreatefromjpeg($oldname);
  	$old_x=imageSX($src_img);
  	$old_y=imageSY($src_img);
  	$dst_img=ImageCreateTrueColor($new_w,$new_h);
  	imagecopyresampled($dst_img,$src_img,0,0,0,0,$new_w,$new_h,$old_x,$old_y); 
  	imagejpeg($dst_img,$newname); 
  	imagedestroy($dst_img); 
  	imagedestroy($src_img); 
  }

I would like to make sure I am understanding this function correctly.

in line 2 it makes a copy of the current image and stores it as a variable
in line 3 it grabs the current x dimension
in line 4 it grabs the current y dimension
in line 5 it makes a new image variable with the dimensions
in line 6 it resizes the old image and saves it to the $dst_img variable
- I'm not sure what the 0s are for
In line 7 it saves the image, and renames it to the new file name
line 8 and 9 destroy the temporary images stored into the variable.


And then all I would need to do is setup a foreach loop with a file name to convert the files, or would it be better to perform it at the time of upload?

I dont know if i am understanding you correctly, but i am busy with a similar project.
what i did was:

When the admin adds a vehicles they select up to three images per vehicle, the images are stored in the row if the vehicle with field names, image1, image2 and image3. here i only store the image time as md5() * time() to keep it unique,

when the car is sold the admin deletes the entire row including model, make, price and all images etc etc.

if i needed the stock number i would ad it as a field in the row of the db where the car details are held

I am as new to this as you so my reply to your question above is my best effort,
ImageCreateTrueColor created a black image.

the first 0,0, is the x and y co-ordinate of $dst_img (the new black image), the second 0,0 is the x and y co ordinates of $src_img (the original), in simple terms copy the entire source image onto the destination image.

I do not want to store the images into the database. The site has to many vehicles and to much traffic. It would overtax the resources of my server, plus storing the images as a flat file increases page response time, and reduces load time.

Member Avatar for brewbuff

Your understanding is correct. The 0s would be used for cropping. I would write a utility to make thumbnails of existing images if you need them but I would create the thumbnails upon upload from now on. You will need the PHP/GD functions to do this.

sorry, i probably didnt explain myself clearly, store only the image name in the database and the actual images in a folder. i cannot post my code right now, but if you dont find a solution soon, ill try a bit later.

in a nut shell, this is what id do:
first add <input type="file"> to the form,

just as you usually process your form data add the following to manage your images

$imagename = ($_FILES['userfile']['name']['0']);
$temp_image = ($_FILES['userfile']['tmp_name']['0']);

Your image name would be in $imagename, now you can manipulate the image name, check for extensions etc.

Your temp image would be in $temp_image, now you can just upload the image to a designated folder on the server with the function move_uploaded_file, or
if you need to resize, create thumnail etc, your image upload function would probably be imagejpeg()

with your normal mysql query that saves your other form data, you add the name of the image to the database.

Your result should be an image saved on your server and a database row with all your cars details including the image name.

sorry that i cant be of better assistance right now but Hope this helps

What my thoughts were was to do as follows:

(Psuedo)
Upload files
create directory under images / cars / with the stocknumber
loop
save file 1 to this directory at 640 x 480 px with the name of 1.jpg
save file 1 thumb to this directory at whatever size i want my thumbnails to be with the name of 1t.jpg
change the number 1 to 2 
go to next file
repeat process until all uploaded files are complete

Then when I go to output the information I'll be able to run a count on the files in the directory:

(Psuedo)
Set the image directory to the original stocknumber of the vehicle.
count the number of files in the directory and divide by 2
loop through the files showing all the thumbnails.

And everyone is a happy camper, no one knowing the wiser.

I believe you are barking up the wrong tree.
Storing image data in the database does not affect database performance. Database performance is mainly determined by table structure and indexes, not by the amount of data stored in the DB. If pictures have their own table and you use the implicit mysql query cache the performance loss is minimal.
Also your argument of website / HTML performance is not true. The performance is the same, regardless of the content of the IMG src attribute which would make the only difference in not pointing to a physical file but to PHP script which outputs the image data.
Bookkeeping, moving and deleting images with their rows would be much easier. You could not use batch conversion programs like convert, though, without first exporting images from the database.

I believe you are barking up the wrong tree.
Storing image data in the database does not affect database performance. Database performance is mainly determined by table structure and indexes, not by the amount of data stored in the DB. If pictures have their own table and you use the implicit mysql query cache the performance loss is minimal.
Also your argument of website / HTML performance is not true. The performance is the same, regardless of the content of the IMG src attribute which would make the only difference in not pointing to a physical file but to PHP script which outputs the image data.
Bookkeeping, moving and deleting images with their rows would be much easier. You could not use batch conversion programs like convert, though, without first exporting images from the database.

While for smaller sites I agree with you, our site generates a large amount of traffic, and has more than 400 cars active at any time, and normally garners about 50-60k unique visitors a month.

From my research though storing into a database can affect performance. Especially with file sizes around 100kb. The database currently has a little over 3000 records since I last purged it. That would make the size of the database (if pictures were added) 1.76gb roughly.

Also in the past I have had issues restoring the SQL database from backup with images in it.

So my preference is to use the file system.

Between 2000 and 2005 I ran a website with 15 mio. page impressions / month and a few thousand users with a few ten thousand of pictures in a MySQL 3.21 database. No performance problems and easy handling.
But if you go for flat files keep in mind that to many files in one directory can degrade the performance quite a bit. On other projects I limited the number of files to about 1.000 per directory. You could for example use the last three digits of a car number as the directory name.
For deleting I would either write a cronjob which loops through image directories and checks for each file if its parent record in the database has the "deleted" flag set (I hope that you do not physically delete old records). Or I would write a query which lists all deleted image names and paths and feed it to a delete script (as a cronjob, too).

Between 2000 and 2005 I ran a website with 15 mio. page impressions / month and a few thousand users with a few ten thousand of pictures in a MySQL 3.21 database. No performance problems and easy handling.
But if you go for flat files keep in mind that to many files in one directory can degrade the performance quite a bit. On other projects I limited the number of files to about 1.000 per directory. You could for example use the last three digits of a car number as the directory name.
For deleting I would either write a cronjob which loops through image directories and checks for each file if its parent record in the database has the "deleted" flag set (I hope that you do not physically delete old records). Or I would write a query which lists all deleted image names and paths and feed it to a delete script (as a cronjob, too).

Thanks Smantscheff, I really appreciate your input on this.

So here is where I am standing right now on all of this:

  1. Store images to the operating system under a folder for each individual stocknumber
  2. Resize all the images to make sure they are 640x480 px for the larger images
  3. Duplicate and make thumbs at a yet to be determined thumbnail size (I'm not done with the new page yet so not sure exactly how big they will be.)
  4. Files will need to be renamed 1, 2,3, etc.jpg

As far as validation of images, I think I really only need the following:

  • Size is less than 2 mb
  • Photo type is jpg

Things I am not really sure on:

  • Script to upload the images
  • A way to make sure that at least 6 images are uploaded.
  • If one of the six images isn't uploaded, to use a generic image unavailable.
  • Should I store the number of pictures into the database, or should I just run a count on the files in the directory?

I know this last part is probably pointless to put on here, but I am looking for any additional input because once I make this change, I don't want to have to change it in the future! I'm sure we all know that one.

For uploading images and other files there are lots of PHP scripts around for paste and copy.
For image processing you might use the gd library functions, or an external program like ImageMagick's "convert".
If you convert images on upload to the desired size you can also make sure that they are valid (e.g. with ImageMagick's "identify").
If each stock item has its own folder, count the items in it instead of storing a number in the database which may be out of sync at some point in time.
Keep in mind that for the number of subdirectories (your image folders) in one directory the same warning applies as for the number of files. If there are too many, performance will suffer. Use a hierarchical system which will guarantee a maximum number of entries per directory.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.