I can't get my select query correct. I have 4 tables and 2 foreign keys. Here are my table structures.
movies movieid int unsigned not null auto_increment primary key moviename varchar directid int unsigned # foreign key to directors table date adddate genres genreid int unsigned not null auto_increment primary key genre varchar movieid int unsigned # foreign key to movies table ratings ratingid int unsigned not null auto_increment primary key rating float(2,1) movieid int unsigned # foreign key to movies table directors directorid int not null auto_increment directname varchar
Here is my query:
$query = "select movies.moviename, ratings.rating, genres.genre, directors.directname from movies, ratings, genres, directors where movies.ratingid = ratings.ratingid and movies.movieid = genres.movieid and directors.directid = movies.directid order by movies.moviename";
Can anyone help spot my mistake? I can manage to get them to show correctly without the director, but anytime I try selecting director I get problems. I have these displayed in a table like this.
Movie Name Director Name Rating Genre Cat in the Hat Ebert 0.0 family Dark Knight Nolan 5.0 action Die Hard Man 0.0 action