Help with result statistics page

[neljan]

Hello

I'm trying to create a page for my gaming website which will show some total statistics for matches played against each opponent.

For example, let's say the results I have are:

My Team 300 : 100 Some Team
My Team 200 : 100 Some Other Team
My Team 100 : 300 Some Team
My Team 300 : 100 Another Team
My Team 100 : 100 Another Team

What I would like to do, is output this:

[CODE]| Team name | Played | Won | Drawn | Lost | Biggest Win | Biggest Defeat |

| Another Team | 2 | 1 | 1 | 0 | 300 : 100 | n/a |
| Some Other Team | 1 | 1 | 0 | 0 | 200 : 100 | n/a |
| Some Team | 2 | 1 | 0 | 1 | 300 : 100 | 100 : 300 |[/CODE]

I have one database table for storing opponent data:

http://i86.photobucket.com/albums/k98/neljanization/Forum/01Dec091543.gif
http://i86.photobucket.com/albums/k98/neljanization/Forum/02Dec091543.gif

And another for storing results:

http://i86.photobucket.com/albums/k98/neljanization/Forum/03Dec091547.gif
http://i86.photobucket.com/albums/k98/neljanization/Forum/05Dec091549.gif

So far all I've managed to accomplish is a list of opponents:

http://www.clanrv.com/index.php?site=head2head

With this code:

<?php

if (isset($site)) $_language->read_module('clanwars');

// tab menu
echo '<div id="tabs">
		  <ul>
  			  <li><a href="index.php?site=clanwars"><span>Results</span></a></li>
  			  <li class="activetab"><a href="index.php?site=clanwars"><span>Results</span></a></li>
  			  <li><a href="index.php?site=individuals"><span>Individuals</span></a></li>
  			  <li><a href="index.php?site=records"><span>Records</span></a></li>
  		  </ul>
  	  </div>
  	  <div class="webspell-head"></div>';

// start template output
eval ("\$clanwars_head2head_head = \"".gettemplate("clanwars_head2head_head")."\";");
echo $clanwars_head2head_head;

// get opponent ID (oppID) from 'opponents' table
$opponents=safe_query("SELECT oppID FROM ".PREFIX."opponents ORDER BY clanname");

if(mysql_num_rows($opponents)) {

	while($opponentdata=mysql_fetch_array($opponents)) {

		$i=1;

		// get results from 'clanwars' table, for each opponent retrieved from 'opponent' table
		$alldata=safe_query("SELECT * FROM ".PREFIX."clanwars WHERE oppID='".$opponentdata['oppID']."' LIMIT 0,1");

			while($resultdata=mysql_fetch_array($alldata)) {

				// opponent name output
				$opponentname=$resultdata['opponent'];

				eval ("\$clanwars_head2head_content = \"".gettemplate("clanwars_head2head_content")."\";");
				echo $clanwars_head2head_content;
				$n++;

			}
	}

	echo '</table>

		  <div class="webspell-foot"></div>';
}
?>

Would anyone by so kind as to help me with the other things please? It'll probably take me months to get there otherwise 😕

Thanks very much in advance...

[neljan]

I've taken some of the code used on another page (this page) and I think I might have enough now to get some help :p

Here's the result: http://www.clanrv.com/index.php?site=head2head

My code:

<?php

if (isset($site)) $_language->read_module('clanwars');

// tab menu
echo '<div id="tabs">
		  <ul>
  			  <li><a href="index.php?site=clanwars"><span>Results</span></a></li>
			  <li class="activetab"><a href="index.php?site=head2head"><span>Head2head</span></a></li>
  			  <li><a href="index.php?site=individuals"><span>Individuals</span></a></li>
  			  <li><a href="index.php?site=records"><span>Records</span></a></li>
  		  </ul>
  	  </div>
  	  <div class="webspell-head"></div>';

// start template output
eval ("\$clanwars_head2head_head = \"".gettemplate("clanwars_head2head_head")."\";");
echo $clanwars_head2head_head;

