my queries are too big, having trouble with order by

[Greysoul]

because i'm new, i find odd ways to get around what i need, even if its messy and weird. i'm trying to understand if its possible to reduce the amount of code i have here, or if this is it. because the way my tables are setup, and i'm reducing the need to have to use more than i need to, i some times have real difficult issues getting things to work. here i'm trying to simply show wins, losses, ties, and total points for each clan in a little side bar. kind of like a standings box for every clan. my main problem is, how do i order by $points? secondly..is there a way to reduce all this code down to a single query? lol

echo "W-L-T,P" . "<br>";$clanid=mysql_query("SELECT * FROM Clans");while($cid=mysql_fetch_array($clanid)){$clan=$cid['ClanName'];$W=mysql_query("SELECT *, Count(Winner) AS w FROM Schedule WHERE Winner='$clan'") or die(mysql_error());while($wins = mysql_fetch_array($W)){$won=$wins['w'];}$L=mysql_query("SELECT *, Count(Winner) AS l FROM Schedule WHERE winner!='$clan' AND winner!='Tie' AND winner!='' AND (Green='$clan' OR Red='$clan')") or die(mysql_error());while($loss= mysql_fetch_array($L)){$lost=$loss['l'];}$T=mysql_query("SELECT *, Count(Winner) AS t FROM Schedule WHERE winner='Tie' AND (Green='$clan' OR Red='$clan')") or die(mysql_error());while($tie= mysql_fetch_array($T)){$ties=$tie['t'];}$Points = ($won * 2) + $ties;echo $won . "-" . $lost . "-" . $ties . "," . $Points . " " . '<a href=o.php?c=' . $clan . '>' . $clan . '</a>' . "<br>";}

[ShadowMage]

I think you can probably get away with two queries and use conditionals to add up the wins/losses/ties for each clan.You will still have the first query that pulls all the clans, but your other queries can be combined and moved out of the loop. You also won't need the count() function either. You should end up with something like this:

echo "W-L-T,P" . "<br>";$clanid=mysql_query("SELECT * FROM Clans");$sched=mysql_query("SELECT * FROM Schedule");while($cid=mysql_fetch_array($clanid)){$clan=$cid['ClanName'];$win=0;$loss=0;$tie=0;//...Loop through $sched$Points = ($won * 2) + $ties;echo $won . "-" . $lost . "-" . $ties . "," . $Points . " " . '<a href=o.php?c=' . $clan . '>' . $clan . '</a>' . "<br>";}

Then where it says "Loop through $sched" you'll need to loop through the rows in $sched and test each schedule to see if it was a win, loss, or tie for the current clan and increment the appropriate variable.