PrestoGP.com - the results and league table

Nicolai Nicholson · Sep 16, 2014

I'll use this thread to post some of the programming (php) that I think is relevant to the creation of the league table.

Nicolai Nicholson · Sep 16, 2014

standings.php

<?php
include("dbconnect.php");

$league = $_GET['league'];
$season = $_GET['season'];

$sql="SELECT DISTINCT RACENUMBER FROM r07leaguerace WHERE leaguenumber = '".$league."' and seasonnumber = '".$season."'";
$result = mysql_query($sql);
$racecount = mysql_num_rows($result);

$sqltotal="SELECT r07leagueracedrivers.driver, SUM(r07leagueracedrivers.points) as totalpoints FROM r07leagueracedrivers, r07leaguerace WHERE r07leagueracedrivers.leagueraceid = r07leaguerace.id and r07leaguerace.leaguenumber = '".$league."' and r07leaguerace.seasonnumber = '".$season."' group by r07leagueracedrivers.driver order by totalpoints desc";
$resulttotal = mysql_query($sqltotal);
$drivercount = mysql_num_rows($resulttotal);

if ($league == 1) $leaguename = "division 1";
else if ($league == 3) $leaguename = "division 2";
else if ($league == 4) $leaguename = "pacific league";
?>

<table width="100%" border="0" cellspacing="0" cellpadding="6" style="background-color: #FBFBFB; border: 1px solid #CCCCCC;" class="text1">
<tr>
<td>
<strong>Presto GP League season <?php echo $season." ".$leaguename; ?></strong><br />
<br />

Click on Race1, Race2 etc to see detailed results. <br /><br />

<table width="400" border="0" cellspacing="0" cellpadding="2" class="results">
<tr height="20">

<td height="20" colspan="2"><strong>Points</strong></td>
<td height="20" colspan="2" align="center" valign="top"> </td>
<td height="20" colspan="2" align="center" valign="top"> </td>
</tr>
<tr height="20">
<td height="20"></td>
<td><u>Driver</u></td>

<?php
for ($i=1; $i<=$racecount; $i++) {
echo "<td align='right'><u><a href='http://prestogp.com/index.php?view=leagueraceresult&season=".$season."&league=".$league."&racenumber=".$i."'>Race".$i."</a></u></td>";
}

?>

<td align="right"><u>TOTAL</u></td>
</tr>

<?php
$placecount = 1;

while($row = mysql_fetch_array($resulttotal)) {

$sqlraces="SELECT r07leagueracedrivers.driver, r07leaguerace.racenumber, r07leagueracedrivers.points FROM r07leagueracedrivers, r07leaguerace WHERE r07leagueracedrivers.leagueraceid = r07leaguerace.id and r07leaguerace.leaguenumber = '".$league."' and r07leaguerace.seasonnumber = '".$season."' and r07leaguerace.racetype = '1' and r07leagueracedrivers.driver = '".$row['driver']."' order by r07leaguerace.racenumber asc";
$resultraces = mysql_query($sqlraces);
$row2 = mysql_fetch_array($resultraces);

echo "<tr height='20'><td height='20'>";
if ($row['totalpoints'] == $lastscore and $row['totalpoints'] != 0) echo ($placecount-1).".";
else if ($row['totalpoints'] == 0) echo "-";
else echo $placecount.".";
echo "</td>";
echo "<td height='20'>".$row['driver']."</td>";

$resultcount = 0;
for ($i=0; $i<$racecount; $i++) {
echo "<td align='right'>";

if ($row2['racenumber'] == $i+1) {
echo $row2['points'];
$row2 = mysql_fetch_array($resultraces);
// $resultcount++;
}
else {
echo "-";
// mysql_data_seek($resultraces, $resultcount-1);
}
echo "</td>";
}
echo "<td align='right'>".$row['totalpoints']."</td></tr>";
$placecount++;
$lastscore = $row['totalpoints'];

}
?>

</table>
</table>

Nicolai Nicholson · Sep 16, 2014

leaguerace.php

<?php
/*session_start();
$newip = $_SERVER['REMOTE_ADDR'];
if (!isset($_SESSION['username']) || empty($_SESSION['username']) || $newip!= $_SESSION['ip']) {
header("location:index.php");
}
*/
$qualid = $_GET['qualid'];
$race1id = $_GET['race1id'];
$race2id = $_GET['race2id'];
//$qualid = "122047040710531";
//$race1id = "122047343715278";
//$race2id = "121684381630159";

include("dbconnect.php");

$sql="SELECT * FROM r07driverevent WHERE race_id = '$qualid' order by bestlap";
?>




<table width="100%" border="0" cellspacing="0" cellpadding="6" style="background-color: #FBFBFB; border: 1px solid #CCCCCC;" class="text1">
<tr>
<td width="26%" align="left" valign="bottom"><strong>Race results</strong><br />
<br />
<table width="288" cellpadding="2" cellspacing="0" class="results">

