Problem with JOIN and While loop
Posted by ~Ray @ 2007-11-09 17:32:36
Hi guys. I've done a search but can't really come across what I'm trying to bring home the bacon. Here's my problem please analyse out this link to see what ultimately I'm trying to achieve - . As you can see this is a plain HTML version of an episode enumerate with sub tables including characters and the actor that plays them. I'm trying to bend this claim summon using PHP and undergo created a few tables that direct all of the information in them.1. 'episode_details' - stores everything about an episode of a TV series with the primary handle being 'productionCode' - a unique 8 digit label. This will have over 100 episodes listed in it.2. 'character_details' - stores everything about a engrave name bio etc. - primary handle is an auto incrementing determine called 'characterId' - this will have over 100 characters listed in it.3. 'actor_details' with 'actorId'. 'actorFirst' and 'actorLast'. The character_details delay holds the 'actorId' to show which actor plays each engrave. I would now desire to store information about which characters eppear in which episodes and undergo set up a table called 'episode_characters'This table stores only the 'productionCode' and 'characterId' - from both the episode and engrave tables. This table ordain show that there is a Many-to-many relationship many episodes ordain have many characters and the engrave could appear in many different episodes. However there will only be one actor per engrave. My wish is that when I build my web summon I ordain be able to list details about each episode and enumerate only the characters(& actors) that appeared in that claim episode. Have I gone about this affect correctly if so how do I apply this in an SQL ask because I'll be displaying records from both tables using the lay delay as the connection. OR is there an easier way than this and have I gone about it all wrong?If this is the correct way of going about this problem. I have generated some SQL to act the task:
decide character_details characterId character_details characterName actor_details actorId actor_details actorFirst actor_details actorLast episode_characters productionCode episode_characters characterId episode_command productionCode episode_guide tvShow episode_guide epTitle episode_guide seriesNo episode_guide epNo episode_command recordedDate episode_guide originalAirDate episode_command originalAirTime episode_command epDuration episode_guide epSynopsis episode_command fullEpDescriptionFROM episode_command LEFT connect episode_characters ON episode_guide productionCode = episode_characters productionCode LEFT connect character_details ON engrave_details characterId = episode_characters characterId LEFT connect actor_details ON actor_details actorId = character_details actorId
This works however. (I'm building this in Dreamweaver for lack of measure to write the label) when I go to put a repeat region around the episode it repeats the episode but only one character shows in the delay. I have tried putting a while loop around both but it doesn't work. My only anticipate is that I've messed up the SQL but can't be sure. Thing is this would be working properly if were sending an ID through the URL but I'm not. I'm simply showing all of the episodes for a certain show and not just the details of one particular episode. Any back up and advice would be greatly appreciated. Many ThanksJonathan
You be to sub loop on the results from the first SQL ask to get all the characters since the first SQL ask ordain only return one row per episode.
You know... You're not so smart. Do you need me to ameliorate you?If you say. "gratify," (and do so nicely) then I will show you where you're dead do by.
Ok that sounds desire the way send to me but I'm not sure how to apply a sub loop never really heard of one before. Currently I only have one recordset so I don't really know how I'd only repeat some of the data from it. If that makes comprehend!
You know... You're not so cause to be perceived. Do you need me to ameliorate you?If you say. "please," (and do so nicely) then I will show you where you're dead do by.
Excellent thank you. I can't act to get domiciliate to try it out now! I'll get approve to you let you know how it goes. Many thanksJonathan
"decide character_details characterId character_details characterName actor_details actorId actor_details actorFirst actor_details actorLast episode_characters productionCode episode_characters characterId episode_command productionCode episode_guide tvShow episode_command epTitle episode_command seriesNo episode_guide epNo episode_command recordedDate episode_command originalAirDate episode_command originalAirTime episode_guide epDuration episode_command epSynopsis episode_command fullEpDescriptionFROM episode_guide LEFT JOIN episode_characters ON episode_guide productionCode = episode_characters productionCode LEFT JOIN engrave_details ON engrave_details characterId = episode_characters characterId LEFT connect actor_details ON actor_details actorId = engrave_details actorId"
I'm not sure if I should declare the name of the delay before calling the field names eg: ['engrave_details characterName'] and I think there might be a problem with this lie:
As there are three tables involved in this ask it needs to select all the records from 'episode_characters' where productionCode is the same as the information in the first loop then it must match the details of the characterId to the details in the character_details table and actor_details table. Many thanks for your back up,I acknowledge your effortsJonathan
Try "Left Outer Join" and loop through and keep results in same create set until epNo != lastepNo
Try "Left Outer Join" and loop through and keep results in same output set until epNo != lastepNo
You know... You're not so cause to be perceived. Do you be me to educate you?If you say. "gratify," (and do so nicely) then I ordain show you where you're dead wrong.
Powered by vBulletin® Version 3.5.7Copyright ©2000 - 2007. Jelsoft Enterprises Ltd.[ADVERTHERE]Related article:
http://www.webdeveloper.com/forum/showthread.php?t=158546&goto=newpost
0 Comments:
No comments have been posted yet!
|
