Joomla! (DAY 9) - Joomsport (DAY 7): Create a Real View 'Home VS Away'

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:

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/

http://www.phpq.net/mysql/mysql-select-skill.html