Count the difference between 2 records

[guestabc]

Hi not sure if this comes under php or mysql help. Anyway what I have is a database holding football league tables. I am selecting a league and 2 teams that are playing each other on a specific weekend. I want to find the difference of their positions in the league i.e football team A are 1st and football team B are 2nd in the league so the difference would be 1. However I recognise this would easily be acheived by adding a position field in the database but I want to programatically find the difference instead of storing the position each time a match is played. Thanks in advance! 

[litebearer]

where do you derive the values (if not from a db)?

[guestabc]

there is a league table in the database that basically have all the teams from the premier league down to league 2. There is then a league details page that hold the league name i.e premier league. I am then selecting all the teams in the premier league for example and then sorting the teams by who has the most points. I then want to find the league position difference between two teams, but because I am not storing the league positions it is sorted by points I am not sure how to implement this. Thanks sorry if my post was not clear.

[guestabc]

is this not possible to do then?

[litebearer]

You say you are "sorting the teams by who has the most points". Are these in an array? Can you show us what you have done thus far as to coding?

[Pikachu2000]

What if two teams are tied in points?

[AMcHarg]

There are quite a few ways of doing it but I don't know why you don't just have a field in the database for 'leaguePosition'?  Every time a match is played you can have a script go through your database and update the league positions.  Doing it this way means you have fewer calculations to do which will improve performance for your users.

 

[guestabc]

I think the idea of having leaguePosition in the database is the best solution. I thought it would just be easier to calculate it on the fly before. I basically have a script where I can upload results for a certain league which then automatically updates the league table. I have posted the code for this below, I don't have a clue where to start with how to find what league position each team should have?

// Save was pressed
for($rows = 0; $rows < $total_rows; $rows++)
{
$row_array = mysql_fetch_array($db_query_result);		

// Update query on table fixture_results
$sql  = 'UPDATE fixture_results ';
$sql .= 'SET ';
$sql .= 'home_score = "'.$_POST['home_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]].'", ';
$sql .= 'away_score = "'.$_POST['away_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]].'" ';
$sql .= 'WHERE fixture_result_id = "'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID].'"';

$result_query_result = mysql_query($sql, $db_connection);


// Update query on table fixture_results
$sql  = 'UPDATE league ';
$sql .= 'SET ';
$sql .= 'played  = played + 1, ';
if($_POST['home_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]] > $_POST['away_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]])
{
	$sql .= 'home_wins  = home_wins + 1, ';
	$sql .= 'home_draws = home_draws + 0, ';
	$sql .= 'home_lost  = home_lost + 0, ';
	$sql .= 'points  = points + 3 ';
}
else if($_POST['home_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]] == $_POST['away_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]])
{
	$sql .= 'home_wins  = home_wins + 0, ';
	$sql .= 'home_draws = home_draws + 1, ';
	$sql .= 'home_lost  = home_lost + 0, ';
	$sql .= 'points  = points + 1 ';
}
else if($_POST['home_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]] < $_POST['away_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]])
{
	$sql .= 'home_wins  = home_wins + 0, ';
	$sql .= 'home_draws = home_draws + 0, ';
	$sql .= 'home_lost  = home_lost + 1, ';
	$sql .= 'points  = points + 0 ';
}
$sql .= 'WHERE league_id = "'.$league_id.'" AND team = "'.$row_array[$FIXTURE_RESULTS__HOME_TEAM_NAME].'" ';

    $result_query_result = mysql_query($sql, $db_connection);


//-----------------------------

// Update query on table fixture_results
$sql  = 'UPDATE league ';
$sql .= 'SET ';
$sql .= 'played  = played + 1, ';
if($_POST['home_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]] > $_POST['away_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]])
{
	$sql .= 'away_wins  = away_wins + 0, ';
	$sql .= 'away_draws = away_draws + 0, ';
	$sql .= 'away_lost  = away_lost + 1, ';
	$sql .= 'points  = points + 0 ';
}
else if($_POST['home_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]] == $_POST['away_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]])
{
	$sql .= 'away_wins  = away_wins + 0, ';
	$sql .= 'away_draws = away_draws + 1, ';
	$sql .= 'away_lost  = away_lost + 0, ';
	$sql .= 'points  = points + 1 ';
}
else if($_POST['home_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]] < $_POST['away_score_'.$row_array[$FIXTURE_RESULTS__FIXTURE_ID]])
{
	$sql .= 'away_wins  = away_wins + 1, ';
	$sql .= 'away_draws = away_draws + 0, ';
	$sql .= 'away_lost  = away_lost + 0, ';
	$sql .= 'points  = points + 3 ';
}
$sql .= 'WHERE league_id = "'.$league_id.'" AND team = "'.$row_array[$FIXTURE_RESULTS__AWAY_TEAM_NAME].'" ';

$result_query_result = mysql_query($sql, $db_connection);
}	

 

Thanks