Mysql - Classifica campionato (Totale, casa, fuori casa)

[kikoweb]

Salve, ho scaricato uno scritp per Wordpress per la realizzazione di un sito web di statistiche di calcio etc, ho un problema relativo alla visualizzazione della classifica.
Le funzioni di sotto visualizzano la classifica completa, cioè totale delle partite giocate, goal fatti subiti etc, io vorrei poter estrapolare però anche una classifica in base alle partite giocate in casa o fuori casa oltre a questa che già va bene per la classifica totale.
Ho provato vari modi ma non sono riuscito a trovare una soluzione valida al mio problema.
Nelle tabelle, x chiarezza, spiego cosa contengono i campi in questione.
In particolare nella tabella "leaguemanager_matches":

id -> id dell'incontro giocato
home_team -> id della squadra di casa
away_team -> id della squadra fuori casa
home_points -> goal fatti dalla squada di casa nel relativo incontro giocato
away_points -> goal fatti dalla squada fuori casa nel relativo incontro giocato

esempio:


incontro giocato milan-juventus 3-2 avente come id incontro 18
id del milan: 39
id della juventus: 44

id -> 18
home_team -> 39
away_team -> 44
home_points -> 3
away_points -> 2



Le tabelle in questione:

$create_teams_sql = "CREATE TABLE {$wpdb->leaguemanager_teams} (
						`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
						`status` varchar( 50 ) NOT NULL default '•',
						`title` varchar( 100 ) NOT NULL default '',
						`logo` varchar( 150 ) NOT NULL default '',
						`website` varchar( 255 ) NOT NULL default '',
						`coach` varchar( 100 ) NOT NULL default '',
						`stadium` varchar( 150 ) NOT NULL default '',
						`home` tinyint( 1 ) NOT NULL default '0',
						`points_plus` float NOT NULL default '0',
						`points_minus` float NOT NULL default '0',
						`points2_plus` int( 11 ) NOT NULL default '0',
						`points2_minus` int( 11 ) NOT NULL default '0',
						`add_points` int( 11 ) NOT NULL default '0',
						`done_matches` int( 11 ) NOT NULL default '0',
						`won_matches` int( 11 ) NOT NULL default '0',
						`draw_matches` int( 11 ) NOT NULL default '0',
						`lost_matches` int( 11 ) NOT NULL default '0',
						`diff` int( 11 ) NOT NULL default '0',
						`group` varchar( 30 ) NOT NULL default '',
						`league_id` int( 11 ) NOT NULL,
						`season` varchar( 255 ) NOT NULL default '',
						`rank` int( 11 ) NOT NULL default '0',
						`roster` longtext NOT NULL,
						`custom` longtext NOT NULL,
						PRIMARY KEY ( `id` )) $charset_collate;";
		maybe_create_table( $wpdb->leaguemanager_teams, $create_teams_sql );
			
		$create_matches_sql = "CREATE TABLE {$wpdb->leaguemanager_matches} (
						`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
						`group` varchar( 30 ) NOT NULL default '',
						`date` datetime NOT NULL default '0000-00-00',
						`home_team` varchar( 255 ) NOT NULL default '0',
						`away_team` varchar( 255 ) NOT NULL default '0',
						`match_day` tinyint( 4 ) NOT NULL default '0',
						`location` varchar( 100 ) NOT NULL default '',
						`league_id` int( 11 ) NOT NULL default '0',
						`season` varchar( 255 ) NOT NULL default '',
						`home_points` varchar( 30 ) NULL default NULL,
						`away_points` varchar( 30 ) NULL default NULL,
						`winner_id` int( 11 ) NOT NULL default '0',
						`loser_id` int( 11 ) NOT NULL default '0',
						`post_id` int( 11 ) NOT NULL default '0',
						`final` varchar( 150 ) NOT NULL default '',
						`custom` longtext NOT NULL,
						PRIMARY KEY ( `id` )) $charset_collate;";
		maybe_create_table( $wpdb->leaguemanager_matches, $create_matches_sql );
		$create_stats_sql = "CREATE TABLE {$wpdb->leaguemanager_stats} (
						`id` int( 11 ) NOT NULL AUTO_INCREMENT,
						`name` varchar( 30 ) NOT NULL default '',
						`fields` longtext NOT NULL,
						`league_id` int( 11 ) NOT NULL,
						PRIMARY KEY ( `id` )) $charset_collate;";
		maybe_create_table( $wpdb->leaguemanager_stats, $create_stats_sql );

