Step 1: Output the basic table structure
We will design a view-layout shows this kind of table:
The file structure:
metadata.xml:
<?xml version="1.0" encoding="utf-8"?>
<metadata>
<view title="主客對賽表">
<message><![CDATA[主客對賽表]]></message>
</view>
</metadata>
view.html.php:
<?php
// Check to ensure this file is included in Joomla!
defined( '_JEXEC' ) or die( 'Restricted access' );
jimport( 'joomla.application.component.view');
/**
* HTML View class for the Registration component
*
* @package Joomla
* @subpackage Registration
* @since 1.0
*/
class bleagueViewhomevsaway extends JView
{
function display($tpl = null)
{
$s_id = JRequest::getVar( 'sid', 0, '', 'int' );
$this->assignRef('sid', $s_id);
$gr_id = JRequest::getVar( 'gr_id', 0, '', 'int' );
$this->assignRef('gr_id', $gr_id);
$db = & JFactory::getDBO();
$query = "SELECT group_name FROM #__bl_groups WHERE id = ".$gr_id;
$db->setQuery($query);
$table_header = $db->loadResult();
$query = "SELECT COUNT( * ) FROM #__bl_grteams WHERE g_id = ".$gr_id;
$db->setQuery($query);
$total = $db->loadResult();
$query = "SELECT t.id, t.t_name FROM #__bl_teams AS t, #__bl_grteams AS gt WHERE t.id = gt.t_id AND gt.g_id =".$gr_id;
$db->setQuery($query);
$teams = $db->loadObjectList();
$this->assignRef('amt_of_team', $total);
$this->assignRef('table_header', $table_header);
$this->assignRef('teams', $teams);
parent::display($tpl);
}
}
tmpl/default.xml:
<?xml version="1.0" encoding="utf-8"?>
<metadata>
<layout title="默認佈局">
<message>
<![CDATA[主客對賽表默認佈局]]>
</message>
</layout>
<state>
<name>主客對賽表默認佈局</name>
<description>主客對賽表默認佈局</description>
<url addpath="/administrator/components/com_joomsport/elements">
<param name="sid" type="season" default="0" label="Select Season" description="Season" />
<param name="gr_id" type="season" default="0" label="Select Group" description="Group" />
</url>
<params>
</params>
</state>
</metadata>
tmpl/default.php:
<?php // no direct access
defined('_JEXEC') or die('Restricted access');
if(isset($this->message))
{
$this->display('message');
}
$Itemid = JRequest::getInt('Itemid');
?>
<table>
<caption>
</caption>
<thead>
<tr bgcolor='#FFE168'>
<th colspan=<?php echo '"' .($this->amt_of_team+1) .'"'?> align='center'>
<strong>主客對賽表</strong>
</th>
</tr>
<tr>
<th colspan=<?php echo '"' .$this->amt_of_team .'"'?> align="left">(<?php echo $this->table_header ?>)</th>
</tr>
</thead>
<tbody>
<tr>
<td><strong>作客\主場</strong></td>
<?php
$k = 0;
foreach ( $this->teams as $team )
{
$k++;
echo "<td>(".$k.")".$team->t_name."</td>";
}
?>
</tr>
<?php
$j = 0;
foreach ( $this->teams as $team )
{
$j++;
echo "<tr>";
echo "<td>(".$j.")".$team->t_name."</td>";
$inner = 0;
foreach ( $this->teams as $team )
{
$inner++;
if($inner == $j)
{
echo "<td bgcolor='#B0C4D7'></td>";
}
else
{
echo "<td></td>";
}
}
echo "</tr>";
}
?>
</tbody>
</table>
Select the menu type as this new created view:
![select-menu-type-hvsa](http://hi.csdn.net/attachment/201110/17/2190190_1318822438e272.png)
Specify the parameters, reuse the JElement 'Season' class in joomsport:
The outcome:
Step 2: Output the Match Scores inside Table:
There is no direct link between 'match' and 'season', but match will inherit season's id from 'match day', means that one specific 'match' must belong to one certain 'season':
So, without creating JOIN selecting query, we can't select all the matches within one 'season' with one sole query. Let's do it. First of all, let's select the 'match-day's within one season:
$query = "SELECT id FROM #__bl_matchday WHERE s_id = ".$s_id;
$db->setQuery($query);
$md_ids = $db->loadObjectList();
$md_ids will be an array of the returned rows. We need an array to store the selected 'matchday's id:
$matchDay_ids = array();
foreach($md_ids as $md_id)
{
$matchDay_ids[] = $md_id->id;
}
The next up is to design a query that select all the matches with 'm_id' equals to the id stored in $matchDay_ids[ ] . We can useIN clause:
SELECT * FROM 'tablename' WHERE 'fieldname' IN ('value1', 'value2', ...)
Implore array $matchDay_ids[ ] to a string for building this query.
$mdid_str = implode("','", $matchDay_ids);
Then go further, in addition to meet the condition of matching m_id, let's add two condition: to match the id of home team and away team, and put it inside a double loop:
$results = array();
for($i=0; $i<$total; $i++)
{
$results[$team_ids[$i]] = array();
for($j=0; $j<$total; $j++)
{
if($i == $j)
{
$results[$team_ids[$i]][$team_ids[$j]] = 'N/A';
}
else
{
$query = "SELECT CONCAT(score1,':',score2) AS score "
. "FROM #__bl_match "
. "WHERE team1_id = "
. $team_ids[$i]
. " AND team2_id = "
. $team_ids[$j]
. " AND m_id "
. "IN ('"
. $mdid_str
. " ') ";
$db->setQuery($query);
$match_score = $db->loadResult();
$results[$team_ids[$i]][$team_ids[$j]] = $match_score;
}
}
}
At this point, we find out all the matches within one specific season, and store all those scores in a two dimension array:$results[ ]. The outer array's key is home team's id, and the inner array's key is away team's id.
The php script in layout PHP file for outputting the table HTML:
<?php
$j = 0;
foreach ( $this->teams as $team )
{
$j++;
echo "<tr>";
echo "<td>(".$j.")".$team->t_name."</td>";
$inner = 0;
foreach ( $this->teams as $team )
{
$inner++;
if($inner == $j)
{
echo "<td bgcolor='#B0C4D7'>" ;
echo "</td>";
}
else
{
echo "<td>";
if(null != $this->match_scores[$this->teams_id[$inner-1]][$this->teams_id[$j-1]])
{
echo $this->match_scores[$this->teams_id[$inner-1]][$this->teams_id[$j-1]];
}
echo "</td>";
}
}
echo "</tr>";
}
?>
The output:
Here is a query to select the current season with a specific tournment, MySQL doesn't support TOP clause, so we use LIMIT:
SELECT c.c_name, f.father_name, c.c_id
FROM father_table AS f, child_table AS c
WHERE c.f_id = f.id
AND f.id =3
ORDER BY c.c_id DESC
LIMIT 0 , 1
<?php echo $this->teams[0]['t_name'] ?>
won't work, you should use:
<?php echo $this->teams[0]->t_name ?>
because the element in array 'teams' is astdClass Object.
REFS:
http://docs.joomla.org/How_to_use_the_database_classes_in_your_script#loadObjectList.28.29
http://help.joomla.org/content/view/709/125/