PHP SQL - Golf leader board

Author

Golf leader board

MannyLS

2004-08-18, 3:57 pm

Hi,

I have a Web site for online golf (free, non-profit and
international). I've been using a program written by someone in
PERL that allow players to report their scores and see the leader
board updated instantly.
It also show stats and its very

. However, it's not very
friendly to use and is not MySql based. So, I decided to write my own
program starting from scratch using PHP-MySql. I did pretty good up
to now but I'm stuck on the leader board.
I want the leader board to show the players who completed the most
rounds in first place in ascending order according to their total
score.

This is the MySql table:

CREATE TABLE `results` (
`player` int(4) NOT NULL default '0',
`name` varchar(150) NOT NULL default '',
`country` varchar(150) NOT NULL default '',
`round1` char(3) default NULL,
`round2` char(3) default NULL,
`round3` char(3) default NULL,
`round4` char(3) default NULL,
`password` varchar(25) default NULL,
PRIMARY KEY (`player`),
KEY `name` (`name`)
) TYPE=MyISAM;

Below is the sql command:

[SQL]"SELECT results.player, results.name, results.country,
results.round1,
results.round2, results.round3, results.round4,
SUM(results.round1+results.round2+results.round3+results.round4)
AS `Total Score` FROM results WHERE results.round1>0 GROUP BY
`name` ORDER BY `Total Score` ASC";[/SQL]

Here's what I get:

>
> ----------------------------------------------
>
Position**Player**Name*Country**Score**R
ound1**Round2**Round3**Round4**Total
Score
>
1_________71___John____GBR_____- 16_____56______________________________5
6

>
2_________100___Joe____USA_____- 7______65______________________________6
5
>
3_________169___Peter__DEN_____- 31_____58______55______________________1
13
>
4_________12___Manuel__USA_____- 22_____56______66______________________1
22
>
5_________26___Sergio__USA_____- 49_____53______56______58______________1
67
>
6_________111__Ben____CAN_____- 40_____55______65______56______________1
76
>
7_________28___Paul____USA_____- 55_____60______68______55______50______2
33
>
8_________29___Jack____USA_____- 48_____56______65______56______63______2
40
> #-----------------------------------------------
>
>
"Score", "Position" and "Total Score"
are variables. As you can see, the ranks are not in order. In fact,
players that have completed round 4 should be on top in ascending
order. Then, these should be followed by players who completed round
3, round 2 and round 1.

This is how it should appear:

>
> ----------------------------------------------
>
Position**Player**Name*Country**Score**R
ound1**Round2**Round3**Round4**Total
Score
>
1_________28___Paul___USA_____- 55_____60______68______55______50______2
33
>
2_________29___Jack___USA_____- 48_____56______65______56______63______2
40
>
3_________26___Sergio_USA_____- 49_____53______56______58______________1
67
>
4_________111__Ben___CAN_____- 40_____55______65______56______________1
76
>
5_________169__Peter__DEN_____- 31_____58______55______________________1
13
>
6_________12___Manuel_USA_____- 22_____56______66______________________1
22
>
7_________71___John___GBR_____- 16_____56______________________________5
6

>
8_________100__Joe____USA_____- 7______65______________________________6
5
>
> #-----------------------------------------------
>
I saw someone who did something similar in ASP but he only shows the
leader board round by round. I guess he also found the same problem.
I would like to show all players in the leader board that have
completed round 1 at least (Therefore: WHERE round1>0 ) but in the
order above.

Could anyone help me with this?

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

Barand

2004-08-18, 3:57 pm

Create a dummy field to order by which would be (288 - total of the
scores in each round). Then if they did not complete a round they
effectively get a penalty score of 72.

"SELECT results.player, results.name, results.country,
results.round1,
results.round2, results.round3, results.round4,
(results.round1+results.round2+results.round3+results.round4)
AS `Total Score`, (288 - `Total Score`) as `hashtotal` FROM results
WHERE results.round1>0 GROUP BY `name` ORDER BY `hashtotal`
ASC";

NB Use SUM when you are aggregating with a GROUP BY clause, not here.

hth

Barand

http://members.aol.com/barryaandrew...agridguide.html easy
data tables - and more

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

Barand

2004-08-18, 3:57 pm

Disregard last post, you may as well just order by tot score DESC,
which would be closer but wouldn't give the order within each
round.

BTW, should 3 and 4 be reversed?

I think the only way to do this one is to read the data into an array
and write a custom sort function. Then output from the sorted array.

Barand

http://members.aol.com/barryaandrew...agridguide.html easy
data tables - and more

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

MannyLS

2004-08-18, 3:57 pm

Hi Barand,

Thanks for your reply. :)

In fact, I've been trying all kinds of options with MySql (I'm
not an expert but I do have a hard head :D ) Finally, I decided to
try 4 queries in the same page:

