Division Record column under Standings | Peter's Sports League Standings | Forum
Back to the sports standings script page
Log In
Home
Topic RSS
4:52 pm
Hey,
I was wondering if anyone or Peter can let me know how I can add a column with the Division record for each team. Meaning their record within the division.
Here is my league site:
http://madden.amirsahib.com/
Thanks,
Amir
1:42 am
Offline
I did something similar a few years ago for a custom implementation on an older version. It included some ugly additions to the main SQL calls in standings.php such as:
FROM sportsdb_wins
WHERE winner = teamid
AND (winortie = 1 or winortie = 2) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = loser) = teamdiv
) AS divwins, [... and so on ...]
In that example above, you then have the variable $results['divwins'] available for use in a custom column in your output.
12:13 pm
Peter,
Can you email me a quote of what you would charge to modify the scripts to add Division Record and have the standings sort by Division Record? I would need both Div and overall record. It would be nice to have the second sort as overall record, but not a requirement.
Thank You
Brad
10:09 am
I'm trying to do this as well...
I tried adding the code you gave here, but I get an error in query on the page:
$query = "SELECT COUNT(*) FROM sportsdb_teams WHERE teamdiv = {$divs['divid']}";
$result = mysql_query($query);
$result_exist = mysql_result($result,0);
if ($result_exist) {
$query = "SELECT teamid, teamname, teamwins, teamties, teamlosses, teamforfeits, teamrf, teamra, teamdiv,
(teamwins + teamties + teamlosses + teamforfeits) AS gamesplayed,
(teamwins * $points_win + teamforfeits * $points_forfeit + teamties * $points_tie) AS points,
(SELECT * FROM sportsdb_wins WHERE winner = teamid AND (winortie = 1 or winortie = 2) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = loser) = teamdiv ) AS divwins,
$winning_pct_formula AS winningpct,
(teamwins - teamlosses - teamforfeits) AS leadervalue
FROM sportsdb_teams
WHERE teamdiv = {$divs['divid']}
ORDER BY leadervalue DESC, teamties DESC LIMIT 1";
$result = mysql_query($query) or die ("Error in query: $query");
$results = mysql_fetch_array($result, MYSQL_ASSOC);
$winningpct = number_format($results['winningpct'], 3);
}
// Show the leader at the top for default sorting (games behind)
if ($standings_sort == 9) {
?>
12:10 pm
Offline
Try running the query directly on the database (via phpMyAdmin or something similar) and debugging from there.
10:55 pm
Okay...now I have numbers showing...but they aren't correct...
For instance, a team's actual in-conference is 3-0 but it is displaying 5-5...
if ($result_exist) {
$query="SELECT teamid, teamname, teamwins, teamties, teamlosses, teamforfeits, teamrf, teamra,
(teamwins + teamties + teamlosses + teamforfeits) AS gamesplayed,
(teamwins * $points_win + teamforfeits * $points_forfeit + teamties * $points_tie) AS points,
(teamrf / ({$results['gamesplayed']})) AS teamppg,
(SELECT COUNT(*) FROM sportsdb_wins WHERE winner = teamid AND (winortie = 3 or winortie = 1) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = loser) = teamdiv) AS divwins,
(SELECT COUNT(*) FROM sportsdb_wins WHERE loser = teamid AND (winortie = 1 or winortie = 3) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = winner) = teamdiv) AS divlosses,
(teamrf - teamra) AS teamdiff,
$winning_pct_formula AS winningpct,
((({$results['leadervalue']}) - (teamwins - teamlosses - teamforfeits)) / 2) AS gamesbehind
FROM sportsdb_teams
WHERE teamdiv = {$divs['divid']}";
if ($standings_sort == 9) {
$query .= " AND teamid != {$results['teamid']}";
}
$query .= " AND active = 1" . $division_sort . " ORDER BY $sort_order";
$result=mysql_query($query);
$num=mysql_num_rows($result);
while ($results = mysql_fetch_array($result, MYSQL_ASSOC)) {
$winningpct=number_format($results['winningpct'], 3);
$teamppg=number_format($results['teamppg'], 2);
$gamesbehind = number_format($results['gamesbehind'],1);
if ($gamesbehind == "0.0") {
$gamesbehind = "---";
}
?>
4:53 pm
I've also tried using this code: Same thing, wrong numbers, an actual record is 3-0 but it is showing 3-4. Also, forgot to mention, using either of these codes, only one of the conferences show something besides zeros...
Example, I have four conferences set up: Boys SCC, Girls SCC, Boys Bluegrass and Girls Bluegrass. The Boys SCC shows numbers (but the wrong ones) and the other three all show 0-0
<?php }
if ($result_exist) {
$query="SELECT teamid, teamname, teamwins, teamties, teamlosses, teamforfeits, teamrf, teamra,
(teamwins + teamties + teamlosses + teamforfeits) AS gamesplayed,
(teamwins * $points_win + teamforfeits * $points_forfeit + teamties * $points_tie) AS points,
(teamrf / ({$results['gamesplayed']})) AS teamppg,
(SELECT COUNT(*) FROM sportsdb_wins WHERE winner = teamid AND (winconf = {$divs['divid']}) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = loser) = teamdiv - 2) AS divwins,
(SELECT COUNT(*) FROM sportsdb_wins WHERE loser = teamid AND (winconf = {$divs['divid']}) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = winner) = teamdiv -2) AS divlosses,
(teamrf - teamra) AS teamdiff,
$winning_pct_formula AS winningpct,
((({$results['leadervalue']}) - (teamwins - teamlosses - teamforfeits)) / 2) AS gamesbehind
FROM sportsdb_teams
WHERE teamdiv = {$divs['divid']}";
8:41 pm
Offline
The original intent of the code is to return a division wins and losses. Are you trying to track conference wins and losses instead? (If that's the case, unfortunately I don't currently have time to write up some code for conference records -- you'll have to join in another table since conference associations are stored in the divs table -- but hopefully that prevents you from going down the wrong route.)
12:24 pm
Right now, I have two conferences set up, Boys and Girls. Inside both of those, I have two divisions: South Central and Bluegrass.
So, for instance, I want a column that will display what each team's record is against other teams in their divisions...
So, South Central Team A beat South Central Team B, therefore South Central Team A's division record should be 1-0, and South Central Team B's record should be 0-1.
....
I'm using the standings software a bit differently...using conferences to separate Boys and Girls and using the divisions as conferences (maybe a bit confusing...).
Thanks!
3:52 pm
Okay, I'm staring all over with this because it appears that I don't have it organized quite the right way. However, I'm getting similar results as before...
What I have now is four seasons: 2010-11 Boys SCC, 2010-11 Girls SCC, 2010-11 Boys Bluegrass, and 2010-11 Girls Bluegrass.
Using the 2010-11 Boys SCC season as the example from here on out:
I have one conference set up: South Central Conference
Under that, I have on division (where all of the teams are stored as the conference does not have divisions).
If you need a link to this, let me know and I will message you one...
There are six teams. Here is what is actually being displayed using this code:
(SELECT COUNT(*) FROM sportsdb_wins WHERE loser = teamid AND (winortie = 1 or winortie = 3) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = winner) = teamdiv) AS divlosses,
I only have information entered for the top two at the moment...
Team A 4-1
Team B 3-1
What SHOULD be displayed:
Team A 5-1
Team B 5-1
I would be willing to pay a little for your time. I'm sure it would take someone well versed in this script and PHP very long to crank out code to do this.
1:24 pm
Offline
Unfortunately I don't have time for more free or paid work right now. Hopefully someone else familiar with MySQL and/or PHP can help.
A quick note, though: winortie value of 3 = play; 1 = beat; 2 = beat by forfeit; 0 = tied; 4 = lost; 5 = lost by forfeit. So it looks like the on-the-fly query is quite complicated: you have to check for winortie of 1 or 2 when the team ID is in the winner (aka Home) field, as well as winortie of 4 or 5 when the team ID is in the loser (aka Away) field, and then match up the other team in that game to the original team's division, just to get divwins.
I can't spend any more time on this right now. Good luck!
10:27 am
I'll just do this by hand. Your script is SO close to what I need, but I need this feature. If you ever have time, make a post or email me.
It's too bad you don't have time to support this script, but I understand. I would imagine you have paid projects, those should always be first priority. Maybe let someone pick this up to further develop/support it?
:cry:
7:20 pm
peter we need support for this script
cause you who made this script and yoy know every things about it
so please do not close the door
10:05 am
I just want to say thanks Peter for this code. It is a very useful start to those who need a standings/schedule script. I used it for a site that tracks high school standings. http://www.vermontscoreboard.c.....gs/?conf=1
Also I have to say it cracks me up how no matter how many times you say you do not actively work on the script anymore, people post on here asking you a million questions... half of which are so obvious if you just take a couple hours to examine the code. I am by no means a php or database expert, but if you take some time to look at the code, you can figure out what to adjust or copy/paste to make it work for you.
There are other solutions out there that offer similar results. Yes, they cost money. Guilt tripping Peter into updating the code because you do not want to pay for someone to code it for you is just disrespectful. I suggest people be grateful to have this much code out there for free... I am. Would you rather Peter just delete all the code?
8:26 pm
Trey said:
I just want to say thanks Peter for this code. It is a very useful start to those who need a standings/schedule script. I used it for a site that tracks high school standings. http://www.vermontscoreboard.c.....gs/?conf=1
Also I have to say it cracks me up how no matter how many times you say you do not actively work on the script anymore, people post on here asking you a million questions... half of which are so obvious if you just take a couple hours to examine the code. I am by no means a php or database expert, but if you take some time to look at the code, you can figure out what to adjust or copy/paste to make it work for you.
There are other solutions out there that offer similar results. Yes, they cost money. Guilt tripping Peter into updating the code because you do not want to pay for someone to code it for you is just disrespectful. I suggest people be grateful to have this much code out there for free... I am. Would you rather Peter just delete all the code?
No Trey...and I would pay for a script if I could find one to fit what I need. I would pay Peter to code it to do what I need it to do.
I've been tearing this script up for quite a while, trying multiple codes and methods, I can't get it done. If you'd like to help me, you're more than welcome. I took Peter's code in this forum post, tried it and it didn't work.
Then I tried tearing it apart and customizing it for days, it still didn't work right.
I think this is a wonderful script, but if it doesn't track conference win/loss too, it's useless to me. I'd pay a professional designer to give me a complete custom script, but I'm sorry, I don't make seven figures and can't afford that. Which is why I appreciate Peter's script so much.
