Need help merging tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Need help merging tables

Post by jonthomas83 »

Hi guys,

Don't know if this is the right place to be posting, if not, please move it and let me know where it's gone! lol!

I was wondering if you can help me with a little problem.

I have two tables

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.

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.

My hope is that when I build my web page I will be able to show details about the episodes and list only the characters 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?

please help!

Many thanks, any advice appreciated,
Jonathan
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You're setting up what's called a many-to-many relationship. Research JOINs, particularly INNER JOINs.
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post by jonthomas83 »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I just realised that I needed to add another table into the equation '[b]actor_details[/b]' with '[i]actorId[/i]', '[i]actorFirst[/i]' and '[i]actorLast[/i]'. The character_details table holds the '[i]actorId[/i]' to show which actor plays each character.

I've been reading about JOIN's and INNER JOIN's all day long and I don't get the 'ON' bit, I've not got access to a server at the moment to try it out.

The only examples I've found of JOINing three tables or more have the foreign ID in the other table at some point, my structure has a table which holds the primary fields of each in order to do the link - '[i]productionCode[/i]' and '[i]characterId[/i]'. Something's telling me that I don't need a primary key in this table, tell me if I'm wrong.

[syntax="sql"]
SELECT episode_details.productionCode
     , episode_details.episodeTitle
     , episode_details.episodeSynopsis  

(NOTE: + there's loads more fields from the episode_details table I can't remember off the top of my head)   
     
     , character_details.characterId
     , character_details.characterName
     , actor_details.actorId
     , actor_details.actorFirst
     , actor_details.actorLast
     , episode_characters.productionCode
     , episode_characters.characterId
  FROM episode_characters
INNER
  JOIN episode_details
    ON episode_details.productionCode = episode_characters.productionCode 
INNER
  JOIN character_details
    ON character_details.characterId = episode_characters.characterId
INNER
  JOIN actor_details
    ON actor_details.actorId = character_details.actorId
Am I on the right lines? I can't really tell if it works until I get to a computer but can someone explain the what the 'ON' function is doing? and if I'm doing ok with this statement?

Cheers,
Jonathan


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

[color=green]Apologies, I wasn't too sure. Newbies huh! (they're a total pain in the rear!). Sorry! : )[/color]
Last edited by jonthomas83 on Mon Aug 13, 2007 11:58 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your query looks good from the quick read I did of it.
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post by jonthomas83 »

ok cheers for that, I'll take a look when I get back home and get back to you if I get any problems! (no doubt I will!)
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post by jonthomas83 »

Code: Select all

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_guide
      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
I'm getting an error saying

MySQL error# 1066
Not unique table/alias: 'episode_guide'

Perhaps I should mention that I'm building my site in Dreamweaver as I thought it would be quicker to build rather than type code from scratch
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You have two table references to "episode_guide."
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post by jonthomas83 »

All sorted thank you, the only problem is, I would like to list all of the episode Details one after the other using a repeat region, however, it won't let me do a repeat region on the table that lists all the characters for each episode, which is a seperate table within the table that holds the episode details.

it says: "This server behaviour does not support nesting, make another selection that does not already contain a server behaviour"

e.g.

ProductionCode: Ep Title

Ep Synopsis and description

Character Name | Actor Name Table
----------------------------------------------
1. Jon | Giles Amber
2. Andy |James Blurb
3. etc.
-------------------------------------------------------

ProductionCode: Ep Title

Ep Synopsis and description

Character Name | Actor Name Table
----------------------------------------------
1. Jon | Giles Amber
2. Gemma | Barbara Windsor! lol
3. etc.
-------------------------------------------------------
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What query are you attempting to use?

Have you looked at the first two threads linked from Useful Posts (sticky in PHP - Code?)
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post by jonthomas83 »

I've taken a look at some of the stuff in there, managed to find a few ansers to other things I had to sort out but I couldn't find a way of solving this little issue.

The query as it stands at the moment is:

Code: Select all

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
It's all working but I have no way of getting more than one character in a list when the episodes are repeated one after another, and vice versa if i have only one episode and all the characters repeating.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

jonthomas83 wrote:It's all working but I have no way of getting more than one character in a list when the episodes are repeated one after another, and vice versa if i have only one episode and all the characters repeating.
I'm not sure I understand. Please explain further.
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post by jonthomas83 »

http://www.satellitecitytv.net/sc_episo ... 1_episode1

If you check this link out, it shows exactly what I'm trying to achieve, I've put all that information into a table and the sub table on each episode (the pink table) holds information on the characters which is held in one table and the actor which is in another table.

When I do a repeat region around an episode it repeats all the information, however, when I try to do a further repeat region around the character table it fails to work. It's because both sets of information are held in the same recordset.

I hope that makes a little more sense.

Many Thanks
Jonathan
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What do you define as a repeat region? Are you using different queries for each?
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post by jonthomas83 »

This is the code I'm using for repeating the episodes until the EoF

Code: Select all

<?php do { ?>
  <h2><?php echo $row_MyRS['productionCode']; ?> <?php echo $row_MyRS['seriesNo']; ?>/<?php echo $row_MyRS['epNo']; ?>: <?php echo $row_MyRS['epTitle']; ?></h2>
  <h4>Original Air Date: <?php echo $row_MyRS['originalAirDate']; ?> (<?php echo $row_MyRS['originalAirTime']; ?>) - <?php echo $row_MyRS['epDuration']; ?><br />
    Recorded On: <?php echo $row_MyRS['recordedDate']; ?></h4>
  <h4>Official Episode Synopsis</h4>
  <p><?php echo $row_MyRS['epSynopsis']; ?></p>
  <?php echo $row_MyRS['fullEpDescription']; ?> <br />
  <hr />
  <table width="431" border="1" cellpadding="3">
    <tr>
      <td>Character</td>
      <td>Actor</td>
    </tr>
    <tr>
      <td><?php echo $row_MyRS['characterName']; ?></td>
      <td><?php echo $row_MyRS['actorFirst']; ?> <?php echo $row_MyRS['actorLast']; ?></td>
    </tr>
	</table>
  <?php } while ($row_MyRS = mysql_fetch_assoc($MyRS)); ?>
However on the lines:

Code: Select all

<tr>
      <td><?php echo $row_MyRS['characterName']; ?></td>
      <td><?php echo $row_MyRS['actorFirst']; ?> <?php echo $row_MyRS['actorLast']; ?></td>
</tr>
I would also like to do a while loop but when I do, it's only giving me one episode and it's only showing me the characters, even that output is wrong too because it's repeated one of the characters twice.

The Recordest query I am using is the same on both because they are both part of the same recordset. This would be simple if I was passing some sort of Id through the URL but I'm not and I'd really like to list all of the episodes on one entire page.

Again, many thanks for your patience and help.
Jonathan
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It appears your code could be helped with the logic and code posted in the first two threads linked from Useful Posts. If you don't know where/what that is, it's a sticky thread in PHP - Code.
Post Reply