Ordering a table based on another table.....

[Zepo.]

This is a little confusing so please bear with me.  I was thinking of using left.join but couldn't figure out how to implement it properly.

 

I am trying to order all of the results from a table names 'clans' based on how many points the clan has. To calculate the points you have to go into another table 'clanteams' and then loop every team in the clan pulling wins and losses from a row in a specific ladder table.

 

Here is the code i have to calculate the points.

 

	$total[wins] = 0;
$total[loss] = 0;
$members=mysql_query("SELECT id,clanid,teamid,DATE_FORMAT(datejoined,'%M %d, %Y') FROM clanteams WHERE clanid='$member[id]'");
while(list($id,$clanid,$teamid,$joined)=mysql_fetch_row($members)){
	$team=mysql_query("SELECT name,ladderid FROM teams WHERE id='$teamid'");
	if(mysql_num_rows($team) == 0) continue;
	$team=mysql_fetch_array($team);
	$ladder=mysql_query("SELECT name FROM ladders WHERE id='$team[ladderid]'");
	$ladder=mysql_fetch_array($ladder);
	$linfo=mysql_query("SELECT rank,wins,loss FROM ladder_$team[ladderid] WHERE teamid='$teamid'");
	$linfo=mysql_fetch_array($linfo);

	$total[wins] = $total[wins] + $linfo[wins];		
	$total[loss] = $total[loss] + $linfo[loss];

}
$totalpoints = ($total[wins] * 2) - $total[loss];

 

So now i want to loop through every row in the clans table, and using the above code oder them by $total points.

Ive spent hours wrapping my head around it and still cannot figure it out.

 

Please help.

[Zepo.]

bump

[denno020]

Put another field in your database table where the total points is stored.

Then when you compute the total wins, you could update this new field with the value, then you run another query where you pull the clan name, and use ORDER BY total_points ASC or DESC, depending on how you want it..

 

It's probably not the best way to do it, but I'm sure it would work.

 

Denno

[laffin]

The thing that stops you from having a pure mysql solution, is in this query

"SELECT rank,wins,loss FROM ladder_$team[ladderid] WHERE teamid='$teamid'"

 

instead of normalizing your data, you opted to create new tables.