Basically, I create a new weblog for each new “table”, so one for players, clubs and tournaments, then wrote a plugin for each (but could have been all in one plugin) to pull the data out.
Lots of functions like this one, to list specific data in particular order:
function current_world_rankings()
{
global $DB, $TMPL;
$limit = $TMPL->fetch_param('limit');
$return_data='';
$sql = "SELECT field_id_21 as world_ranking,
title as player,
url_title as player_link,
field_id_23*1 as world_championship_points ,
if(field_id_25='UK','',concat('(',upper(left(field_id_25,3)),')')) as nationality
FROM exp_weblog_data,exp_weblog_titles
WHERE exp_weblog_data.weblog_id=4 AND
exp_weblog_data.entry_id=exp_weblog_titles.entry_id AND
field_id_21>0
ORDER BY (field_id_21*1)
";
if ($limit>0)
$sql.='LIMIT '.$limit;
$query = $DB->query($sql);
unset($sql);
if ($query->num_rows == 0)
{
return false;
}
$tagdata = $TMPL->tagdata;
$rows=$query->result;
foreach ($rows as $row) {
$t=$tagdata;
foreach ($row as $key=>$value) {
$t =str_replace(
'{'.$key.'}',
$value,
$t
);
} //foreach
$return_data.=$t;
} //while
return $return_data;
} //current_world_rankings
then a template that includes this:
{exp:players:current_world_rankings limit="8" }
<tr bgcolor="#F0F0F0">
<td align="center">{world_ranking}</td>
<td align="center"><a href="{path="realtennis/player_details/{player_link}"}">{player}
{nationality}</a></td>
<td align="center">{world_championship_points}</td>
</tr>
{/exp:players:current_world_rankings}
I fudged the relationship bit by putting a list of the data that doesn’t change much (the clubs) into a custom field for the players, then coverted the club name to a url where I needed it. This has not worked very well and I should really have done a custom entry template and written a plugin to deliver an up-to-date drop-down. If I did it again I think I would store both the name and the entry_id of the related entry in the child entry.
Also used a general weblog for content. Wanted the users to be able to control entries but not have access to templates. So some entries are whole pages, other entries are used for odd bits, like last updated date, like this:
World Rankings last updated: {exp:weblog:entries weblog="realtennis" category="39" limit="1"}{summary}{/exp:weblog:entries}
Does that make sense?
Phoebe.
