RE: [PHP] Multipe Tables, Single Query Problem

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. tech@AppIdeas.com AppIdeas.com Meeting cutting edge dynamic web site needs For a good time, http://www.AppIdeas.com/