修正每个球队在每个赛季的额外信息的缺陷-优快云博客

本文链接：https://blog.youkuaiyun.com/zlxadhkust/article/details/7746508

本文详细阐述了一种改进方法，用于在赛季保存时保留与球队的联合数据，包括纪律分与备注。通过引入is_highlight字段，实现对赛季和比赛日的详细管理，并在表单提交前确保所有数据的正确传递。最后，展示了如何在成绩龙虎榜中应用这些新字段，以提供更丰富的展示效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

保留已经建立的team与season关联的数据，和team与matchday关联的数据

这些信息目前是额外扣除的纪律分与备注。

问题在于（上篇描述过），当赛季被重新保存时，关于球队与该赛季的联合数据会被清除，原因是保存的方法是先删除season_teams表中所以属于该赛季的记录，然后再重新插入新记录，为每个球队。

现在将要采取的解决办法是：

首先验证每个球队id，在season_teams表中寻找它与赛季相关联的记录，如果有，则跳过处理下一个球队数据，如果没有，则新插入一条记录。

上述循环完成后，删除那些没出现在新的球队记录中的旧记录，利用mySQL的NOT IN语句。

代开文件：/administrator/components/com_joomsport/admin.joomsport.php

对函数BL_SeasonSave($option)进行修改：

将：

$query = "DELETE FROM #__bl_season_teams WHERE season_id = ".$row->s_id;
$db->setQuery($query);
$db->query();

$teams_season = JRequest::getVar( 'teams_season', array(0), '', 'array' );
JArrayHelper::toInteger($teams_season, array(0));
if(count($teams_season))
{
	foreach($teams_season as $teams)	// integer
	{
		$query = "INSERT INTO #__bl_season_teams(season_id,team_id) VALUES(".$row->s_id.",".$teams.")";
		$db->setQuery($query);
		$db->query();
	}
}

替换成：

$teams_season = JRequest::getVar( 'teams_season', array(0), '', 'array' );
JArrayHelper::toInteger($teams_season, array(0));
if(count($teams_season))
{
	foreach($teams_season as $teams)	// integer
	{
		$query = "SELECT COUNT(*) FROM #__bl_season_teams WHERE season_id = " . $row->s_id. " AND team_id = " . $teams;
		$db->setQuery($query);
		$result = $db->loadResult();
		
		if($result == 0)
		{
			$query = "INSERT INTO #__bl_season_teams(season_id,team_id) VALUES(".$row->s_id.",".$teams.")";
			$db->setQuery($query);
			$db->query();
		}
	}
	$query = "DELETE FROM #__bl_season_teams WHERE season_id = " . $row->s_id . " AND team_id NOT IN (". implode(",",$teams_season) . ")";
	$db->setQuery($query);
	$db->query();

}

将：

// added by Vincent 2012-07-09
$query = "SELECT t.t_type FROM #__bl_tournament AS t , #__bl_seasons as s WHERE s.t_id = t.id AND s.s_id = ".$row->s_id;
$db->setQuery($query);
$tour_type = $db->loadResult();	// string
if($tour_type == '1')	// 聯賽盃
{
	$query = "SELECT id AS mday_id FROM #__bl_matchday WHERE s_id = ".$row->s_id;
	$db->setQuery($query);
	$mday_ids = $db->loadResultArray();	// array of string
	if(!empty($mday_ids))
	{
		$mdid_str = implode("','", $mday_ids);

		$query = "DELETE FROM #__bl_matchday_teams WHERE matchday_id IN('". $mdid_str . "')";
		$db->setQuery($query);
		$db->query();

		if(count($teams_season))
		{
			foreach($mday_ids as $mdid)
			{
				foreach($teams_season as $teams)
				{
					$query = "INSERT INTO #__bl_matchday_teams(matchday_id, team_id) VALUES(".$mdid.",".$teams.")";
					$db->setQuery($query);
					$db->query();
				}

			}

		}
	}
}
// end of addition

替换成：

