Page 2 of 2

Posted: Fri Aug 24, 2007 12:49 pm
by jonthomas83
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.

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>"
    }
}
?>
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:

Code: Select all

$characters = mysql_query("SELECT * FROM episode_characters WHERE productionCode = '".(int)$episode["episode_guide.productionCode"]."'");
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