Joining Three Tables - final relation troubles

[EternalSorrow]

I have three tables, two information tables and a relational table, as set up below:

 

People

ID

Name

 

Film

ID

Title

 

Related

Title

Name

 

I have them joined as such:

$query1 = "SELECT * FROM related r, people p, film f WHERE r.name = p.name AND r.title = f.title AND p.name != '{$name}' ";

 

Now here's the problem.  The $query is located on a person's page, and I want the results to show those other people and films related to them.  Because there is no specific film or person for the $query, I can't simply do what was explained in this post.  Here's an example of what I mean:

 

clicked Raymond Massey's link -->

 

viewing page of Raymond Massey:

The Scarlet Pimpernel | Leslie Howard | Merle Oberon

 

With the current $query it is returning all people in the databases, regardless of any relation, or lack thereof, to the person.

 

Is it possible to create some WHERE statement which could filter the wanted results or related films and people to the specified person's page, or would it be better for me to create a subquery?

 

Here's the full code (be aware this is for a content slider, and the connection and GET code for the person's name are not included):

<?php

$query = "SELECT * FROM related LEFT JOIN film ON related.title = film.title WHERE related.name = '$name' ";
$result = mysql_query( $query ) or die(mysql_error());

$slides = array();

while ($row = mysql_fetch_array($result))
{
extract($row);

$slides[] = "{$row['title']} | images/thumbnails/{$row['image']} | {$row['year']}";
}

$subjects='';

foreach($slides as $v)
{
$data = preg_split('/\s*\|\s*/',$v);

$subjects.='<li><a href="filmography.php?title='.$data[0].'"><img src="'.$data[1].'.jpg" alt="" title="'.$data[0].' ('.$data[2].')"></a></li>';

}

$query1 = "SELECT * FROM related r, people p, film f WHERE r.name = p.name AND r.title = f.title AND p.name != '{$name}' ";
$result1 = mysql_query( $query1 ) or die(mysql_error());

$slide = array();

while ($row2 = mysql_fetch_array($result1))
{
$slide[] = "{$row2['name']} | images/thumbnails/{$row2['img']} | {$row2['occupation']}";
}

$subject='';

foreach($slide as $v)
{
$data = preg_split('/\s*\|\s*/',$v);

$subject.='<li><a href="biography.php?name='.$data[0].'"><img src="'.$data[1].'.jpg" alt="" title="'.$data[0].' ('.$data[2].')"></a></li>';

}

[EternalSorrow]

Bump.  Since this may involve my search engine, I'll persevere and continue to bother.

[EternalSorrow]

I decided to try and see if I could approach this from a new angle, and may have come to a simple (not quite) solution.  I've been able to retrieve all films which are related to the $name, and through a sub query get all cast and exclude the person who's page the visitor is currently browsing.

 

Here is the working example of the script:

<?php

$query = "SELECT * FROM related LEFT JOIN film ON related.title = film.title WHERE related.name = '$name' ";
$result = mysql_query( $query ) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
extract($row);

$select_cast = mysql_query("SELECT * FROM people LEFT JOIN related ON people.name = related.name WHERE related.title = '$title' AND people.name != '{$name}' ") or die (mysql_error());

$cast = "";
while ($row2 = mysql_fetch_array($select_cast)) {
   $cast .= "<li><a href=\"biography.php?name={$row2[name]}\"><img src=\"images/thumbnails/{$row2[img]}.jpg\"></a></li>";
}

echo '<li><a href="filmography.php?title='.$title.'"><img src="images/thumbnails/'.$image.'.jpg"></a></li>
'.$cast.'';

}
?>

 

The problem I'm having is I don't know how to implement the above script into the content slider, which is what I'm using to show the related items.  The problem looks like this:

 

PEOPLE

Leslie Howard

Merle Oberon

Raymond Massey

Cary Grant

 

FILM

The Scarlet Pimpernel

Arsenic And Old Lace

 

RELATED

The Scarlet Pimpernel | Leslie Howard

The Scarlet Pimpernel | Merle Oberon

The Scarlet Pimpernel | Raymond Massey

Arsenic And Old Lace | Cary Grant

Arsenic And Old Lace | Raymond Massey

 

click on $name: Raymond Massey -->

 

output: Arsenic And Old Lace | Leslie Howard | Merle Oberon | The Scarlet Pimpernel | Leslie Howard | Merle Oberon

 

when the output should be: Arsenic And Old Lace | Cary Grant | The Scarlet Pimpernel | Leslie Howard | Merle Oberon

 

The sub query is obviously taking only the first $title and repeating those cast members beside every film, since when I change the ORDER BY in the $query the cast members change.  What I don't know is how to relate the $cast field to the film within the $subjects field.

 

Here's the modified code for reference:

<?php

$query = "SELECT * FROM related LEFT JOIN film ON related.title = film.title WHERE related.name = '$name' ";
$result = mysql_query( $query ) or die(mysql_error());

$slides = array();

while ($row = mysql_fetch_array($result))
{
extract($row);
$slides[] = "{$row['title']} | images/thumbnails/{$row['image']} | {$row['year']}";
}

$select_cast = mysql_query("SELECT * FROM people LEFT JOIN related ON people.name = related.name WHERE related.title = '$title' AND people.name != '{$name}' ") or die (mysql_error());

$cast = "";
while ($row2 = mysql_fetch_array($select_cast)) {
   $cast .= "<li><a href=\"biography.php?name={$row2[name]}\"><img src=\"images/thumbnails/{$row2[img]}.jpg\"></a></li>";
}

$subjects='';

foreach($slides as $v)
{
$data = preg_split('/\s*\|\s*/',$v);

$subjects.='<li><a href="filmography.php?title='.$data[0].'"><img src="'.$data[1].'.jpg" alt="" title="'.$data[0].' ('.$data[2].')"></a></li>
'.$cast.'';

}

?>