// added by Vincent 2012-07-09
$query = "SELECT t.t_type FROM #__bl_tournament AS t , #__bl_seasons as s WHERE s.t_id = t.id AND s.s_id = ".$row->s_id;
$db->setQuery($query);
$tour_type = $db->loadResult();	// string
if($tour_type == '1')	// 聯賽盃
{
	$query = "SELECT id AS mday_id FROM #__bl_matchday WHERE s_id = ".$row->s_id;
	$db->setQuery($query);
	$mday_ids = $db->loadResultArray();	// array of string
	if(!empty($mday_ids))
	{
		$mdid_str = implode("','", $mday_ids);

		if(count($teams_season))
		{
			foreach($mday_ids as $mdid)
			{
				foreach($teams_season as $teams)
				{
					$query = "SELECT COUNT(*) FROM #__bl_matchday_teams WHERE matchday_id = " . $mdid . " AND team_id = " . $teams;
					$db->setQuery($query);
					$result = $db->loadResult();
					if($result == 0)
					{
						$query = "INSERT INTO #__bl_matchday_teams(matchday_id, team_id) VALUES(" . $mdid . "," . $teams . ")";
						$db->setQuery($query);
						$db->query();
					}
				}

				// added
				$query = "DELETE FROM #__bl_matchday_teams WHERE matchday_id = " . $mdid . " AND team_id NOT IN (". implode(",",$teams_season) . ")";
				$db->setQuery($query);
				$db->query();

			}

		}
	}
}
// end of addition

增加新字段is_highlight到team与season，team与matchday的关系中

需要给season_teams与matchday_teams表增加新字段：is_highlight，来标记是否需要使用红色高亮来显示备注。

add-new-field-is_high

