[SOLVED] Simple Looping

[Pawn]

Hi. Here's how it is:

 

I have a teams table and a matches table in a MySQL database. I need to display the statistics of each team. To avoid looking through every match each time a team profile is viewed, I have counters which I want to update at intervals.

 

My (limited) understanding is that I can do this with a PHP script executed as a cronjob. My question is: what is the most efficient way of looping through the matches and updating the counters in the teams table?

 

Thanks in advance for any help you can offer.

[ober]

We're going to need more info.  Where are these "counters" stored?  What does a "match" consist of?

 

A description of the DB fields would help.

 

Do you have any code that you've started with?

[Pawn]

My teams table includes the counters as matches_won and matches_lost. A match is basically just a list of team IDs, in the form team_id_1, team_id_2 and winner.

 

I just drafted this. I expect it's an ocean away from working or even making sense, but it might give a better of impression of what I want to do:

 

<?
$won = 0; $lost = 0;
$query_teams = mysql_query("SELECT * FROM teams");
while($current_team = mysql_fetch_array($query_teams)) {
$query_matches = mysql_query("SELECT * FROM matches WHERE team_id_1='$current_team[team_id]' OR team_id_2='$current_team[team_id]'");
while($current_match = mysql_fetch_array($query_matches)) {
	if($current_match['winner'] == $current_team['team_id']) {
		$won++;
	} else {
		$lost++;
	}
}
mysql_query("UPDATE teams SET games_won='$won', games_lost='$lost' WHERE team_id='$current_team[team_id]'");
}
?>

[gurroa]

I would do it in different way:

 

First count winnings and loosings:

 

$arteam = array();
$query = mysql_query('select team_id from teams order by team_id');
while($row = mysql_fetch_array($query)) {
  $arteam[$row['team_id']] = array(
    'won'  => 0,
    'lost' => 0
  );
}

$query = mysql_query('select winner, team_id_1, team_id_2 from matches');

while($row = mysql_fetch_array($query)) {
  $tm1id = $row['team_id_1'];
  $tm2id = $row['team_id_2'];
  
  if ($row['winner'] == $tm1id) {
    $arteam[$tm1id]['won'] += 1;
    $arteam[$tm2id]['lost'] += 1;
  } else {
    $arteam[$tm1id]['lost'] += 1;
    $arteam[$tm2id]['won'] += 1;
  }
}
  
reset($arteam);
while(list($id, $wonlostarray) = each($arteam)) {
  mysql_query(sprintf("
    update teams set games_won = %d, games_lost = %d WHERE team_id = %d
  ",
    $id, $wonlostarray['won'], $wonlostarray['lost']        
  ));
}

[Psycho]

No, no ,no. You do NOT need to update the Teams table with the results from the Matches table. The whole point of using a relational database is that you can access records across tables in a single query. So, if you have a page to display a team's profile you access ALL the data dynamically in real time.

 

You need to look at how to JOIN tables in your queries. You could do a JOIN in this instance, but I would just use two subqueries.

 

Run this query and you will get all the profile data for a team as well as their win/loss record in a single query.

 

$team_id = 5; //

$query = "SELECT *, 
            (SELECT COUNT(*) FROM test WHERE (team_id_1=$team_id OR team_id_2=$team_id) AND winner=$team_id) as wins,
            (SELECT COUNT(*) FROM test WHERE (team_id_1=$team_id OR team_id_2=$team_id) AND winner<>$team_id) as losses
         FROM `teams`
         WHERE teams.team_id = $team_id";

[Pawn]

No, no ,no.

 

I love you guys . Seriously, thanks.

 

Clearly I need to learn to query properly!