mysql help [required]

Kinneh

i am currently trying to make a clan webpage and atm i'm attempting to do a stats page of How many games played, how many games won, drawn, lost etc.

Now i found some inspiration from a wikid clan site www.body-count.org and on the left hand side there is a stats section where they have Highest Win. Now i've tried coding this but nothing works, so i must be doing it completely wrong. Any help on how to make this work so it will show the Highest victory would be great thnx.

My second problem, also inspired by body-count is that they have a streaks section. It shows there max streak of wins, max streak of loses and their current win streak. Again i dont know where to start so any help on that also would be great.

I need to know how to do this in php + mysql.

DB Info:

table name for results is "results".
i have a field called "outcome" where either Win, Draw or Loss is inserted whether the clan won, drew or lost.
i have a 2 fields for the score i.e. our_score and there_score.

hopefully thats it, so any help would be greatly appreciated.

Weedpacket

Well, I presume that for every win in the table, there is also a loss ... basically, a fair whack of the data in the table is duplicated. So I can get away with just looking at our_score, because all the scores in their_score will be the same (only in a different order).

SELECT interesting fields FROM results WHERE our_score=MAX(our_score)

will return the interesting fields for all the teams whose score was the highest. If "Highest Win" is relative to the score of the other team:

SELECT interesting fields FROM results WHERE our_score-their_score = MAX(our_score-their_score)

I think.

As for your streaks and such, those are properties of the teams, not the games (games don't have winning streaks, teams do). What you could do is stick on some fields in the teams table (whatever you call it); "current_winning_streak" and "current_losing_streak". Set them to zero.
If a team wins a game, bump their current_winning_streak up by one, and set their current_losing_streak to zero. Vice versa in the event of a loss, and I guess you'd want to zero both of them if they draw.

But you want to retain the longest winning/losing streak of each team. That's two more fields: longest_winning_streak, longest_losing_streak. Whenever you bump current_winning_streak, look to see if it's gone higher than longest_winning_streak. If it has, then this is their longest winning streak, so update longest_winning_streak. Similar for longest_losing_streak.

To find the team with the longest winning streak, SELECT team, longest_winning_streak WHERE longest_winning_streak=MAX(longest_winning_streak)

Kinneh

i'm using this statement

$sql = mysql_query("SELECT ourscore, id, opponent, therescore FROM results WHERE ourscore=MAX(ourscore)") or die (mysql_error());

like you said, but its not working, its giving me this error: Invalid use of group function. Any ideas?

BTW all field names are correct

Weedpacket

Funny, could have sworn .... oh well, it's not like I tested it at all.

If this weren't MySQL the simplest answer would have been:

SELECT ourscore, id, opponent, theirscore FROM results WHERE ourscore IN (SELECT MAX(ourscore) FROM results)

In MySQL (pre v4.1) it could be done in two steps: first, get the maximum score, and then get the teams with that score. Or it could be done as

SELECT ourscore, id, opponent, theirscore FROM results ORDER BY ourscore DESC LIMIT 1

The problem with that second solution is (a) it's hardly an efficient way of going about it (sorting the entire table just to see what's on top), and (b) won't help if more than one team has tied for the lead.

(There's a section in the MySQL tutorial on this subject - Section 3.5.2 in the documentation. There's also a mention of it in subsection 1.7.4.1)