Need help merging tables
Moderator: General Moderators
-
jonthomas83
- Forum Newbie
- Posts: 15
- Joined: Sun Aug 12, 2007 6:21 pm
Need help merging tables
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
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
-
jonthomas83
- Forum Newbie
- Posts: 15
- Joined: Sun Aug 12, 2007 6:21 pm
feyd | Please use
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]
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
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.
-
jonthomas83
- Forum Newbie
- Posts: 15
- Joined: Sun Aug 12, 2007 6:21 pm
-
jonthomas83
- Forum Newbie
- Posts: 15
- Joined: Sun Aug 12, 2007 6:21 pm
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
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
-
jonthomas83
- Forum Newbie
- Posts: 15
- Joined: Sun Aug 12, 2007 6:21 pm
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.
-------------------------------------------------------
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.
-------------------------------------------------------
-
jonthomas83
- Forum Newbie
- Posts: 15
- Joined: Sun Aug 12, 2007 6:21 pm
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:
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.
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
-
jonthomas83
- Forum Newbie
- Posts: 15
- Joined: Sun Aug 12, 2007 6:21 pm
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
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
-
jonthomas83
- Forum Newbie
- Posts: 15
- Joined: Sun Aug 12, 2007 6:21 pm
This is the code I'm using for repeating the episodes until the EoF
However on the lines:
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
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)); ?>Code: Select all
<tr>
<td><?php echo $row_MyRS['characterName']; ?></td>
<td><?php echo $row_MyRS['actorFirst']; ?> <?php echo $row_MyRS['actorLast']; ?></td>
</tr>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