--------
mysql_select_db(database_mannylscom, mannylscom);
query_lboardrs = "SELECT `player` , `name` , `country` ,
`round1` , `round2` , `round3` , `round4` , SUM( round1 + round2
+ round3 + round4 ) AS `Total Score` FROM `results` WHERE round4
> 0 GROUP BY `name` ORDER BY `Total Score` ASC ";
lboardrs = mysql_query(query_lboardrs,
mannylscom) or die(mysql_error());
row_lboardrs = mysql_fetch_assoc(lboardrs);
totalRows_lboardrs = mysql_num_rows(lboardrs);
--------

--------

mysql_select_db(database_mannylscom, mannylscom);
query_lboardrs = "SELECT `player` , `name` , `country` ,
`round1` , `round2` , `round3` , `round4` , SUM( round1 + round2
+ round3 + round4 ) AS `Total Score`
FROM `results`
WHERE round3 > 1 AND round4=0
GROUP BY `name`
ORDER BY `Total Score` ASC ";
lboardrs = mysql_query(query_lboardrs,
mannylscom) or die(mysql_error());
row_lboardrs = mysql_fetch_assoc(lboardrs);
totalRows_lboardrs = mysql_num_rows(lboardrs);
--------

--------
mysql_select_db(database_mannylscom, mannylscom);
query_lboardrs = "SELECT `player` , `name` , `country` ,
`round1` , `round2` , `round3` , `round4` , SUM( round1 + round2
+ round3 + round4 ) AS `Total Score`
FROM `results`
WHERE round2 > 1 AND round4=0 AND
round3=0
GROUP BY `name`
ORDER BY `Total Score` ASC ";
lboardrs = mysql_query(query_lboardrs,
mannylscom) or die(mysql_error());
row_lboardrs = mysql_fetch_assoc(lboardrs);
totalRows_lboardrs = mysql_num_rows(lboardrs);
--------

--------
mysql_select_db(database_mannylscom, mannylscom);
query_lboardrs = "SELECT `player` , `name` , `country`
, `round1` , `round2` , `round3` , `round4` , SUM( round1 +
round2 + round3 + round4 ) AS `Total Score`
FROM `results`
WHERE round1 > 1 AND round4=0
AND round3=0 AND round2=0
GROUP BY `name`
ORDER BY `Total Score` ASC ";
lboardrs = mysql_query(query_lboardrs,
mannylscom) or die(mysql_error());
row_lboardrs = mysql_fetch_assoc(lboardrs);
totalRows_lboardrs = mysql_num_rows(lboardrs);
--------

This is what I got:

1 28 Jim USA -55 60 68 55 50 233
2 29 Brad USA -48 56 65 56 63 240
3 1 Manny CAN -43 70 65 55 55 245
4 26 Gary USA -49 53 56 58 0 167
5 169 Søren DEN -31 58 55 0 0 113
6 71 Derek GBR -16 56 0 0 0 56

Which is good but I'm missing a few rows with other scores and I
don't know why. <_<

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

Barand

2004-08-18, 3:57 pm

I think this is what you want

--------

function resultSort(a, b) {
if
(a['r4']) {
if
(b['r4']) {
if
(a['r4'] ==
b['r4']) return 0;
return
a['r4'] < b['r4'] ?
-1 : 1;
}
else return -1;
}
else if
(b['r4']) return 1;

if
(a['r3']) {
if
(b['r3']) {
if
(a['r3'] ==
b['r3']) return 0;
return
a['r3'] < b['r3'] ?
-1 : 1;
}
else return -1;
}
else if
(b['r3']) return 1;

if
(a['r2']) {
if
(b['r2']) {
if
(a['r2'] ==
b['r2']) return 0;
return
a['r2'] < b['r2'] ?
-1 : 1;
}
else return -1;
}
else if
(b['r2']) return 1;

if (a['r1'] ==
b['r1']) return 0;
return a['r1'] <
b['r1'] ? -1 : 1;

}

sql = "SELECT name, country, round1, round2, round3, round4
FROM results WHERE round1 > 0";
res = mysql_query(sql);
while (list(pl, co, r1, r2, r3,
r4) = mysql_fetch_row(res)) {

results[pl]['country'] =
co;

results[pl]['r1'] = r1;

results[pl]['r2'] = r2;

results[pl]['r3'] = r3;

results[pl]['r4'] = r4;

results[pl]['total'] = r1 +
r2 + r3 + r4;

}

uasort(results, 'resultSort');

echo '<pre>';
foreach (results as name => data) {
printf ('%-12s %-6s %6d %6d %6d
%6d %6d<br>',
name,
data['country'],
data['r1'], data['r2'],
data['r3'], data['r4'],
data['total']);
}
echo '</pre>';
--------

hth

Barand

http://members.aol.com/barryaandrew...agridguide.html easy
data tables - and more

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

Sponsored Links