$totaldrawall="";
$totalwonall="";
$totalloseall="";

// get opponent via oppID from 'opponents' table
$opponents=safe_query("SELECT oppID FROM ".PREFIX."opponents ORDER BY clanname");

// start loop
if(mysql_num_rows($opponents)) {

	while($opponentdata=mysql_fetch_array($opponents)) {

		$i=1;

		// get results from 'clanwars' table, for each opponent retrieved from 'opponent' table
		$alldata=safe_query("SELECT * FROM ".PREFIX."clanwars WHERE oppID='".$opponentdata['oppID']."' LIMIT 0,1");

			while($resultdata=mysql_fetch_array($alldata)) {

				// opponent name output
				$opponentname=$resultdata['opponent'];

				// get total number of clanwars played vs opponent
				$played=safe_query("SELECT * FROM ".PREFIX."clanwars WHERE oppID='".$opponentdata['oppID']."'");
				$p=mysql_num_rows($played);

				while($cwdata = mysql_fetch_array($played)) {

					// get total score for my team from clanwars played vs the opponent
					$totalhomeqry=safe_query("SELECT homescore FROM ".PREFIX."clanwars WHERE oppID='$cwdata[oppID]'");
					while($theHomeData = mysql_fetch_array($totalhomeqry)) {
						$totalHomeScore+=array_sum(unserialize($theHomeData['homescore']));
						$theHomeScore=array_sum(unserialize($theHomeData['homescore']));
					}

					// get total opponents score for clanwars played vs my team
					$totalhomeqry=safe_query("SELECT oppscore FROM ".PREFIX."clanwars WHERE oppID='$cwdata[oppID]'");
					while($theHomeData = mysql_fetch_array($totalhomeqry)) {
						$totalHomeScore+=array_sum(unserialize($theHomeData['oppscore']));
						$theHomeScore=array_sum(unserialize($theHomeData['oppscore']));
					}

					if($theHomeScore > $theOppScore) $totalwonall++;
					if($theHomeScore < $theOppScore) $totalloseall++;
					if($theHomeScore == $theOppScore) $totaldrawall++;

				}

				if(!$totalwonall) $totalwonall=0;
				if(!$totalloseall) $totalloseall=0;
				if(!$totaldrawall) $totaldrawall=0;

				eval ("\$clanwars_head2head_content = \"".gettemplate("clanwars_head2head_content")."\";");
				echo $clanwars_head2head_content;
				$n++;
			}

	} // end loop

	echo '</table>
		  <div class="webspell-foot"></div>';
}

?>

From what I can tell, the 'P' column is accurate and does show the number of games played vs the respective opponent, but the 'W' column seems to add the 'W' value of the previous opponent, to the next opponents 'P' value:

21st Clan 2 2 0 0
50% 5 7 0 0

[neljan]

Ok I've been making some progress but now I've hit a wall.

Here's where I'm now at:

http://www.clanrv.com/index.php?site=head2head

I now just need to figure out how to get the biggest win and defeat vs the opponent and to also get the pagination working 😕

Here's the full code. I've added some comments which I hope will help you to help me:

<?php

// added for page_link
	if(isset($_GET['page'])) $page=(int)$_GET['page'];
	else $page = 1;
// end

// tab menu
echo '<div id="tabs">
		  <ul>
  			  <li><a href="index.php?site=clanwars"><span>Results</span></a></li>
			  <li class="activetab"><a href="index.php?site=head2head"><span>Head2head</span></a></li>
  			  <li><a href="index.php?site=individuals"><span>Individuals</span></a></li>
  			  <li><a href="index.php?site=records"><span>Records</span></a></li>
  		  </ul>
  	  </div>
  	  <div class="webspell-head"></div>';

// get opponent information
$opponents=safe_query("SELECT * FROM ".PREFIX."opponents ORDER BY clanname");

// added for page_link
	$pages=1;
	$max=20;
	$pages = ceil($opponents/$max);

if($pages>1) $page_link = makepagelink("index.php?site=head2head", $page, $pages);
else $page_link = "";

