You need an associating table; members and movies are in a many-to-many relationship here. The associating table represents the act of viewing-a-movie, and describe who and what are involved
comme ca: "members" memberid, name 1 Fred 2 Jack 3 Jill "movies" movieid, title 1 Fear and loathing 2 Monkey Town 3 PHP: The film 4 PHP: The sequel "viewings" viewingid, memberid, movieid 1 2 2 2 1 4 3 1 1 4 3 4 this data indicates that Jack saw "monkey town", Fred saw "Fear&Loathing" and "PHP:The Sequel", and Jill saw only "PHP: The sequel" Samios wrote: > I am having a problem designing a query on a mysql 3.23 database. > > I have 2 tables - "member" and "movie". > The "member" table stores the people details, the "movie" table records the > movies they have seen (each record is one movie). > > MEMBER > memberid int not null auto_increment primary key, > membername varchar(50) not null, > etc... > > MOVIE > movieid int not null auto_increment primary key, > movietitle varchar(50) not null, > memberid int, > index (memberid) > etc... > > Obviously member to movie is a one-to-many relationship. > > I want to create a search page which will allow me to search for members who > have watched a specified set of movies. > i.e. I want to query the database for members who have seen "Star Trek" AND > "Star Wars" AND "Planet of the APES". > > I can create a query which searches for "Star Trek" OR "Star Wars" OR > "Planet of the Apes". > Unfortunately the "AND" condition is causing me problems. > > I'm also hoping to use the "LIKE" operator in this query. > e.g. where movietitle like "%star%". > This will give me added flexiblity down the track. > > Any help would be greatly appreciated. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
