Help with a query please

[Ugluth]

Hello there, i have a database for a video store where there's a table for movies, a movie_actor table and an actors table. The movie_actor table holds the id of a movie and the id of an actor. What i want to do is display the actors for each movie (can be more than 1). Here is what i have done up to now.

 

 $sql="SELECT m.movie_ID, m.name, c.category_name, mm.medium_name, rp.period, rp.price, m.total_stock, m.current_stock
                        FROM movies AS m
                        INNER JOIN categories AS c ON m.category = c.category_ID
                        INNER JOIN movie_medium AS mm ON m.medium = mm.medium_ID
                        INNER JOIN rental_period AS rp ON m.rental_time = rp.medium_ID"; //This is the main query, no probs with this one
$pager = new PS_Pagination($link, $sql, 10, 3); //pager is a script for pagination i'm using, not relevant to the problem i think.
$rs = $pager->paginate();
while ($row = mysql_fetch_array($rs, MYSQL_NUM)) {
?>
	<tr>
		<td><?php echo $row[1]; ?></td>
		<td><?php echo $row[2]; ?></td>
		<td>
<?php $result_actor = mysql_query("SELECT a.name, a.surname    FROM movie_actor AS ma WHERE ma.movie_ID = $row[0] INNER JOIN actors AS a ON ma.actor_ID = a.actor_ID") or die("Query failed: " . mysql_error());
		while ($row_actor = mysql_fetch_array($result_actor, MYSQL_NUM)) {
?>																	
		<?php echo $row_actor[0] . " " . $row_actor[1]; ?><br />
<?php } ?>
</td>
		<td><?php echo $row[3]; ?></td>
		<td><?php echo $row[4]; ?></td>
		<td><?php echo $row[5]; ?></td>
		<td><?php if(($row[6] - $row[7]) > 0) { echo "available"; } else { echo "all copies are rented"; } ?></td>
	</tr>
<?php } ?>

The error i'm getting is this one:

Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN actors AS a ON ma.actor_ID = a.actor_ID' at line 1

 

I'm pretty sure the fields are correct its not a matter of a typo. I'm using wamp server, mysql version 5.1.36 if someone could tell me whats wrong with my second query i would really be grateful.

 

P.S. If i remove the inner join part and just leave it with the where, the query works properly it returns the id's of the actors that are associated with that movie, so my problem is displaying the actor name and surname.

 

Thanks in advance and if u need any more info please just ask!

[MatthewJ]

Try moving the WHERE clause to the end of the query

 

SELECT a.name, a.surname FROM movie_actor AS ma INNER JOIN actors AS a ON ma.actor_ID = a.actor_ID WHERE ma.movie_ID = $row[0]

[Ugluth]

I can't believe i spent so much time trying to get whats wrong and it was that simple after all, worked great, thank you very much!

[Ugluth]

Hello again, on a different page i'm having trouble with the where clause. I'll post the code first and explain after

<?php	
$result_sql=mysql_query($sql="SELECT DISTINCT m.movie_ID, m.name, c.category_name, mm.medium_name, rp.period, rp.price, m.total_stock, m.current_stock
														FROM movie_actor AS ma
														INNER JOIN movies AS m ON ma.movie_ID = m.movie_ID
														INNER JOIN categories AS c ON m.category = c.category_ID
														INNER JOIN movie_medium AS mm ON m.medium = mm.medium_ID
														INNER JOIN rental_period AS rp ON m.rental_time = rp.medium_ID") or die("Query failed: " . mysql_error());
														if (isset($_POST['actor'])) {
						 			 $sql .= "WHERE ma.actor_ID = " . $actor;
				}
while ($row = mysql_fetch_array($result_sql, MYSQL_NUM)) {
?>
	<tr>
		<td><?php echo $row[1]; ?></td>
		<td><?php echo $row[2]; ?></td>
		<td>
<?php $result_actor = mysql_query("SELECT a.name, a.surname	FROM movie_actor AS ma INNER JOIN actors AS a ON ma.actor_ID = a.actor_ID WHERE ma.movie_ID = $row[0]") or die("Query failed: " . mysql_error());
		while ($row_actor = mysql_fetch_array($result_actor, MYSQL_NUM)) {
?>																	
		<?php echo $row_actor[0] . " " . $row_actor[1]; ?><br />
<?php } ?>
		</td>
		<td>
<?php $result_director = mysql_query("SELECT d.name, d.surname	FROM movie_director AS md INNER JOIN directors AS d ON md.director_ID = d.director_ID WHERE md.movie_ID = $row[0]") or die("Query failed: " . mysql_error());
		while ($row_director = mysql_fetch_array($result_director, MYSQL_NUM)) {
?>																	
		<?php echo $row_director[0] . " " . $row_director[1]; ?><br />
<?php } ?>
		</td>
		<td><?php echo $row[3]; ?></td>
		<td><?php echo $row[4]; ?></td>
		<td><?php echo $row[5]; ?></td>
		<td><?php if(($row[6] - $row[7]) > 0) { echo "available"; } else { echo "all copies are rented"; } ?></td>
	</tr>
<?php } 

 

I don't get any errors on this one, but its not fetching the results i want it to. I also have a drop down list that displays all actors and when it postback's it brings the id of the actor (checked it) and there are only supposed to be results with movies that the certain actor is in them. I have also tried selecting from movies table instead of movie actor, but it had the same effect, it didn't limit the results to the selected actor. The movies table has the movie_ID PK, the movie_actor has movie_ID as FK and actor_ID as FK and actors has actor_ID as PK. Please help me again, thanks in advance!