[GRedner]

This will be detailed one...

I recently rewrote my webpage (locally hosted!) in PHP with a MySQL backend. I am completely new to all of this, so I assume that the solutions I implemented are non-optimal. Basically, how would you structure this?

Webpage info:
Webpage is a photo gallery site. Each photo has a base name (image path and thumbnail path are derived from the name), a title and associated info (location, date, etc). The pictures are arranged in categories.

Each category has a name, title, etc. Pictures can appear in more than one category, or in no categories (i.e. not appear at all).

What I'd like to ask is, how would you set up your MySQL tables?

-Gabe

edit: I supose I should tell you how I did it so you can tell me how wrong I was.

Table:
images - each row holds an image name, date, etc.
categories - each row holds category name, title, etc.
Each category has a table with the same name as the category. These tables simply hold a list of the names of the images in that category.

Thanks for your help.

[ 05-05-2002: Message edited by: GRedner ]

[Mactoid]

Originally posted by GRedner:
<STRONG>What I'd like to ask is, how would you set up your MySQL tables?
...
Table:
images - each row holds an image name, date, etc.
categories - each row holds category name, title, etc.
Each category has a table with the same name as the category. These tables simply hold a list of the names of the images in that category.</STRONG>

The way I see it you only need 3 tables. The images and categories tables that you already have, and then the third is a table that stores the relations between them.

This table would have 2 fields: a foreign key pointing to an image, and a foreign key pointing to a category.

This way, you have an entry in that 3rd table for each category that an image is in, so if image "mycat" is in categories "foo" and "bar", then you would have two entries for "mycat", one that links it to "foo", and one that links it to "bar".

Make sense to you? That is how I would do it.

[timster]

If you really want to write it yourself, you can ignore this.

But I would suggest checking out Gallery
http://www.sourceforge.net/projects/gallery

It's a really well done photo album site, and its easy to set up and get going. Save yourself the effort of having to reinvent the wheel, and since its open source, you can diddle around with the source to implement features you'd like to put in.

-tim