Re: Multipe Tables, Single Query Problem
| From: | Hugh Bothwell | Date: | Tue, 19 Jun 2001 20:24:00 +0000 |
| Subject: | Re: Multipe Tables, Single Query Problem | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-54419@lists.php.net to get a copy of this message | ||
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"" <simon@funny.co.uk> wrote in message
news:NEBBLHHDOLBKMGLEBCKFGEPICOAA.simon@funny.co.uk...
> 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?