MySQL database setup help

Question

I am making a classifieds website... I have these 6 tables: Every category has sub-categories (or options) which you can see below.

Lets say the user wants to post a classified, and has entered all info into the forms necessary, and I am at the stage where I have to create the PHP code to actually INSERT the data into the database.

I am thinking something like this:

mysql_query("INSERT INTO classifieds (classified_id, ad_id, poster_id, cat_id, area_id, headline, description) VALUES ($classified_id, '$ad_id', $poster_id, $cat_id, $area_id, '$headline', '$description')");

But I don't know where to take it from here... I think the posters table should not be like this, because how should I determine what the poster_id should be? Or should I set it to auto-increment? Remember this, posters may not log in or anything, so there is no problem with one person having multiple poster_table records if you know what I mean.

classified_id is a random unique value generated by PHP so that is always unique.

Please guide me! I don't know how to link the tables together correctly.

If you have any Q let me know and I will update this Q!

category table:
cat_id (PK)
cat_name

category_options table:
option_id (PK)
cat_id (FK)
option_name

option_values table:
value_id (PK)
option_id (FK)
value

classifieds table:
classified_id (PK)
ad_id (VARCHAR) something like "Bmw330ci_28238239832" which will appear in URL
poster_id (FK)
cat_id (FK)
area_id (FK)
headline
description
price
etc....

posters table:
poster_id (PK)
name 
email
tel
password

area table:
area_id (PK)
area
community

Answer 1

You've got the right idea already. When someone creates a post, and enters their personal info, FIRST insert the "poster" record into the posters table. The "poster_id" primary key for that table should be an auto_increment field.

Next, get the ID of the new poster you just created using PHP's "mysql_insert_id". That integer value will be the number you put in the "poster_id" foreign key field in the "classifieds" table.

Answer 2

You should usually set the primary key to an auto-increment field.

When you have linked tables and you need to join on the id, you can first insert into the main table and then use the function mysql_insert_id to retrieve the id of the element you just inserted. You can then insert into the other table using this value as the foreign key.

This is a very standard way to do things, so it should be fine for you.