Three Tables - Maybe Unusual Combination?

[EternalSorrow]

I'm setting up three tables, each with their own use:

 

FILM table (used to store information on films):

id

title

year

info

image

 

PEOPLE table (used to store information on films):

id

name

occupation

bio

image

 

RELATED table (used to connect films to people):

title

name

 

So those are the layouts of the three tables.  I've connected the PEOPLE table to the FILM table, so that when someone clicks on a link of a person they can see a list of those films which relate to that person.  Like so:

 

<?php

if (!is_numeric($_GET["name"]) && !empty($_GET["name"]) && $_GET["name"]!="")
{
$name = $_GET["name"];
}

mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT * FROM related
INNER 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);

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

}
?>

 

Here's where it gets tricky: I want to also show those people who are also connected to the same films, so that when a visitor clicks on a person's link they will view not only related films but those people they are related to through those films.  Here's an example:

 

PEOPLE table holds these people:

Merle Oberon

Leslie Howard

 

FILM table holds this row:

The Scarlet Pimpernel

 

RELATED table holds these rows:

The Scarlet Pimpernel | Merle Oberon

The Scarlet Pimpernel | Leslie Howard

 

So here's what the visitor should see when they click a link to Merle Oberon's bio page:

 

Click Merle Oberon link -->  And view these related links:

 

The Scarlet Pimpernel (movie)

Leslie Howard (person)

 

I cannot wrap my mind around whether to create a new $query statement to show the connection between PEOPLE of like FILM, or to create some type of JOIN in the existing $query which will find the connection.  Any help and/or advice would be much appreciated.

[The Little Guy]

in the related table, I would use the Persons ID and the Film ID, instead of their name and film name.

 

It will make it easier to connect the two. Remember there are people with the same names.

 

So, if you did that you could do something like this:

 

Not tested

SELECT * FROM 
`film` f LEFT JOIN `related` r ON (f.id = r.film_id)
LEFT JOIN `people` p ON (p.id = r.person_id)
WHERE p.id = '10'

[EternalSorrow]

SELECT * FROM 
`film` f LEFT JOIN `related` r ON (f.id = r.film_id)
LEFT JOIN `people` p ON (p.id = r.person_id)
WHERE p.id = '10'

 

The problem with that code is it won't choose people from the PEOPLE table who are related to the film; it will only retrieve the person who's link was clicked.  Like so:

 

What appears:

 

Clicked Merle Oberon -->  What is viewed: The Scarlet Pimpernel | Merle Oberon

 

And what should appear:

 

Clicked Merle Oberon -->  What is viewed: The Scarlet Pimpernel | Merle Oberon | Leslie Howard

 

I've been playing around with the coding since I posted, and was able to create two separate queries which output the total result I want.  I've tried combining them, but when I do the PEOPLE table always duplicates the number of those people associated with the film and places the linked person's information in their places, like so:

 

Clicked Merle Oberon -->  What is viewed: The Scarlet Pimpernel | Merle Oberon | Merle Oberon

 

It should be like this:

 

Clicked Merle Oberon -->  What is viewed: The Scarlet Pimpernel | Merle Oberon | Leslie Howard

 

Does anyone have any ideas on how I can successfully join these two queries into one?

 

<?php

mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$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);

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

}
?>
<?php

mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

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

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

echo '<li><a href="biography.php?name='.$name.'"><img src="images/thumbnails/'.$img.'.jpg" alt="" title="'.$name.' ('.$occupation.')"></a></li>';

}
?>