You need an associating table; members and movies are in a many-to-many
The associating table represents the act of viewing-a-movie, and describe who
and what are involved
1 Fear and loathing
2 Monkey Town
3 PHP: The film
4 PHP: The sequel
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"
> 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).
> memberid int not null auto_increment primary key,
> membername varchar(50) not null,
> movieid int not null auto_increment primary key,
> movietitle varchar(50) not null,
> memberid int,
> index (memberid)
> 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]