if ($page == "1") {
	$ergebnis = safe_query("SELECT * FROM ".PREFIX."opponents ORDER BY clanname LIMIT 0,$max");
	$n=1;
}
else {
	$start=$page*$max-$max;
	$ergebnis = safe_query("SELECT * FROM ".PREFIX."opponents ORDER BY clanname LIMIT $start,$max");
	$n = ($opponents+1)-$page*$max+$max;
}
// end page_link

if(mysql_num_rows($opponents)) {

	// start output - head
	eval ("\$head2head_head = \"".gettemplate("head2head_head")."\";");
	echo $head2head_head;

// added for page_link
		$n=1;
// end page_link

	while($opponentdata=mysql_fetch_array($opponents)) {

  		$opponent=getoppname($opponentdata['oppID']);
		$oppflag='<img src="'.$prefix.'images/flags/'.$opponentdata['country'].'.gif" width="16" height="11" border="0" alt="" />';

  		$drawall="";
  		$wonall="";
  		$loseall="";

		$opponentcws=safe_query("SELECT * FROM ".PREFIX."clanwars WHERE oppID='".$opponentdata['oppID']."'");
		$total=mysql_num_rows($opponentcws);

		while($opponentcwdata=mysql_fetch_array($opponentcws)) {

			// scores for my clan vs opponent
			$opHomeScoreQry=mysql_fetch_array(safe_query("SELECT homescore FROM ".PREFIX."clanwars WHERE cwID='".$opponentcwdata['cwID']."' AND oppID='".$opponentdata['oppID']."'"));
			$opHomeScore=array_sum(unserialize($opHomeScoreQry['homescore']));

			// scores for opponent vs my clan
			$opOppScoreQry=mysql_fetch_array(safe_query("SELECT oppscore FROM ".PREFIX."clanwars WHERE cwID='".$opponentcwdata['cwID']."' AND oppID='".$opponentdata['oppID']."'"));
			$opOppScore=array_sum(unserialize($opOppScoreQry['oppscore']));

			// counts +1 for each win for my clan
			if($opHomeScore > $opOppScore) $wonall++;
			// counts +1 for each defeat against my clan
			if($opHomeScore < $opOppScore) $loseall++;
			// counts +1 for each draw
			if($opHomeScore == $opOppScore) $drawall++;

			// Trying to figure out a way to find biggest win
			if($opHomeScore > $opOppScore) $win_difference = $opHomeScore - $opOppScore;
			if($opHomeScore < $opOppScore) $defeat_difference = $opOppScore - $opHomeScore;

			//something needs to go here to 'select' greatest '$win_difference' to get $biggest_win vs opponent
			//something needs to go here to 'select' greatest '$defeat_difference' to get $biggest_defeat vs opponent

			unset($opHomeScore);
			unset($opOppScore);
		}

		// fill empty vars
		if(empty($wonall)) $wonall=0;
		if(empty($loseall)) $loseall=0;
		if(empty($drawall)) $drawall=0;

		// start output
		eval("\$head2head_content = \"".gettemplate("head2head_content")."\";");
		echo $head2head_content;

		unset($wonall);
		unset($loseall);
		unset($drawall);
		unset($opponentcwdata);

// added for page_link
			$n++;
// end page_link
		}

	echo '</table>

		  <table class="webspell-content" width="500px" border="0">
		  	<tr>
		    	<td style="padding: 0 0 5px 10px; text-align: left;">'.$page_link.'</td>
			</tr>
		  </table>

		  <div class="webspell-foot"></div>';
}

?>

Any pointers?

[neljan]