ALTER TABLE  `jos_bl_matchday_teams` CHANGE  `is_highlight`  `is_highlight` CHAR( 1 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT  '0'

在BL_TeamEdit($is_id, $option)里，我们所执行的SQL查询已经有获取这个字段了：matchday_teams的is_highlight。那么在

/administrator/components/com_joomsport/admin.joomsport.html.php

的bl_editTeam($row,$lists,$option)函数里，我们需要加入checkbox来呈现这个字段：

修改如下，这里面的逻辑参考了编辑matchDay的页面：

for($i=0;$i<count($lists['bonuses']);$i++){
	$bonuses = $lists['bonuses'][$i];
	echo '<tr><td><input type="hidden" name="sids[]" value="'.$bonuses->season_id.'" />'.$bonuses->name.'</td>';
	echo '<td><input style="width:300px" type="text" name="remarks[]" value="'.$bonuses->results_remark.'" />'.'</td>';
	//add by Vin 2012-07-14
	echo '<td><input type="checkbox" name="is_highlight[]" value="'.($bonuses->is_highlight ? 1 : 0).'" '.( $bonuses->is_highlight ? "checked" : "" ).' /></td>';
	//end of add
	echo '<td><input type="text" name="bonuses[]" value="'.floatval($bonuses->bonus_point).'" />'.'</td></tr>';
}


if( isset($lists['bonuses_md']) && !empty( $lists['bonuses_md'] ) )
{
	for($r=0;$r<count($lists['bonuses_md']);$r++)
	{
		$bonuses = $lists['bonuses_md'][$r];
		echo '<tr><td><input type="hidden" name="mdids[]" value="'.$bonuses->matchday_id.'" />'.$bonuses->name.'</td>';
		echo '<td><input style="width:300px" type="text" name="remarks_mdt[]" value="'.$bonuses->results_remark.'" />'.'</td>';
		//add by Vin 2012-07-14
		echo '<td><input type="checkbox" name="is_highlight_mdt[]" value="'.($bonuses->is_highlight ? 1 : 0).'" '.( $bonuses->is_highlight ? "checked" : "" ).' /></td>';
		//end of add
		echo '<td><input type="text" name="bonuses_mdt[]" value="'.floatval($bonuses->bonus_point).'" />'.'</td></tr>';
	}
}

注意，两个部分加入的input控件的名称保持了之前的差别。但是根据check box被提交到服务器的机制，这样做是不够的，服务器端将只收到被勾选的check box的值，而且都是0。现在继续参照编辑matchDay的做法，在提交表单的Javascript函数里，加入：

var highlighten = eval("document.adminForm['is_highlight[]']");
if(highlighten){
	if(highlighten.length)
	{
		for(i=0; i<highlighten.length; i++)
		{
			if(highlighten[i].checked)
			{
				highlighten[i].value = 1;
			}
			else
			{
				highlighten[i].value = 0;
			}
			highlighten[i].checked = true;
		}
	}
	else
	{
		if(highlighten.checked)
		{
			highlighten.value = 1;
		}
		else
		{
			highlighten.value = 0;
		}
		highlighten.checked = true;
	}
}

var highlighten_mday = eval("document.adminForm['is_highlight_mdt[]']");
if(highlighten_mday){
	if(highlighten_mday.length)
	{
		for(i=0; i<highlighten_mday.length; i++)
		{
			if(highlighten_mday[i].checked)
			{
				highlighten_mday[i].value = 1;
			}
			else
			{
				highlighten_mday[i].value = 0;
			}
			highlighten_mday[i].checked = true;
		}
	}
	else
	{
		if(highlighten_mday.checked)
		{
			highlighten_mday.value = 1;
		}
		else
		{
			highlighten_mday.value = 0;
		}
		highlighten_mday.checked = true;
	}
}

这段代码在提交前将所有的check box都设为勾选，这样即可以保证它们都被提交，然后根据是否勾选将其值设定为0或者1。

现在要做的是，在保存team的时候，将这些数据写进数据库，这就要修改函数BL_TeamSave($option)了：

在保存的SQL查询里加入新的字段，当然，要注意for循环使用的计数变量的不同，还有参数名称的不同：

if(isset($_POST['sids']) && count($_POST['sids']))
{
	//dump($_POST['is_highlight'], 'is-high-array');
	for($p=0;$p<count($_POST['sids']);$p++)
	{
		//dump($_POST['is_highlight'][$p], 'is_hl');
		$query = "UPDATE #__bl_season_teams SET bonus_point = ".($_POST['bonuses'][$p])
				.", results_remark = '".($_POST['remarks'][$p])	// modified by Vincent 14th-Nov-2011
				."', is_highlight = '" .($_POST['is_highlight'][$p])
				."' WHERE season_id=".$_POST['sids'][$p]." AND team_id=".$row->id;
		$db->setQuery($query);
		$db->query();
	}
}

// added by Vincent 2012-07-08
if(isset($_POST['mdids']) && count($_POST['mdids']))
{
	for($q=0;$q<count($_POST['mdids']);$q++)
	{
		$query = "UPDATE #__bl_matchday_teams SET bonus_point = " . ($_POST['bonuses_mdt'][$q])
				.", results_remark = '" . ($_POST['remarks_mdt'][$q])
				."', is_highlight = '" .($_POST['is_highlight_mdt'][$q])
				."' WHERE matchday_id = " . $_POST['mdids'][$q]." AND team_id = " . $row->id;
		$db->setQuery($query);
		$db->query();
	}
}
// end of addition

现在是时候使用这个新加入的字段了。

首先打开成绩龙虎榜的controller文件：/components/com_joomsport/views/rankinglist/view.html.php

if(1 == $ranktype)
{
	...
	
	$query = "SELECT bonus_point, results_remark, is_highlight FROM #__bl_matchday_teams "
					. "WHERE matchday_id = "
					. $mday_id
					. " AND team_id = "
					. $team->id;

	// end of mod
	$db->setQuery($query);
	$mday_team = $db->loadAssoc();

	$team_record->remark = isset($mday_team['results_remark']) ? $mday_team['results_remark'] : '';
	$team_record->bonus_deducted = isset($mday_team['bonus_point']) ? intval($mday_team['bonus_point']) : 0;
	// add by Vin 2012-07-14
	$team_record->is_highlight = $mday_team['is_highlight'] ? 1 : 0;
	...
	
}

else if(3 == $ranktype)
{
	...
	
	$query = "SELECT bonus_point, results_remark, is_highlight FROM #__bl_season_teams AS st, #__bl_matchday AS md "
					. "WHERE st.season_id =  "
					. $s_id
					. " AND st.team_id = "
					. $team->id;
	$db->setQuery($query);
	$season_team = $db->loadAssoc();

	$team_record->remark = $season_team['results_remark'];
	$team_record->bonus_deducted = intval($season_team['bonus_point']);
	// add by Vin 2012-07-14
	$team_record->is_highlight = $season_team['is_highlight'] ? 1 : 0;
	...
	
}

else if(2 == $ranktype)
{
	...
	
	$query = "SELECT bonus_point, results_remark, is_highlight FROM #__bl_season_teams AS st, #__bl_matchday AS md "
					. "WHERE st.season_id =  "
					. $s_id
					. " AND st.team_id = "
					. $team->id;
	$db->setQuery($query);
	$season_team = $db->loadAssoc();

	$team_record->remark = $season_team['results_remark'];
	$team_record->bonus_deducted = intval($season_team['bonus_point']);
	// add by Vin 2012-07-14
	$team_record->is_highlight = $season_team['is_highlight'] ? 1 : 0;
	...
	
}

所做的修改主要是在SQL中加入is_highlight，以及将这个得到的结果加入到球队对象作为属性。

现在打开视图文件：/components/com_joomsport/views/rankinglist/tmpl/default.php

将输出备注的语句修改成：

$red_font = "";
if($team['is_highlight'])
{
	$red_font = "class='vincent_red_font' ";
}
echo "<td ".$red_font.">" .$team['remark']. "</td>";

vincent_red_font是我们早已加入到template.css里的一个规则。