RE: [PHP-DB] SQL problem is killing this newbie

The following will list movies seen by each member: SELECT membername,movietitle FROM member,movie WHERE member.memberid=movie.memberid This one lists all members who have seen these 3 movies. SELECT membername FROM member,movie WHERE member.memberid=movie.memberid && movietitle="Star Wars" && movietitle="Star Trek" && movietitle="Planet of the Apes" In practice, you would construct the movies' titles' "AND" clause in you PHP script -----Original Message----- From: Samios [mailto:psamios@primus.com.au] Sent: Monday, December 10, 2001 9:58 PM To: php-db@lists.php.net Subject: [PHP-DB] SQL problem is killing this newbie 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: 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