PHP/MySQL help request

[Dutch]

I need some help and am hoping some of the seasoned PHP/MySQL vets can help me out here. Forgive the look of the code, I'm just a hobbyist here.

My current project is to determine "Games Back" in a standings (PHP) page. I've been stuck on this for a while now. Although the following code is broken, it has all the moving parts that I think I need for the GB column of a standings page.

I've got two SELECT statements, so my first question is how do I handle that? Can (and should) I nest the queries? Should I set the @wl_diff in a different PHP variable? (the book suggests I shouldn't do that). I don't know what to do next.

$sql11 = 'SELECT @wl_diff:=MAX((sum(Score>OppScore))-(sum(OppScore>Score))) FROM fof_teamschedule';
. 'SELECT'
. ' fof_teams.ID,'
. ' fof_teams.CityName,'
. ' fof_teams.Nickname,'
. ' fof_teams.Conference,'
. ' fof_teams.Division,'
. ' TeamID,'
. ' sum(Score>OppScore) AS Wins,'
. ' sum(OppScore>Score) AS Losses,'
. ' sum(Score=OppScore) AS Ties,'
. ' sum(Score>OppScore)/(sum(Score>OppScore)/sum Score>OppScore) AS PCT,'
. ' (@wl_diff - (sum(Score>OppScore)-sum(Score>OppScore))) / 2 AS GB,'
. ' sum(Score),'
. ' sum(OppScore)'
. ' FROM fof_teamschedule, fof_teams'
. ' WHERE fof_teams.ID = TeamID AND Conference=1 AND Division=1 AND Year=2024 AND Week>5 AND Week<23 '
. ' GROUP BY TeamID'
. ' ORDER BY GB DESC, TeamID DESC';

Any and all suggestions and complaints about my code will be accepted as gifts. Thanks.

[Dutch]

To clarify, the @wl_diff variable is designed to identify the first place team in a divsion and then all subsequent teams in that division will determine their GB status against the 1st place team's current record.

[Lonnie]

Are you subtracting your wins twice here?

(@wl_diff - (sum(Score>OppScore)-sum(Score>OppScore))) / 2 AS GB

Test scenario, 1st place team is 12-2, your team is 10-4.

@wl_diff = 10

(10 - 10 - 10)/2 = -5

Formula should be ((TopWins - MyWins) + (MyLosses - TopLosses)) / 2

((12 - 10) + (4-2)) / 2 = (2+2)/2 = 2

Currently your formula @wl_diff finds the TopWins, then you subtract your wins from it twice and then divide by two.

(TopWins - (MyWins - MyWins))/2 = WRONG

So it's not your select, it's your formula. You'll need another select to get TopLosses.

[Dutch]

Yeah, the formula is a work in progress (and it definately needs work). I was hoping to get something (anything) to display in the GB column and then tweak the formula to get the correct data.

Before I went out tonight I made one change. I moved the first SELECT to it's own row like this...

'SELECT @wl_diff:=MAX((sum(Score>OppScore))-(sum(OppScore>Score))) FROM fof_teamschedule';

And then I got an error "Unknown column 'Score' in 'field list'" which gives me hope since now I'm getting some real feedback from the PHP page. After I work out the kinks, I still don't know if I can now use @wl_diff in a new SELECT statement.

I do appreciate the feedback.

EDIT: And looking at the 'Score' the first glaring problem is I haven't identified the right table. I'm not sure if I want to use fof_teamschedule or fof_gameresults. I'll have to think about that one.