I am having trouble coming up with a query for the database I designed.
In its simplest form I have three tables
Characters table | character data
voiceactors table | voice actor data
char_voice table | character ID and corresponding voiceactor ID
My problem is that seperately I can query all of the character information, then query again to get the corresponding voice actors for the character(there is often more than one) However, I can't figure out how to select the character information and the voice actors for that character in one query.
My Query to get the voice actor information for say character 1
SELECT v.voicename, v.voiceid
FROM char_voice cv, voice v
WHERE cv.charid = 1
AND cv.voiceid = v.voiceid
GROUP BY v.voiceid
my attempt to gather both the character information and voice actor information in one query. however, i only get the information for the first voice actor it pulls up and no others for the given character
SELECT c.*, v.voiceid, v.voicename
FROM char_voice cv, characters c, voice v
WHERE c.id = '1'
AND c.id = cv.charid
AND cv.voiceid = v.voiceid
GROUP BY c.id
Also, lets say I was to expand this to display information about which movies a given character was in and i had another table char_movies setup the same way, how hard would adding this additional information to the query be? I have heard about JOIN, but don't quite understand how it works, would that be something I should look into to make these querys. which once I figure them out can span many tables, more effiecient?
Also, the way I set this up is so that it is contained within a function which I can pass it the character ID, and I want to return the data from the query. Would I have to store this information in an array to return it?
What I am doing right now
/*$conn is the connection to db and $query is what was in question above */
$results = @$conn -> query($query);
/*create an array of the results to return*/
$output = array();
while (($row = @$results->fetch_assoc()) != NULL)
{
$output[] = $row;
}
return $output;
I'll have to figure out how to reference the various values in the array later.
This is my first time programming anything of my own design, so please excuse my ignorance and thanks in advance for your assitance.