<tr height="20">
<td colspan="3" height="20"><strong>Qualifying</strong></td>
</tr>
<tr height="20">
<td width="24" height="20"></td>
<td width="192"><u>Driver</u></td>
<td width="58"><u>Qual time</u></td>
</tr>
<?php
$sql="SELECT * FROM r07driverevent WHERE race_id = '$qualid' order by bestlap";
$result=mysql_query($sql);
$place = 1;
while($row = mysql_fetch_array($result)) {
echo " <tr height='20'>
<td height='20'>$place.</td>
<td>$row[Driver_ID]</td>
<td>$row[BestLap]</td>
</tr>
";
$place++;
}
?>


</table></td>
<td width="74%" align="left" valign="bottom">
<table width="62%" cellpadding="2" cellspacing="0" class="results">

<tr height="20">
<td colspan="5" height="20"><strong>Race 1</strong></td>
</tr>

<tr height="20">
<td height="20"></td>

<td><u>Driver</u></td>
<td><u>Best lap</u></td>
<td><u>Race time</u></td>
<td><u>Points</u></td>
</tr>
<?php
$sql="SELECT * FROM r07driverevent WHERE race_id = '$race1id' order by place";
$result=mysql_query($sql);
$place = 1;
$points = 50;
$period = ".";
while($row = mysql_fetch_array($result)) {
if ($place == "1") $points = 50;
else if ($place == "2") $points = 43;
else if ($place == "3") $points = 38;
else if ($place == "4") $points = 34;
else if ($place == "5") $points = 31;
else if ($place == "6") $points = 28;
else if ($place == "7") $points = 25;
else if ($place == "8") $points = 22;
else if ($place == "9") $points = 20;
else if ($place == "10") $points = 18;
else if ($place == "11") $points = 16;
else if ($place == "12") $points = 14;
else if ($place == "13") $points = 13;
else if ($place == "14") $points = 12;
else if ($place == "15") $points = 11;
else if ($place == "16") $points = 10;
else if ($place == "17") $points = 9;
else if ($place == "18") $points = 8;
else if ($place == "19") $points = 7;
else if ($place == "20") $points = 6;
else if ($place == "21") $points = 5;
else if ($place == "22") $points = 4;
else if ($place == "23") $points = 3;
else if ($place == "24") $points = 2;
else if ($place == "25") $points = 1;
else if ($place > "25") $points = "";

if ($row[RaceTime] == "DNF" || $row[RaceTime] == "DQ") {
$place = "";
$period = "";
$points = "";
}
echo " <tr height='20'>
<td height='20'>".$place.$period."</td>
<td>$row[Driver_ID]</td>
<td>$row[BestLap]</td>
<td>$row[RaceTime]</td>
<td align='right'>$points</td>
</tr>
";
$place++;
}
?>
</table></td>


<?php
if ($race2id == "") echo "";

if ($race2id != "") {

$sql="SELECT * FROM r07driverevent WHERE race_id = '$race2id' order by place";
$result=mysql_query($sql);
$place = 1;
$points = 15;
$period = ".";
while($row = mysql_fetch_array($result)) {
if ($place == "1") $points = 15;
else if ($place == "2") $points = 12;
else if ($place == "3") $points = 10;
else if ($place == "4") $points = 8;
else if ($place == "5") $points = 6;
else if ($place == "6") $points = 5;
else if ($place == "7") $points = 4;
else if ($place == "8") $points = 3;
else if ($place == "9") $points = 2;
else if ($place == "10") $points = 1;
else if ($place > "10") $points = "";

if ($row[RaceTime] == "DNF" || $row[RaceTime] == "DQ") {
$place = "";
$period = "";
$points = "";
}
echo " <tr height='20'>
<td height='20'>".$place.$period."</td>
<td>$row[Driver_ID]</td>
<td>$row[BestLap]</td>
<td>$row[RaceTime]</td>
<td align='right'>$points</td>
</tr>
";
$place++;
}
}
?>

</table>

<form method="get" action="addleaguerace.php">

League (affects points system): <input type="radio" id="leaguenumber" name="leaguenumber" value="1">Div 1
<input type="radio" id="leaguenumber" name="leaguenumber" value="3">Div 2<br>
<input type="radio" id="leaguenumber" name="leaguenumber" value="4">Pacific<br>
Season number: <input type="text" id="seasonnumber" name="seasonnumber" value="5"><br>
Race number: <input type="text" id="racenumber" name="racenumber"><br>
Track name: <input type="text" id="trackname" name="trackname"><br>

<input type="hidden" name="qualid" value="<?php echo $qualid;?>">
<input type="hidden" name="race1id" value="<?php echo $race1id;?>">
<input type="hidden" name="race2id" value="<?php echo $race2id;?>">
<br>
<input type="submit" value="Save to DB">

