SQL syntax & help with foreign key

[gat]

Here is the site im working on.
http://art.uk-graffiti.co.uk/index.php

Here is my database structure
http://art.uk-graffiti.co.uk/erd_artwork2.gif

My Problem is this:

When i submit a new piece of artwork through my admin pannel. I can assign many sub categorys for one image.

I am unsure however what table to submit this info to, and how to correspond to an art_id.

e.g: all the image information is submitted into the artwork table, but the sub_cat infor submitted will need to be put in the sub_cat table with a corresponding art_id.

Here is the gallery
http://art.uk-graffiti.co.uk/gallery_cats.php

I would like to be able to click on a sub category from the list which will query the DB for all artwork from that sub_cat.

But as all my table links rely on foreign keys and i do not know how to implement this using SQL querys, im unsure on how to get all this going.

Any examples?
Would you like me to explain my self more clearly?

Thanks in advance.

[nemonoman]

So you've got your basic table:

Artwork

ID
Name, artist, etc., etc.

And probably you should have a Subcategory table

Subcategory

ID
Name, etc. etc

You need a 'Many to Many' Table to link the two:

Artwork_Subcategory:

ArtworkID
SubcategoryID

When you submit the artwork, you'll probably use a SELECT with the attribute MULTIPLE. You'll name it something like subs[], being sure to use brackets in the name.

Then when the form is submitted you can check $_POST ['subs[]']

It will contain an array of all the subcategories selected.

Then you insert all the subcategories for the artwork into Artwork_Subcategory.

To find all the artwork for a subcategory:

SELECT Artwork.* from Artwork, Artwork_Subcategory WHERE ArtworkID=Artwork.ID and SubcategoryID=2

Hope this gets you started. THere are books and Web Tutorials about this: Google: Relational Database Programming SQL Tutorial

[gat]

yes, you've hit it bang on.
my DB is formed exactly as you have said.

at the moment, the sub_cats are posted in the form

landscape_3d_photo

so i will need to explode the results and post them.
But do i not post them to the 'sub_category' table....not the 'artwork_category' table?

Getting my head arround this is proving very difficult for some reason. I sort of understand the principles, but applying it is another thing.

I will do some google searches etc, but if anyone would like to post any examples that would prove very greatful.

many thanks

[nemonoman]

Please reread my earlier post:
You insert the results in a 'many to many' table, e.g. Artwork_Subcategory.

ONE piece of Artwork is associated with MANY subcats

ONE subcat is associated with MANY artworks

You record this many-to-many relationship in the relationship table: Artwork_Subcategory.

[swr]

Another good google search term:
database normalization