1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
Dismiss Notice
Like RaceDepartment on Facebook.

PrestoGP.com - the results and league table

Discussion in 'Presto GP' started by Nicolai Nicholson, Sep 16, 2014.

  1. I'll use this thread to post some of the programming (php) that I think is relevant to the creation of the league table.
     
    • Like Like x 1
  2. 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 />
    <!-- Drivers below the first line at season end will have to qualify for further participation in 1st division. Drivers below the second line will be relegated to 2nd division.<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">&nbsp;</td>
    <td height="20" colspan="2" align="center" valign="top">&nbsp;</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>
     
  3. 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";
    ?>

    <!-- Kopier herfra -->

    <!-- Qualify -->
    <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++;
    }
    ?>

    <!-- Race1 -->
    </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>

    <!-- Race2 -->
    <?php
    if ($race2id == "") echo "<!--"; ?>

    <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 2</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 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>


    <!-- Kopier til hit -->

    </table>
     
  4. Reik Major

    Reik Major
    Premium Member

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

    :D !aet fo puc ym ton s'tI :ylsuoires woN
     
    • Haha Haha x 7
  5. 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..
     
    • Beer Beer x 1
  6. Peter Decker

    Peter Decker
    PrestoGP Veteran

    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 :)
     
    • Like Like x 1
    • Winner Winner x 1
  7. 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 :)
     
  8. Peter Decker

    Peter Decker
    PrestoGP Veteran

    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?
     
    • Like Like x 2
  9. David Turnbull

    David Turnbull
    PrestoGP Veteran

    i cant help here, hopefully nico can give you the info you need when hes around, thanks for looking into this ;)
     
    • Like Like x 1
  10. 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?
     
    • Like Like x 2
  11. Peter Decker

    Peter Decker
    PrestoGP Veteran

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

    If I just could get an overlook of the tables, it would help. These tables would be good:
    r07leaguerace
    r07leagueracedrivers
    r07driverevent
     
    • Like Like x 1
    • Beer Beer x 1
  12. 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.

    r07leaguerace.jpg
    r07leagueracedrivers.jpg
    r07driverevent.jpg
     
    • Like Like x 1
  13. Peter Decker

    Peter Decker
    PrestoGP Veteran

    Thanks! I think I know at least one way of doing this :) PM will come.
     
    • Like Like x 3
  14. When you get this sorted, let me know what method you used. Just out of curiosity, i like these kinds of puzzles.
     
    • Like Like x 1
  15. Peter Decker

    Peter Decker
    PrestoGP Veteran

    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 :D, 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?
     
    • Like Like x 2
  16. Jim Hawley

    Jim Hawley
    PrestoGP Veteran

    Slightly off topic

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

    Jim
     
    • Like Like x 1
    • Beer Beer x 1
  17. 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?
     
    • Like Like x 1
  18. Peter Decker

    Peter Decker
    PrestoGP Veteran

    yeah my PB was updated correctly
     
    • Beer Beer x 1
  19. I have new to PB:), but has not been updated
     
    • Beer Beer x 1
  20. Jim Hawley

    Jim Hawley
    PrestoGP Veteran

    Last night Nico

    1'44'3XX :)
     
    • Winner Winner x 1