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:

memberid, name
1 Fred
2 Jack
3 Jill

movieid, title
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"

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).
> memberid int not null auto_increment primary key,
> membername varchar(50) not null,
> etc...
> 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 (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to