Simon Kimber pressed the little lettered thingies in this order...
>
> Christopher Wrote...
> > You need to be able to tie at least one field from each table to
> > one other
> > field in another table, and then you can have a query like:
> >
> > SELECT videos.*, links.*, actors.*
> > FROM videos, links, actors
> > WHERE videos.VideoID = '$VideoID' AND
> > links.VideoID = videos.VideoID
> > actors.ActorID = links.ActorID
> >
>
> Thanks Christopher,
>
> but wouldn't this return a separate row for each actor? I'm pretty sure i
> need something more than a simple join :o(
>
> Here's my original question again: "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"
>
>
Hello,
I had your thought backwards in my mind. I haven't ever seen a query
that needed a sub-select that couldn't be written using joins. It's true
that in many cases sub-selects would be much simpler, but it's usually
possible to rewrite sub-select queries using joins. You can do this
query with a simple join, but need to start with the table that will give
multiple results first and load those results into an array.
For example:
$result=mysql("DBName","SELECT actors.*, links.*, videos.*
WHERE actors.VideoID = '$VideoID' AND
links.VideoID = actors.VideoID AND
videos.VideoID = actors.VideoID
ORDER BY whatever");
$i=0;
while ($row = mysql_fetch_row($result)) {
$actorsField1[$i] = $row[0];
$actorsField1[$i] = $row[1];
...
$linksField1 = $row[10];
$linksField1 = $row[11];
...
$videosField1 = $row[20];
$videosField1 = $row[21];
...
$i++;
}
If the results of the links and videos tables are going to be the same for
each query, then there's no need to save those values in an array. If
there are going to be multiple results for any other table, you will also
want those to be saved as an array. What you are attempting could
also be done a little differently by using a LEFT JOIN, but I think that the
above statement will do what you want (If I am in fact understanding
what you want). You may also want to change the above SELECT to
have "links.ActorID = actors.ActorID" if your links table is tied to the
actors and not the videos. You can then show your results like:
echo "$videosField1<br>
$videosField2<p>
$linksField1<br>
$linksField2<p>";
for ($i = 0; $actorsField1[$i]; $i++) {
echo "$actorsField1[$i]<br>
$actorsField2[$i]<p>";
}
Or however you want it to be formatted. The end result is that you have
a single SQL query.
Have fun...
Christopher Ostmo
a.k.a. [EMAIL PROTECTED]
AppIdeas.com
Meeting cutting edge dynamic
web site needs
For a good time,
http://www.AppIdeas.com/
