Not sure whether this is in right forum but someone here should know:

[dc_jt]

I am doing a cms on an advertising company.
When someone submits a listing, their listing can come under one or more categories.

I have a table with categories in (bars, restaurants, clubs etc)

I have another table called listings which contains the listing_id, name, address, tel_number etc and CATEGORIE_ID.

So when someone submits their listing, they can choose it to go under the bars AND restaurants category.

However, when I add the data into the table how do i add both category IDs into the category_id field in listings?

Or do I need another table to link them and if so how?

Thanks a lot, really struggling getting my head round this

[onlyican]

What I would do in this situation is
have one table holding most of the main data
One table holding the types
then a third table which links ID's

Such as
TABLE businesss
business_id    int(50) primary, auto_increment
business_name  varchar(150)
...The other fields for the bussiness

TABLE catagory
catagory_id    int(50) primary, auto_increment
catagory_name  varchar(150)


TABLE buss_to_cat  (Just means Business TO Catagory

buss_to_cat_id    int(50) primary, auto_increment
business_id        int(50)
catafory_id      int(50)


Then you can have the data stored in Business
then say Catagory ID 1 was "Bar" and 2 was "Restuarant", the table buss_to_cat can be

buss_to_cat_id        |            bussiness_id            |        catagory_id
1                            |                      1                |              1
2                            |                      1                |              2


I hope this made some sence, not sure how you are with mySQL

[dc_jt]

Hi

That made perfect sense, and thats exactly how i have it at the minute because I thought this would be the method.

The thing that was puzzling me was the fact that I have tick boxes for each category in my listings form.

So if listing X wanted to be in category Bars and category Restaurants, how would I fit that into this:

[quote]public function AddListings($aPostData)
{
if (!$this->ValidateData($aPostData,$sFileName)) return false;

$sSql = "INSERT INTO $this->sTableName SET
`category_id` = '$aPostData[category_id]',
`name` = '$aPostData[name]',
`address` = '$aPostData[address]',
`postcode` = '$aPostData[postcode]',
`telephone_number` = '$aPostData[telephone_number]',
`fax` = '$aPostData[fax]',
`email` = '$aPostData[email]',
`description` = '$aPostData[description]' ";

return mysql_query($sSql, $this->oDb->GetConnection());
}[/quote]

Would I need another function insert into the link table somehow?

Thanks a lot for your help

[pnj]

MySQL has sets which allow you to create up to 64 identifiers in a long integer field, each of which corresponds to one bit in that field.

[url=http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html]http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html[/url]

You might use them something like:

[code]CREATE TABLE business (
  [ ... ]
    category SET('Restaurant','Bar','Club')
  );[/code]

You can insert into that value:
[code]INSERT INTO business(category) VALUES('Restaurant,Bar');[/code]

The documentation cautions against using these as its uses is much more limited than a normalized table, but for cases where you know there won't be more than 64 categories, it can simplify things a bit.

-pnj

[dc_jt]

I dont really understand the above post sorry

I have a form with checkboxes for each category like

bars          [] (<--Thats meant to be a checkbox)
restaurants  []
clubs          []

So if they tick bars and clubs how do i create the function for these to go in the linkcategories table?

I tried:

[quote]public function AddLinks($aPostData)
{
if (!$this->ValidateData($aPostData,$sFileName)) return false;

$sSql = "INSERT INTO $this->sTableName SET
`listings_id` = '$aPostData[listings_id]',
`category_id` = '$aPostData[category_id]' ";

return mysql_query($sSql, $this->oDb->GetConnection());
}[/quote]

Any idea?

Thanks

[Anidazen]

Unless I'm missing something can't you just list all the IDs in the same table-field, seperated by commas? Then just reference them with "WHERE category_ID LIKE '%$id%'" instead of "WHERE category_ID = '$id'"