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

Was having issues with this yesterday and this morning. But I just got some help outside of the forums, just now figured it out. Disregard, thanks

Member Avatar for diafol

Mark it solved next time, so people won't waste their time checking the first post.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.