Re: relational table help

"Nicholas W. Miller" wrote: > > Hello, > > I'm using PHP with MySQL on UNIX box running Apache and I need some help > relating two tables... > > I have a table called Contact which holds records with standard contact > info (ContactID [unique], Name, Add1, Add2, State, Country, Zip, URL). > > Each of these contacts falls into one or more Categories ... > > I have a Categories table (CategoryID [unique], CategoryName) ... > > And I have a CategoryContact (CategoryID, ContactID) table where I keep > track of which Contacts fall into which Catgeories ... so if for example > a Contact with a ContactID=7 qualified for Category X (which has a > Category ID=1) and also qualified for Category Y (which has a > CategoryID=2), than there would be to rows in CatgeoryContact ... 7,1 > and 7,2. > > How do I display all the Contacts that qualify for a particular > Category? > > Please help! > > Thanks, > > Nick > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net Shi Chuanping wrote: > > Hi, > > I'm not sure whether I have understand your question. But I think you > can set a large enough field in "Contact" talbe, and call it "cat". Put > all the category-ids of a contact into this field, and seperate them by > comma. In your query string, you use > "select * from contact where cat like \"%<the id>%\"". > > Hope this can help. > > Alex > Interesting idea, but maintenance will drive absolutely insane. Here's the problem: Contact A gets married, moves, or anything else that changes the combined set of name, address, blah-blah. You end up loosing all of the previously linked Contact data for that person and you now have a new user and all the data entry that comes with it. It sucks, no offense to you, but if you try this approach for a month you will quickly be spending weekends to change it. On the other hand, I think the answer is something like this: select * from tblcontacts join tblcontractcontacts on ID=ID where ID=7 or something like that. I botched your syntax because I didn't want to dig through all the emails trying to figure out the exact fields you wanted to post. But this is the general 'hint' on where you want to go with it.