La funzione che visualizza la classifica:

	/**
	 * Function to display League Standings
	 *
	 *	[standings league_id="1" mode="extend|compact" template="name"]
	 *
	 * - league_id is the ID of league
	 * - league_name (optional) get league by name and not id
	 * - season: display specific season (optional). default is current season
	 * - template is the template used for displaying. Replace name appropriately. Templates must be named "standings-template.php" (optional)
	 * - group: optional
	 *
	 * @param array $atts
	 * @param boolean $widget (optional)
	 * @return the content
	 */
	function showStandings( $atts, $widget = false )
	{
		global $wpdb, $leaguemanager;
		
		extract(shortcode_atts(array(
			'league_id' => 0,
			'league_name' => '',
			'logo' => 'true',
			'template' => 'extend',
			'season' => false,
			'group' => false,
			'home' => 0
		), $atts ));
		
		$search = !empty($league_name) ? $league_name : $league_id;
		$league = $leaguemanager->getLeague( $search );
		if (!$season) {
			$season = $leaguemanager->getSeason( $league );
			$season = $season['name'];
		}

		$search = "`league_id` = '".$league->id."' AND `season` = '".$season."'";
		if ( $group ) $search .= " AND `group` = '".$group."'";
		$teams = $leaguemanager->getTeams( $search );
	
		if ( !empty($home) ) {
			$teamlist = array();
			foreach ( $teams AS $offset => $team ) {
				if ( $team->home == 1 ) {
					$low = $offset-$home;
					$high = $offset+$home;

					if ( $low < 0 ) {
						$high -= $low;
						$low = 0;
					} elseif ( $high > count($teams)-1 ) {
						$low -= $high - count($teams)+1;
						$high = count($teams)-1;
					}

					for ( $x = $low; $x <= $high; $x++ ) {
						if ( !array_key_exists($teams[$x]->rank, $teamlist) ) 
							$teamlist[$teams[$x]->rank] = $teams[$x];
					}
				}
			}
			
			$teams = array_values($teamlist);
		}

		$i = 0; $class = array();
		foreach ( $teams AS $team ) {
			$class = ( in_array('alternate', $class) ) ? array() : array('alternate');
			// Add classes for ascend or descend
			if ( $team->rank <= $league->num_ascend ) $class[] = 'ascend';
			elseif ( count($teams)-$team->rank < $league->num_descend ) $class[] =  'descend';

			// Add class for relegation
			if ( $team->rank >  count($teams)-$league->num_descend-$league->num_relegation && $team->rank <= count($teams)-$league->num_descend ) $class[] = 'relegation';

			// Add class for home team
			if ( 1 == $team->home ) $class[] = 'homeTeam';
			
			$url = get_permalink();
			$url = add_query_arg( 'team', $team->id, $url );

			$teams[$i]->pageURL = $url;
			//if ( $league->team_ranking == 'auto' ) $teams[$i]->rank = $i+1;
			$teams[$i]->class = implode(' ', $class);
			$teams[$i]->logoURL = $leaguemanager->getThumbnailUrl($team->logo);
			if ( 1 == $team->home ) $teams[$i]->title = ''.$team->title.'';
			if ( $team->website != '' ) $teams[$i]->title = ''.$team->title.'';
			
			$team->points_plus += $team->add_points; // add or substract points
			$teams[$i]->points = sprintf($league->point_format, $team->points_plus, $team->points_minus);
			$teams[$i]->points2 = sprintf($league->point_format2, $team->points2_plus, $team->points2_minus);
			$i++;
		}
		
		$league->show_logo = ( $logo == 'true' ) ? true : false;

		if ( !$widget && $this->checkTemplate('standings-'.$league->sport) )
			$filename = 'standings-'.$league->sport;
		else
			$filename = 'standings-'.$template;

		$out = $this->loadTemplate( $filename, array('league' => $league, 'teams' => $teams, 'widget' => $widget) );
			
		return $out;
	}

La funzione che viene richiamata dalla precedente e che seleziona le squadre, e i relativi dati riguardanti i goal, partite giocate, partite vinte, pareggiate, perse etc.. ovviamente però totali!

/**
	 * get teams from database
	 *
	 * @param string $search search string for WHERE clause.
	 * @param string $output OBJECT | ARRAY
	 * @return array database results
	 */
	function getTeams( $search, $orderby = false, $output = 'OBJECT' )
	{
		global $wpdb;
		
		if ( !empty($search) ) $search = " WHERE $search";
		if ( !$orderby ) $orderby = "`rank` ASC, `id` ASC";

		$teamlist = $wpdb->get_results( "SELECT `title`, `website`, `coach`, `stadium`, `logo`, `home`, `group`, `roster`, `points_plus`, `points_minus`, `points2_plus`, `points2_minus`, `add_points`, `done_matches`, `won_matches`, `draw_matches`, `lost_matches`, `diff`, `league_id`, `id`, `season`, `rank`, `status`, `custom` FROM {$wpdb->leaguemanager_teams} $search ORDER BY $orderby" );
		$teams = array(); $i = 0;
		foreach ( $teamlist AS $team ) {
			$team->custom = maybe_unserialize($team->custom);
			if ( 'ARRAY' == $output ) {
				$teams[$team->id]['title'] = htmlspecialchars(stripslashes($team->title), ENT_QUOTES);
				$teams[$team->id]['rank'] = $team->rank;
				$teams[$team->id]['status'] = $team->status;
				$teams[$team->id]['season'] = $team->season;
				$teams[$team->id]['website'] = $team->website;
				$teams[$team->id]['coach'] = $team->coach;
				$teams[$team->id]['stadium'] = $team->stadium;
				$teams[$team->id]['logo'] = $team->logo;
				$teams[$team->id]['home'] = $team->home;
				$teams[$team->id]['group'] = $team->group;
				$teams[$team->id]['roster'] = maybe_unserialize($team->roster);
				if ( $this->hasBridge() ) {
					global $lmBridge;
					$teams[$team->id]['teamRoster'] = $lmBridge->getTeamRoster(maybe_unserialize($team->roster));
				}
				$teams[$team->id]['points'] = array( 'plus' => $team->points_plus, 'minus' => $team->points_minus );
				$teams[$team->id]['points2'] = array( 'plus' => $team->points2_plus, 'minus' => $team->points2_minus );
				$teams[$team->id]['add_points'] = $team->add_points;
				foreach ( (array)$team->custom AS $key => $value )
					$teams[$team->id][$key] = $value;
			} else {
				$teamlist[$i]->roster = maybe_unserialize($team->roster);
				if ( $this->hasBridge() ) {
					global $lmBridge;
					$teamlist[$i]->teamRoster = $lmBridge->getTeamRoster(maybe_unserialize($team->roster));
				}
				$teamlist[$i]->title = htmlspecialchars(stripslashes($team->title), ENT_QUOTES);
				$teamlist[$i] = (object)array_merge((array)$team, (array)$team->custom);
			}

			unset($teamlist[$i]->custom, $team->custom);
			$i++;
		}

		if ( 'ARRAY' == $output )
			return $teams;

		return $teamlist;
	}

