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 $videosField2 $linksField1 $linksField2"; for ($i = 0; $actorsField1[$i]; $i++) { echo "$actorsField1[$i] $actorsField2[$i]"; } 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/
RE: [PHP] Multipe Tables, Single Query Problem
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" Ray Wrote... > Something like: > > SELECT name, title, description from videos, actors, links where > actors.ID = links.ID and videos.ID=videoID, and videos.ID = 20; ? > > But in just want one row? You could return all the names in one row (I > think) using MySQL (I assume?) string functions... I have a funny > feeling you need a sub select, which mysql doesnt support... > Ray, I have a funny feeling you could be right :o( Cheers Simon Kimber Funny.co.uk - The Comedy Portal http://www.funny.co.uk/ Now Incorporating: The UK Live Comedy Directory FREE promotion for UK Comedy Acts and Venues http://www.funny.co.uk/uklive/ eml. [EMAIL PROTECTED] icq. 16156911
Re: [PHP] Multipe Tables, Single Query Problem
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?
RE: [PHP] Multipe Tables, Single Query Problem
Something like: SELECT name, title, description from videos, actors, links where actors.ID = links.ID and videos.ID=videoID, and videos.ID = 20; ? But in just want one row? You could return all the names in one row (I think) using MySQL (I assume?) string functions... I have a funny feeling you need a sub select, which mysql doesnt support... Ray Hilton - [EMAIL PROTECTED] http://rayh.co.uk -Original Message- From: Simon Kimber [mailto:[EMAIL PROTECTED]] Sent: 06 June 2001 14:57 To: [EMAIL PROTECTED] Subject: [PHP] Multipe Tables, Single Query Problem 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? Simon Kimber Funny.co.uk - The Comedy Portal http://www.funny.co.uk/ Now Incorporating: The UK Live Comedy Directory FREE promotion for UK Comedy Acts and Venues http://www.funny.co.uk/uklive/ eml. [EMAIL PROTECTED] icq. 16156911
Re: [PHP] Multipe Tables, Single Query Problem
Simon Kimber pressed the little lettered thingies in this order... > 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... > 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 You should consider picking up the book called "MySQL." It covers this sort of thing rather well. Good luck... Christopher Ostmo a.k.a. [EMAIL PROTECTED] AppIdeas.com Meeting cutting edge dynamic web site needs For a good time, http://www.AppIdeas.com/
Re: [PHP] Multipe Tables, Single Query Problem
At 01:57 PM 6/06/2001, you wrote: >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? $count=0; $query = "select actors.*, videos.* from actors, videos where videos.ID = '$ID' and videos.ID=actors.actorID order by actors.name"; $result=mysql_query($query); while (mysql_fetch_array($result)) { if ($count<0) echo "Movie Title: $display[title] - $display[desc]"; echo "Actor: $display[name] - DOB: $display[dateofbirth] etc etc"; $count++; } This is how I do it anyway. Make an incrementing value, and the if ($count<0) line basically says to display the title once, and skip it for every other entry. Chances are the query would need tweaking but the principle is there to get data from all needed tables in 1 query, then set the loop to display the needed data from the 1 query. There is probebly a more efficient way of doing it, but thats how I do it anyway. Chris
[PHP] Multipe Tables, Single Query Problem
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? Simon Kimber Funny.co.uk - The Comedy Portal http://www.funny.co.uk/ Now Incorporating: The UK Live Comedy Directory FREE promotion for UK Comedy Acts and Venues http://www.funny.co.uk/uklive/ eml. [EMAIL PROTECTED] icq. 16156911