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
