Fine-tuning my MySQL relations database and how to use JOIN on it?

Question

I am very new to mysql and databases, so I need help here...

How should I use JOIN to fetch a record from the tables below, when the only given variable is the ad_id?

category and category options are filled in manually i.e. the users may not alter them. So they are only reference tables, but I am not sure this is how I should do it... I have 6 tables:

category:
cat_id (PK)
cat_name

category_options:
option_id (PK)
cat_id (FK)
option_name

option_value:
value_id (PK) (AI) // I think this should have Auto Increment
option_id (FK)
classified_id (FK)
value

classified: (THIS IS THE MAIN TABLE YOU COULD SAY)
classified_id (PK) (AI)
ad_id
cat_id
headline
description

area: // I am thinking about moving these fields over to the posters table, right?
area_id (PK)
classified_id (FK)
area
description

Here is how I insert a classified into the tables:

mysql_query("INSERT INTO classified (ad_id, cat_id, headline, description) VALUES ('$ad_id', $cat_id, '$headline', '$description')");
$last_classified_id=mysql_insert_id();

mysql_query("INSERT INTO poster (classified_id, name, email, tel) VALUES ($last_classified_id, '$name', '$email', '$tel')");

mysql_query("INSERT INTO area (classified_id, area, community) VALUES ($last_classified_id, '$area', '$community')");

I am new to JOIN!

Every category has sub options (CARS -> color) and every option has a value. I want to, only by having the ad_id, select all this information.

How can I do so?

And should I merge the area and posters table? Also, please take a careful look at my db, and tell me if there is anything I might have missed...

This is really out of my knowledge-base so detailed explanations are appreciated!

Thanks

Answer 1

It looks like you have two fields that could be the primary key in classified: classified_id and ad_id. Then you have two other tables, poster and area, that have a one-to-one correlation with classified. If this is the case, you could put all the fields in classified.

A query to join the tables in your insert statements would like like this:

select
    classified.ad_id,
    classified.classified_id,
    classified.headline,
    classified.description AS classified_description,
    poster.name,
    poster.email,
    poster.tel,
    area.area,
    area.description AS area_description from
    classified inner join
    poster
    on
        classified.ad_id = poster.classified_id inner join
    area
    on
        classified.classified_id where
    classified.ad_id = 123

Answer 2

This is an example of joining some of your tables to get data from more than one of them:

SELECT c.cat_name, co.option_name, cl.headline
FROM category c 
INNER JOIN category_options co ON co.cat_id = c.cat_id
INNER JOIN classified cl ON cl.cat_id = c.cat_id
WHERE cl.ad_id = {Your ad_id}

You can join to any other tables needed in the same way (poster, area).

Edit (response to comment): The 'c', 'cl', and 'co' are aliases for the 'category', 'classified', and 'category_option' tables. They don't have anything to do with the join. Here's a source. When I say FROM category c, that allows me to use 'c' as the a shortcut for the category table. Using aliases allows you to make the select/joins/where clause how I did instead of like this:

SELECT category.cat_name, category_options.option_name, classified.headline
FROM category
INNER JOIN category_options ON category_options.cat_id = category.cat_id
INNER JOIN classified ON classified.cat_id = category.cat_id
WHERE classified.ad_id = {Your ad_id}

Basically it's a shortcut that can save you some typing.