SELECT video.id, video.title, video.description, actor.name
FROM ( video INNER JOIN link ON link.videoID = video.id )
JOIN actor ON actor.id = link.actorID
WHERE video.title LIKE $searchstr
What you'll get is one query returning a bunch of rows, one for each actor.
Then concatenate the actors using PHP for your final output.
if ($res = mysql_query( $dbase, $query )) {
$id = 0;
$title = "";
$description = "";
$actors = "";
while ($row = mysql_fetch_array($res)) {
if ($id == 0) { // first result
$id = $row["id"];
$title = $row["title"];
$description = $row["description"];
}
else { // consecutive results
$actors .= ", ";
}
$actors .= $row["name"];
}
echo "Movie: $title [$description] Starring: $actors.";
}
else {
// query failed... what now, Einstein?
}
""Simon Kimber"" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Hi all,
>
> I have three tables in my (mysql) database:
>
> videos - ID, title, description, etc..
> links - ID, videoID, actorID
> actors - ID, name, dateofbirth, gender, etc...
>
> what i need to do is return a particular video and all it's staring actors
> with just one query returning just one row, ie. with all the actor names
in
> one field... is this possible? or do i have to get the video data first
and
> then the actors separately?
