OK, I'm going to assume a database design like the following. It's rather simplified, but it'll get the idea across.
Actors ------ A_ID integer A_Fullname varchar Episodes -------- E_ID integer E_Title varchar Roles ----- R_ID integer R_Name varchar Actor_Role_Ep ------------- ARE_ID integer ARE_Actor_ID FK References Actor(A_ID) ARE_Episode_ID FK References Episode(E_ID) ARE_Role_ID FK References Role(R_ID)
Now, if you have some actors in multiple roles, or some roles played by multiple actors, your SQL statement will return multiple rows. So you should then loop through the results in a while loop. Example code below.
# I'm assuming a valid $dbh exists.
my $sql = $dbh->prepare_cached("
SELECT ARE_ID, A_Fullname, E_Title, R_Name
FROM Actor_Role_Ep, Actor, Episode, Role
WHERE ARE_Actor_ID = A_ID
AND ARE_Role_ID = R_ID
AND ARE_Episode_ID = E_ID
WHERE (whatever you want here);");
$sql->execute();
# If you need to pass in information above, then put the variables inside the ()'s.
while (($are_id, $a_name, $e_title, $r_name) = $sql->fetchrow_array) {
print "$a_name played $r_name in episode $e_title.\n";
}
I hope that helps you..
In reply to Re: SQL query: are all results in one hashref key the same?
by Yendor
in thread SQL query: are all results in one hashref key the same?
by Cody Pendant
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |