Problem with JOIN and While loop!

[jonthomas83]

Hi guys, I've done a search but can't really come across what I'm trying to achieve.

 

Here's my problem, please check out this link to see what ultimately I'm trying to achieve - www.satellitecitytv.net/sc_episodes.html.

 

As you can see this is a plain HTML version of an episode list with sub tables including characters and the actor that plays them.

 

I'm trying to replicate this exact page using PHP and have created a few tables that hold all of the information in them.

 

1. 'episode_details' - stores everything about an episode of a TV series with the primary field being 'productionCode' - a unique 8 digit code. This will have over 100 episodes listed in it.

 

2. 'character_details' - stores everything about a character name, bio etc. - primary field is an auto incrementing value called 'characterId' - this will have over 100 characters listed in it.

 

3. 'actor_details' with 'actorId', 'actorFirst' and 'actorLast'. The character_details table holds the 'actorId' to show which actor plays each character.

 

I would now like to store information about which characters eppear in which episodes and have set up a table called 'episode_characters'

This table stores only the 'productionCode' and 'characterId' - from both the episode and character tables.

 

This table will show that there is a Many-to-many relationship, many episodes will have many characters and the character could appear in many different episodes. However, there will only be one actor per character.

 

My hope is that when I build my web page I will be able to list details about each episode and list only the characters(& actors) that appeared in that exact episode.

 

Have I gone about this process correctly, if so how do I implement this in an SQL query because I'll be displaying records from both tables using the middle table 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 perform the task:

 

SELECT character_details.characterId         
, character_details.characterName         
, actor_details.actorId         
, actor_details.actorFirst         
, actor_details.actorLast         
, episode_characters.productionCode         
, episode_characters.characterId       
, episode_guide.productionCode       
, episode_guide.tvShow       
, episode_guide.epTitle       
, episode_guide.seriesNo       
, episode_guide.epNo       
, episode_guide.recordedDate       
, episode_guide.originalAirDate       
, episode_guide.originalAirTime       
, episode_guide.epDuration       
, episode_guide.epSynopsis       
, episode_guide.fullEpDescription
FROM episode_guide 
LEFT JOIN episode_characters       
ON episode_guide.productionCode = episode_characters.productionCode 
LEFT JOIN character_details       
ON character_details.characterId = episode_characters.characterId   
LEFT JOIN actor_details       
ON actor_details.actorId = character_details.actorId

 

This works, however, (I'm building this in Dreamweaver, for lack of time to write the code) when I go to put a repeat region around the episode it repeats the episode but only one character shows in the table. I have tried putting a while loop around both but it doesn't work. My only guess 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 help and advice would be greatly appreciated.

 

Many Thanks

Jonathan

[jonthomas83]

*Bump*

 

Is there any way I can explain this, so that it's easier for someone to help me? I know there's a lot of info there but I just wanted to be thorough and cover all bases for you guys like.

 

If anyone can help me out it would be greatly appreciated.

 

Many Thanks

Jonathan

[jonthomas83]

*bump*