Ok worked out pagination, now I just need this part (and I know it's beyond me):

// Trying to figure out a way to find biggest win 
                if($opHomeScore > $opOppScore) $win_difference = $opHomeScore - $opOppScore; 
                if($opHomeScore < $opOppScore) $defeat_difference = $opOppScore - $opHomeScore; 

            //something needs to go here to 'select' greatest '$win_difference' to get $biggest_win vs opponent 
            //something needs to go here to 'select' greatest '$defeat_difference' to get $biggest_defeat vs opponent

[johanafm]

Initialize $biggestWin = $biggestLoss = 0 before the while loop.

Then after you calculate the current win / loss

if ($opHomeScore > $opOppScore)
{
	$win_difference = $opHomeScore - $opOppScore; 
	if ($win_difference > $biggestWin)
	{
		$biggestWin = $win_difference;
	}
}

[neljan]

Johan again to the rescue! <3

Almost there, but the difference was to identify the biggest win/loss and then display the relevant result (I don't want to display the difference).

So I altered your code slightly:

			    // get biggest win
				if ($opHomeScore > $opOppScore) { 
    				$win_difference = $opHomeScore - $opOppScore; 
    				if ($win_difference > $biggestWin) { 
        				$biggestWin = "$opHomeScore : $opOppScore";
    				}
				}

			// get biggest defeat
			if ($opHomeScore < $opOppScore) { 
				$loss_difference = $opHomeScore - $opOppScore; 
				if ($loss_difference < $biggestLoss) { 
    				$biggestLoss = "$opHomeScore : $opOppScore";
				}
			}

Result: http://www.clanrv.com/index.php?site=head2head

It seems to be displaying the last win and first defeat. For example, the biggest win vs "All Stars" is actually 451:281 but it is displaying the last recorded win which was 421:311. The biggest defeat vs "All Stars" is 325:396 but the first defeat of 360-361 is shown instead.

[Derokorian]

YOu need to leave $biggestWin and $biggestLoss as the difference in scores, and then have another variables, say $biggestWinScore and $biggestLossScore to put that value in, because you end up comparing a string instead of numbers.

[neljan]

You legends!

Thanks a lot guys, love you both 🙂

[neljan]

Hey guys

Now I'm trying to get the 10 biggest wins from all results.

I've managed to get the biggest win:

http://www.clanrv.com/index.php?site=records

But I'm stuck on how to get the 10 biggest.

Code:

$all_cws=safe_query("SELECT * FROM ".PREFIX."clanwars");

if(mysql_num_rows($all_cws)) {

	// start output - head
	eval("\$records_biggestwins_head = \"".gettemplate("records_biggestwins_head")."\";");
	echo $records_biggestwins_head;
	$n=1;

	$biggestWin = $biggestLoss = 0;

	while($cw_data = mysql_fetch_array($all_cws)) {

		// total home points
		$totalhomeqry=safe_query("SELECT homescore FROM ".PREFIX."clanwars WHERE cwID='$cw_data[cwID]'");
		while($theHomeData = mysql_fetch_array($totalhomeqry)) {
			$theHomeScore=array_sum(unserialize($theHomeData['homescore']));
		}

		// total opponent points
		$totaloppqry=safe_query("SELECT oppscore FROM ".PREFIX."clanwars WHERE cwID='$cw_data[cwID]'");
		while($theOppData = mysql_fetch_array($totaloppqry)) {
			$theOppScore=array_sum(unserialize($theOppData['oppscore']));
		}

		// get biggest win
		if ($theHomeScore > $theOppScore) { 
			$win_difference = $theHomeScore - $theOppScore; 
			if ($win_difference > $biggestWin) { 
    			$biggestWin = $win_difference;
				$biggestWinScore = '<a href="index.php?site=clanwars_details&amp;cwID='.$cw_data['cwID'].'">'.$theHomeScore.' : '.$theOppScore.'</a>';
				$opponent=getoppname($cw_data['oppID']);
				$oppflag='<img src="'.$prefix.'images/flags/'.$cw_data['oppcountry'].'.gif" width="16" height="11" border="0" alt="" />';
				$date=date("d.m.Y", $cw_data['date']);
			}
		}

		unset($theHomeScore);
		unset($theOppScore);
	}

	// start output
	eval("\$records_biggestwins_content = \"".gettemplate("records_biggestwins_content")."\";");
	echo $records_biggestwins_content;

	unset($cw_data);

	$n++;
}

Thanks again for your time :o

[johanafm]

Can't you simply

SELECT (homescore - oppscore) AS scorediff, CONCAT(homescore, ':', oppscore) 
FROM clanwars
ORDER BY scorediff DESC
LIMIT 10

[neljan]

Where would I put that?

[johanafm]

Wherever you want to retrieve the top 10 wins from the database.

[neljan]

The db table entries for homescore/oppscore look like so:

a:3:{i:0;s:3:"140";i:1;s:3:"134";i:2;s:3:"110";}

And to get homescore (for example) I need to use:

$homescr = array_sum(unserialize($whatever['homescore']));

Your suggestion would still work without unserialize?

[johanafm]

Hmm, why are you storing serialized data?

[neljan]

I think it's because of the way in which results are entered:

'map name' = 'points'
'map name' = 'points'
'map name' = 'points'
(etc)

You can have a match with unlimited number of maps. So instead of having a db column for map1 and another for map2 (etc etc) and a limit (number of columns) all maps/scores are stored in one column.

To be honest, I would only ever need 3 maps and could change the db structure, but if I can get away with not having to do that it'd be preferable, since I'd have to re-enter all 1000+ results currently stored.

[Derokorian]

First you could build a PHP script to auto-magically fix the DB schema when you get the right one.

Second proper database design says that you wouldn't do map1 map2 etc anyway, you would instead have another table with references the primary key from this table, which would store the map and points there, leaving you unlimited number of maps for each map AND the ability to work with the data directly from the database. I would also store the maps in their own table, and then use an id to reference each map.

Example of new table:
match_id map_id score

[johanafm]

neljan;10992954 wrote:

You can have a match with unlimited number of maps. So instead of having a db column for map1 and another for map2 (etc etc) and a limit (number of columns) all maps/scores are stored in one column.

Both of those approaches break database normalization. What you should do is in fact store each map in its own row in a separate table.

I don't really understand what the identifiers in your gif images meant, so I came up with some names on my own, but I believe you'll understand what I mean.

-- id is primary key in all tables, unless otherwise specified.

clan
-------------
id
name
etc

tournament
--------------
id
home_clan
opponent_clan
etc
FOREIGN KEY home_clan REFERENCES clan(id),
FOREIGN KEY opponent_clan REFERENCES clan(id)

games
--------
id
tournament_id
map_id
home_score
opponent_score
game_date
FOREIGN KEY(tournament_id) REFERENCES tournament(id)

Which means that each tournament involves two clans, and may have any number of games (number of games matches number of matching rows in games table). If you do not consider one clan to be always "home" during a tournament, move the home and opponent clan ids to the games table.

neljan;10992954 wrote:

To be honest, I would only ever need 3 maps and could change the db structure, but if I can get away with not having to do that it'd be preferable

Working with unnormalized data always take more time in the long run than fixing what's broken to begin with, so no, it really isn't preferable.

neljan;10992954 wrote:

since I'd have to re-enter all 1000+ results currently stored.

No, you just have to write the code that does it for you 🙂

[neljan]

Thanks guys

I've taken your advice and I've restructured my db table. I'm now working on a script to transfer (or copy) results from the old serialized fields to the new fields.

Basically I want to split this:

a:3:{i:0;s:3:"140";i:1;s:3:"134";i:2;s:3:"110";}

Into this:

140
134
110

Can you please help?

This gives me the total:

$homescore=array_sum(unserialize($whatever['homescore']));

[johanafm]

neljan;10992975 wrote:

Basically I want to split this:

a:3:{i:0;s:3:"140";i:1;s:3:"134";i:2;s:3:"110";}

Into this:

140
134
110

What you need to do is select all records like those above. You then just unserialize that field and get an array containing (140, 134, 110). At that point, you foreach over the array and insert those values in the newly created table. If you need more help, you'd have to post the relevant schema info (show create table).

[neljan]

I appreciate your help Johan.

Not really sure what you mean by schema, so here's everything that I can think of that could help...

First, here are the fields within the 'clanwars' db table that contain data that I would like to unserialize and move to other fields (except for 'cwID' and 'maps'):

[CODE]| cwID | maps | hometeam | homescore | oppscore |

| 40 | a:3:{i:0;s:1:"1";i:1;s:1:"2";i:2;s:1:"3";} | a:2:{i:0;s:2:"57";i:1;s:3:"217";} | a:3:{i:0;s:3:"140";i:1;s:3:"134";i:2;s:3:"110";} | a:3:{i:0;s:2:"86";i:1;s:3:"110";i:2;s:3:"134";} |[/CODE]

Notes:

• cwID is the index (why I included it).
• I do not need to keep 'maps' since I won't be using them in the future, but I included them here because they are linked to 'homescore' and 'oppscore' in the coding.
• 'hometeam' contains userID numbers to represent the players who played. They appear to be stored alphabetically according to the players nickname (for example 57 = 'Adam' and 217 = 'Bob') since other entries are not in numerical order but are alphabetical.
• 'homescore' and 'oppscore' contain 3 scores for each of the 3 maps that were played and make-up a match (or clanwar).

Here's the new fields that I've created, containing the unserialized data (to give you an idea of what I would like):

[CODE]| cwID | player1 | player2 | player3 | player4 | player5 | player6 | player7 | home_score1 | home_score2 | home_score3 | home_total | op_score1 | op_score2 | op_score3 | op_total | difference |

| 40 | 57 | 217 | | | | | | 140 | 134 | 110 | | 86 | 110 | 134 | | |[/CODE]

This is the code used which serializes/stores the data:

if(isset($_POST['hometeam'])) $hometeam = $_POST['hometeam'];

if(isset($_POST['map_name'])) $maplist = $_POST['map_name'];
if(isset($_POST['map_result_home'])) $homescr = $_POST['map_result_home'];
if(isset($_POST['map_result_opp'])) $oppscr = $_POST['map_result_opp'];

$maps = array();
if(!empty($maplist)) {
	if(is_array($maplist)) {
		foreach($maplist as $map) {
			$maps[]=stripslashes($map);
		}
	}
}
$backup_theMaps = serialize($maps);

if(function_exists("mysql_real_escape_string")) {
	$theMaps = mysql_real_escape_string($backup_theMaps);
}
else {
	$theMaps = addslashes($backup_theMaps);
}

$scores = array();
if(!empty($homescr)) {
	if(is_array($homescr)) {
		foreach($homescr as $result) {
			$scores[]=$result;
		}
	}
}
$theHomeScore = serialize($scores);

$results = array();
if(!empty($oppscr)) {
	if(is_array($oppscr)) {
		foreach($oppscr as $result) {
			$results[]=$result;
		}
	}
}
$theOppScore = serialize($results);

$team=array();
if(is_array($hometeam)) {
	foreach($hometeam as $player) {
		if(!in_array($player, $team)) $team[]=$player;
	}
}

$home_string = serialize($team);

safe_query("INSERT INTO ".PREFIX."clanwars ( maps, hometeam, homescore, oppscore)
             VALUES( '".$theMaps."', '$home_string', '$theHomeScore', '$theOppScore' ) ");

$cwID=mysql_insert_id();

And the code used to unserialize/show it:

$ds=mysql_fetch_array(safe_query("SELECT * FROM ".PREFIX."clanwars WHERE cwID='$cwID'"));
	$maps="";
	$hometeam="";
	$score="";
	$extendedresults="";

$homescr = array_sum(unserialize($ds['homescore']));
$oppscr = array_sum(unserialize($ds['oppscore']));
$theMaps = unserialize($ds['maps']);

  if(is_array($theMaps)) {
		$n=1;
		foreach($theMaps as $map) {
			if($n == 1) {
				$maps.=$map;
			} else {
				if($map=='') {
					$maps=$_language->module['no_maps'];
				} else {
					$maps.=', '.$map;
				}
			}
			$n++;
		}
	}

if($homescr>$oppscr){
	$results_1='<font color="'.$wincolor.'">'.$homescr.'</font>';
	$results_2='<font color="'.$loosecolor.'">'.$oppscr.'</font>';
}
elseif($homescr<$oppscr){
	$results_1='<font color="'.$loosecolor.'">'.$homescr.'</font>';
	$results_2='<font color="'.$wincolor.'">'.$oppscr.'</font>';
}
else{
	$results_1='<font color="'.$drawcolor.'">'.$homescr.'</font>';
	$results_2='<font color="'.$drawcolor.'">'.$oppscr.'</font>';
}

  if(!empty($ds['hometeam'])) {
		$array=unserialize($ds['hometeam']);
		$n=1;
		foreach($array as $id) {
			if(!empty($id)) {
				if($n>1) $hometeam.=', <a href="index.php?site=profile&amp;id='.$id.'">'.getnickname($id).'</a>';
				else $hometeam.='<a href="index.php?site=profile&amp;id='.$id.'">'.getnickname($id).'</a>';
				$n++;
			}
		}
	}

$scoreHome=unserialize($ds['homescore']);
$scoreOpp=unserialize($ds['oppscore']);
$homescr=array_sum($scoreHome);
$oppscr=array_sum($scoreOpp);

if($homescr>$oppscr) {
	$result_map='[color='.$wincolor.']'.$homescr.'[/color] : [color='.$loosecolor.']'.$oppscr.'[/color]';
	$result_map2='won';
}
elseif($homescr<$oppscr) {
	$result_map='[color='.$loosecolor.']'.$homescr.'[/color] : [color='.$wincolor.']'.$oppscr.'[/color]';
	$result_map2='lost';
}
else {
	$result_map='[color='.$drawcolor.']'.$homescr.'[/color] : [color='.$drawcolor.']'.$oppscr.'[/b][/color]';
	$result_map2='draw';
}

  if(is_array($theMaps)) {
		$d=0;
		foreach($theMaps as $map) {
			$score='';
      		if(($d+1)%2) { $bgone=BG_1; $bgtwo=BG_2; } else { $bgone=BG_3; $bgtwo=BG_4; }
			if($scoreHome[$d] > $scoreOpp[$d]){
				$score_1='<font color="'.$wincolor.'">'.$scoreHome[$d].'</font>';
				$score_2='<font color="'.$loosecolor.'">'.$scoreOpp[$d].'</font>';
			}
			elseif($scoreHome[$d] < $scoreOpp[$d]){
				$score_1='<font color="'.$loosecolor.'">'.$scoreHome[$d].'</font>';
				$score_2='<font color="'.$wincolor.'">'.$scoreOpp[$d].'</font>';
			}
			else{
				$score_1='<font color="'.$drawcolor.'">'.$scoreHome[$d].'</font>';
				$score_2='<font color="'.$drawcolor.'">'.$scoreOpp[$d].'</font>';
			}

  		eval ("\$clanwars_details_results = \"".gettemplate("clanwars_details_results")."\";");
  		$extendedresults.=$clanwars_details_results;
		unset($score);
		$d++;
	}
} else $extendedresults='';

So far I have this as a pathetic attempt at a script:

<?php

// gets all data
$ergebnis=(safe_query("SELECT * FROM ".PREFIX."clanwars"));

if($ergebnis) {

	while($ds=mysql_fetch_array($ergebnis)) {

		// gets total scores
		$homescore=array_sum(unserialize($ds['homescore']));
		$oppscore=array_sum(unserialize($ds['oppscore']));

		// gets difference
		$difference = $homescore - $oppscore;

	}

}

safe_query("UPDATE ".PREFIX."clanwars SET 
								 home_total='$homescore',
								 op_total='$oppscore',
                                 difference='$difference'
                                 ");

?>

Another question I have is would the above write the relevant data for the correct cwID, since it is selecting all data?

(sorry if this was not what you needed to see/wasted your time).