By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,910 Members | 1,085 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,910 IT Pros & Developers. It's quick & easy.

Help with photo album database...

P: n/a
I am building a photo album webpage and am not sure exactly how I
should organize the database. Here's where Im at so far...

I have one table called 'images' which contains the columns 'albumID',
'url', 'title', and 'description'. Then another table called 'albums'
which contains the column 'name'.

The part where I am having the most trouble is the 'images.albumID'
column. Some photos should be placed in mutliple albums, so should the
'albumID' column be an array type?

Also, I would like to have sub-albums within larger albums. So instead
of having a column called 'images.albumID', should I instead have an
array column called 'images.location' which declares the locations of
where the photo exists? (eg. "Nick's photo album > Vacations > Hawaii"
and "Nick's photo album > Sports > Surfing"). I guess I would also
have to add a 'location' field to the 'albums' table also so they can
be nested.

I would then access the images through JSP/PHP/ASP/etc with ("SELECT *
FROM 'images' WHERE 'location' = albums.location")

Does anyone have any experience with something like this or know of
the best way to do this??? -Nick
Jul 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Nick wrote:
I am building a photo album webpage and am not sure exactly how I
should organize the database. Here's where Im at so far...

I have one table called 'images' which contains the columns 'albumID',
'url', 'title', and 'description'. Then another table called 'albums'
which contains the column 'name'.

The part where I am having the most trouble is the 'images.albumID'
column. Some photos should be placed in mutliple albums, so should the
'albumID' column be an array type?

Also, I would like to have sub-albums within larger albums. So instead
of having a column called 'images.albumID', should I instead have an
array column called 'images.location' which declares the locations of
where the photo exists? (eg. "Nick's photo album > Vacations > Hawaii"
and "Nick's photo album > Sports > Surfing"). I guess I would also
have to add a 'location' field to the 'albums' table also so they can
be nested.

I would then access the images through JSP/PHP/ASP/etc with ("SELECT *
FROM 'images' WHERE 'location' = albums.location")

Does anyone have any experience with something like this or know of
the best way to do this??? -Nick


For the photo album that I created, the database structure looks like this:

CREATE TABLE gallery_index (
category int(10) unsigned NOT NULL default '0',
image int(10) unsigned NOT NULL default '0',
KEY x (category,image)
) TYPE=MyISAM;

CREATE TABLE gallery_images (
id int(11) NOT NULL default '0',
description text NOT NULL,
file varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

CREATE TABLE gallery_categories (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
description text NOT NULL,
num_pics int(11) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
I have attached the class definition that I use so you can get an idea
of how this datbase would work.

I only use one category (album) for each image, but to display it in
additional categories, you'd just have to add additional rows to the
gallery_index table - no need for duplicate entries in the other tables.

I really cannot releases any more of the code than this, but hopefully
it's something to get you started with. The class uses MetaBase for
database abstraction, so the SQL may be a little difficult to follow at
first.

HTH

--
Justin Koivisto - sp**@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.