Databasing Images

[Shalazar]

If anybody has any experience with databasing a pretty sizable image archive (300+ images), I would be very interested in hearing specifics about how best to achieve an efficient image db archive.

I would like it to be searchable by keyword, so I figure the types of columns I need are:

1) Image name
2) Image Format
3) Image Dimensions
4) Image size
5) Keywords
6) Image path
7) # of hits
8) ID

The ID tag would be for calling the image, something like http://www.domain.tld/pictures/show.php?ID=15, as well as being a good track of how many photos are in the database.

# of hits would keep track of the times it was accessed, allowing for a presentation of, say, the Top 10 favorite files.

Questions I currently have are:

Whats the most efficient way to reference an image in a database?[nbsp][nbsp]Use a full path?[nbsp][nbsp]A relative path?[nbsp][nbsp]And what are the advantages/disadvantages of each?

I'd appreciate your experience....

[Shalazar]

Is there no answer to this simply because it got passed up, or because its out of the realm of experience of the folks who've read it?

I've tried searching the net for info, and can't seem to find anything on how to build an image gallery.[nbsp][nbsp]So if you have some information, or know where I can find some, I'd appreciate the help =)

[Justin]

If you are planning to store the images in various directories, I'd go with full path minus any common parts (eg, if they are all somewhere within /big/dom/xyourdomain/www, then leave that part off, adding it when you pull the images). If they are all within one directory (recommended), then just image name should be sufficient.

My ad software (PHP/MySQL, written by me) on my site simply stores each image in the /ads/ directory with filenames such as 000001.gif, with the number corresponding to the unique ID field. This makes it quite easy to select random images. I also have a field that determines file type (1 == GIF, 2 == JPG), to add the extention.

I do this because I require banners on my server, and they upload via the software - so when it creates the files it does so after inserting the entry into MySQL and retrieving the new ID.

My table looks like this:
</font><font face="Courier" size="3">
ID int(11)
URL varchar(100)[nbsp][nbsp] (to link the banner to)
owner int(11)[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp](ID of the sponsor)
height int(3)
width int(3)
alt varchar(50)[nbsp][nbsp][nbsp][nbsp](alt text)
imps int(11)
clicks int(11)
ext enum('gif','jpg')
name varchar(30)[nbsp][nbsp] (friendly name for identification)
</font><font face="Verdana, Arial" size="2">
A lot of the above is only really used by the sponsor. As for pulling the images, you would only need the ID (if you name your files with the ID), ext (file extention), height/width, etc - depending of course on your particular needs.

I would personally recommend against storing the image data in the database - it's best to use files, and store the filename/number/location/whatever.

Hope this helps.

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[nemesis]

Hey, Shalazar, have you checked out the gallery PHP script I made for the new redesign of buffyguide, yet?

I'm not using a database, but just pass the location of the file to the script (relative to the www directory).

It grabs the height and width info from the image itself and uses the filename as a default ALT tag unless you supply a different one with the URL.

I got fed up as well trying to find one already made that did what I wanted to do, so I just did it myself. I would like to add some fancier things to it like previous and next image links, but if you follow some sort of numbering scheme, it shouldn't be too difficult. (I just don't know how I would figure what the last image in the directory is and not give a link to a non-existant file.)

So, just let me know if it is something like this that you are trying to accomplish. The thumbnail pages are created manually right now, but would like to get it to create a thumbnail page eventually.

Obviously since this way doesn't use a databse, it wouldn't be searchable. Come to think of it, this is the way your site does it currently, doesn't it? Ah, well.

Jeremy
[This message has been edited by nemesis (edited 04-06-00@5:26 pm)]

[nemesis]

Hey Shalazar,

A while ago I happened to run across something I was going to refer you to, but I lost it. I just ended up at the site where I found it, so I dug around and found it again. It's called phpImageGallery -- I don't know if it's exactly what you want, but at least it might give you some inspiration in writing your own. :)

http://www.hotscripts.com/cgi-bin/search.cgi?query=image+gallery&amp;category=PHP

Edit -- Actually, there are a couple with potential. Try this link: http://www.hotscripts.com/PHP/Scripts_and_Programs/Image_Manipulation/Image_Display/index.html

Hope this helps!

Jamie
~~~~~~~~~~~~~~~~~~~~~~~
www.BuffyGuide.com (http://www.BuffyGuide.com)
[This message has been edited by nemesis (edited 04-09-00@8:50 pm)]

[4bil]

Hi,

I don't know if this will work for all your 300+ images but I think it might be worth a try. It is primarily designed as a banner management system. It will give hits and visitor stats on each image viewed, I think the rest of the data you require can also be derived from the db it creates.

http://www.phpmyadmin.com - phpads

Charles