dc_jt Posted November 8, 2006 Share Posted November 8, 2006 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 Quote Link to comment Share on other sites More sharing options...
onlyican Posted November 8, 2006 Share Posted November 8, 2006 What I would do in this situation ishave one table holding most of the main dataOne table holding the typesthen a third table which links ID'sSuch asTABLE businesssbusiness_id int(50) primary, auto_incrementbusiness_name varchar(150)...The other fields for the bussinessTABLE catagorycatagory_id int(50) primary, auto_incrementcatagory_name varchar(150)TABLE buss_to_cat (Just means Business TO Catagorybuss_to_cat_id int(50) primary, auto_incrementbusiness_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 bebuss_to_cat_id | bussiness_id | catagory_id1 | 1 | 12 | 1 | 2I hope this made some sence, not sure how you are with mySQL Quote Link to comment Share on other sites More sharing options...
dc_jt Posted November 8, 2006 Author Share Posted November 8, 2006 HiThat 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 Quote Link to comment Share on other sites More sharing options...
pnj Posted November 8, 2006 Share Posted November 8, 2006 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 Quote Link to comment Share on other sites More sharing options...
dc_jt Posted November 8, 2006 Author Share Posted November 8, 2006 I dont really understand the above post sorryI have a form with checkboxes for each category likebars [] (<--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 Quote Link to comment Share on other sites More sharing options...
Anidazen Posted November 8, 2006 Share Posted November 8, 2006 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'" Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.