SQL search problem

[micmac]

Hi all,
I have the following search code:

<?
$sql = "SELECT * FROM movie,movie_actor,actor,movie_director,director WHERE movie.movie_id=movie_actor.movie_id AND movie_actor.actor_id=actor.actor_id AND movie.movie_id=movie_director.movie_id AND movie_director.director_id=director.director_id ";
$sql = $sql."AND (movie.movie_title = '$stext' OR movie.movie_pitch = '$stext' OR movie.movie_pitch_short = '$stext' OR actor.actor_name = '$stext' OR director.director_name = '$stext') ";
$sql = $sql."GROUP BY movie.movie_id ORDER BY movie.movie_title";
}
$result = mysql_query($sql);
$resultsnumber = mysql_num_rows($result);
?>	

At the moment if i do a search for the movie name "The Tuxedo" it brings up no results. This is because i have no actor and director associated to this movie. If i add an actor and director to the respective tables in the DB the search result displays the movie details correctly.
What will i need to change in my SQL query so that it searches the DB irrespective to whether there is an actor or director associated to the movie and show the results?

Cheers,
micmac

[dalecosp]

Less AND, more OR? Particularly here:

$sql = $sql."AND (movie.movie_title =

???

[[deleted]]

Give this a shot. It's untested but it's probably close to what you need.

$sql = "SELECT * FROM movie, movie_actor, actor, movie_director, director"
	. "WHERE (movie.movie_id = movie_actor.movie_id OR movie.movie_id = '')"
	. "AND (movie_actor.actor_id = actor.actor_id OR movie_actor.actor_id = '')"
	. "AND (movie.movie_id = movie_director.movie_id OR movie.movie_id = '')"
	. "AND (movie_director.director_id = director.director_id OR movie_director.director_id = '')"
	. "AND (movie.movie_title LIKE '%$stext%' OR movie.movie_pitch LIKE '%$stext%' OR movie.movie_pitch_short LIKE '%$stext%' OR actor.actor_name LIKE '%$stext%' OR director.director_name LIKE '%$stext%')"
	. "GROUP BY movie.movie_id"
	. "ORDER BY movie.movie_title";