Posted: Fri Aug 24, 2007 12:49 pm
Ok I've tried looking but not really sure where you're pointing to.
I have this worked out so far but it's very very wrong! lol.
I'm not sure if I should declare the name of the table before calling the field names eg: ['character_details.characterName'].
and I think there might be a problem with this line:
As there are three tables involved in this query, 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 help,
I appreciate your efforts
Jonathan
I have this worked out so far but it's very very wrong! lol.
Code: Select all
<?php
echo "<h1>"$episodes['showFullTitle'];"</h1>"
$episodes = mysql_query("
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");
while($episode = mysql_fetch_assoc($episodes))
{
echo "<h2>"$episodes['productionCode'];" "$episodes['seriesNo'];" "$episodes['epNo'];" "$episodes['epTitle'];"</h2>"
echo "<h4>Original Air Date: "$episodes['originalAirDate'];" "$episodes['originalAirTime'];" - "$episodes['epDuration'];"<br />Recorded On: "$episodes['recordedDate'];"</h4>"
echo "<h4>Official Episode Synopsis</h4>"
echo "<p>"$episodes['epSynopsis'];"</p>"
$episodes['fullEpDescription'];
$characters = mysql_query("SELECT * FROM episode_characters WHERE productionCode = '".(int)$episode["episode_guide.productionCode"]."'");
while($character = mysql_fetch_assoc($characters))
{
echo "<table width="431" border="1" cellpadding="3">"
echo "<tr>"
echo "<td>Character</td>"
echo "<td>Actor</td>"
echo "</tr>"
echo "<tr>"
echo "<td>"$character['characterName'];"</td>"
echo "<td>"$character['actorFirst'];" "$character['actorLast'];"</td>"
echo "</tr>"
echo "</table>"
}
}
?>and I think there might be a problem with this line:
Code: Select all
$characters = mysql_query("SELECT * FROM episode_characters WHERE productionCode = '".(int)$episode["episode_guide.productionCode"]."'");Many thanks for your help,
I appreciate your efforts
Jonathan