</form>
</td></tr>



</table>

Reik Major · Sep 16, 2014

Ok, took me nearly 1 hour to read through it sign by sign. Now I feel sick.

!aet fo puc ym ton s'tI :ylsuoires woN

Kennett Ylitalo · Sep 16, 2014

SQL is not my cup of tea either, nor PHP (specially uncommented..

) but if i understood correctly, you have $resulttotal and it has the whole results row added up (sum array or similar..) making my suggestion.. let's say problematic.. But when you do have to sum an array, why not sum the array, makes sense. Next suggestion is to search for the lowest score and deduct it from resulttotal. Is there some sort of "array min" function in PHP? well, google... http://php.net/manual/en/function.min.php

But i'll better let PHP experts sort this out, i can only do pseudo code and some C#/C++/Java with plenty of googletutor..

Peter Decker · Sep 17, 2014

I will try to sort it out - some PHP/MySQL sites have been coded here

But my schedule is filled due to this 100% study-speed Im into. So let me get some time

Nicolai Nicholson · Sep 17, 2014

Peter Decker said:
I will try to sort it out - some PHP/MySQL sites have been coded here But my schedule is filled due to this 100% study-speed Im into. So let me get some time

There might be other "pages" involved, these are just two that looked relevant to an untrained eye. But I assume you'll know when you start deciphering the php and let me know

Peter Decker · Sep 19, 2014

Ok, I just skipped a 2h lecture about functional programming in Haskell (not much of my thing, want to go for industrial management anyway and this wont come on exam, so what do you do - you go to library and study PrestoGP php-code!).

The code could be better commented so rusty Peter could understand better. But I'm on it. It would be good to check the database tables. Is there some way I can just look at those in phpmyadmin or something?

David Turnbull · Sep 19, 2014

i cant help here, hopefully nico can give you the info you need when hes around, thanks for looking into this

Nicolai Nicholson · Sep 19, 2014

Peter Decker said:
It would be good to check the database tables. Is there some way I can just look at those in phpmyadmin or something?

I do not know of an easy way to do that. The way I access it is through my workplace where I also get access to all our customers websites etc. But I'll see what I can do. Maybe I can download some of the relevant tables?

Peter Decker · Sep 19, 2014

You mean it would not be ok if I accessed the customers' sites?

If I just could get an overlook of the tables, it would help. These tables would be good:
r07leaguerace
r07leagueracedrivers
r07driverevent

Nicolai Nicholson · Sep 20, 2014

I hope these will be helpful. All three screen shots are towards the end of their respective tables. The first (r07leaguerace) by chance include all the races (and quals) in season 12. The second (r07leagueracedrivers) is from last race of season 12 (except top row which is from second last race). The third (r07driverevent) is very recent server activity.

Peter Decker · Sep 20, 2014

Thanks! I think I know at least one way of doing this

PM will come.

Kennett Ylitalo · Sep 20, 2014

Peter Decker said:
Thanks! I think I know at least one way of doing this PM will come.

When you get this sorted, let me know what method you used. Just out of curiosity, i like these kinds of puzzles.

Peter Decker · Sep 21, 2014

In standings.php, the driver ordering is made by this...
"
$sqltotal="SELECT r07leagueracedrivers.driver, SUM(r07leagueracedrivers.points) as totalpoints FROM r07leagueracedrivers, r07leaguerace WHERE r07leagueracedrivers.leagueraceid = r07leaguerace.id and r07leaguerace.leaguenumber = '".$league."' and r07leaguerace.seasonnumber = '".$season."' group by r07leagueracedrivers.driver order by totalpoints desc";
"
...which fetches all drivers from first to last order based on total points. Problem is to find and cut off the worst points here and then rearrange the driver order.

Since I've forgot about the more advanced SQL-functions (that school-class is coming up this spring

), I will probably fetch the data similar to this and then - with php instead of SQL - find every driver's worst point (by some super algorithm with Ordo(nd) where n=#races and d=#drivers so total amount of operations will be ok (not that we are like thousands of drivers driving driving millions of races but anyway!) - yeah Im studying algorithm design atm

, so this is ok to put time into, right?), drop them from the sum of total points and print

The only place to change the pages at prestogp.com is at the standings-page right? The one with links to every race respectively and drivers from first to last order - the page for each single race should not be altered right?

Jim Hawley · Sep 23, 2014

Slightly off topic

I'm pretty sure the lap times are not updating on PGP.com

Jim

Nicolai Nicholson · Sep 24, 2014

Jim Hawley said:
Slightly off topic

I'm pretty sure the lap times are not updating on PGP.com

Jim

I see that Peter's time was set 2 days ago, there should not be any changes since then, unless Peter knows something I don't?

When and where did you set a time better than 1:45.285 Jim?