Qualcuno può aiutarmi?
Grazie!

[kikoweb]

Template che visualizza la classifica:

<?php
/**
Template page for the standings table in extended form (default)

The following variables are usable:
	
	$league: contains data about the league
	$teams: contains all teams of current league
	
	You can check the content of a variable when you insert the tag <?php var_dump($variable) ?>
*/
?>

<?php if ( isset($_GET['team']) && !$widget ) : ?>
	<?php leaguemanager_team($_GET['team']); ?>
<?php else : ?>

<?php if ( $teams ) : ?>

<table class="leaguemanager standingstable" summary="" title="<?php _e( 'Standings', 'leaguemanager' ) .' '.$league->title ?>">
<tr>
	<th class="num"><?php echo _c( 'Pos|Position', 'leaguemanager' ) ?></th>
	<th class="num">&#160;</th>
	<?php if ( $league->show_logo ) : ?>
	<th class="logo">&#160;</th>
	<?php endif; ?>
	
	<th><?php _e( 'Team', 'leaguemanager' ) ?></th>
	<?php if ( 1 == $league->standings['pld'] ) : ?>
	<th class="num"><?php _e( 'Pld', 'leaguemanager' ) ?></th>
	<?php endif; ?>
	<?php if ( 1 == $league->standings['won'] ) : ?>
	<th class="num"><?php echo _c( 'W|Won','leaguemanager' ) ?></th>
	<?php endif; ?>
	<?php if ( 1 == $league->standings['tie'] ) : ?>
	<th class="num"><?php echo _c( 'T|Tied','leaguemanager' ) ?></th>
	<?php endif; ?>
	<?php if ( 1 == $league->standings['lost'] ) : ?>
	<th class="num"><?php echo _c( 'L|Lost','leaguemanager' ) ?></th>
	<?php endif; ?>
	<?php do_action( 'leaguemanager_standings_header_'.$league->sport ) ?>
	<th class="num"><?php _e( 'Pts', 'leaguemanager' ) ?></th>
</tr>
<?php if ( $teams ) : ?>
<?php foreach( $teams AS $team ) : ?>

<tr class='<?php echo $team->class ?>'>
	<td class='rank'><?php echo $team->rank ?></td>
	<td class="num"><?php echo $team->status ?></td>
	<?php if ( $league->show_logo ) : ?>
	<td class="logo">
		<?php if ( $team->logo != '' ) : ?>
		[img]<?php echo $team->logoURL ?>[/img]' title='<?php _e('Logo','leaguemanager')." ".$team->title ?>' />
		<?php endif; ?>
	</td>
	<?php endif; ?>
	
	<td><?php echo $team->title ?></td>
	<?php if ( 1 == $league->standings['pld'] ) : ?>
	<td class='num'><?php echo $team->done_matches ?></td>
	<?php endif; ?>
	<?php if ( 1 == $league->standings['won'] ) : ?>
	<td class='num'><?php echo $team->won_matches ?></td>
	<?php endif; ?>
	<?php if ( 1 == $league->standings['tie'] ) : ?>
	<td class='num'><?php echo $team->draw_matches ?></td>
	<?php endif; ?>
	<?php if ( 1 == $league->standings['lost'] ) : ?>
	<td class='num'><?php echo $team->lost_matches ?></td>
	<?php endif; ?>
	<?php do_action( 'leaguemanager_standings_columns_'.$league->sport, $team, $league->point_rule ) ?>
	<td class='num'><?php echo $team->points ?></td>
</tr>
<?php endforeach; ?>
<?php endif; ?>
</table>

<?php endif; ?>
<?php endif; ?>