Getting personal records from WCA database. This file is for my blog article at: http://www.terabo.net/blog/practical-wcadb/
| <?php | |
| // DB connection | |
| try { | |
| $pdo = new PDO( | |
| 'mysql:dbname=xxxx;host=localhost;charset=utf8', | |
| 'xxxx', | |
| 'xxxxxxxx', | |
| $options = array( | |
| PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, | |
| PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, | |
| PDO::ATTR_EMULATE_PREPARES => false, | |
| PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true | |
| ) | |
| ); | |
| } catch (PDOException $e) { | |
| die($e->getMessage()); | |
| } catch (Exception $e) { | |
| die($e->getMessage()); | |
| } | |
| // SQL queries | |
| $stmt = $pdo->prepare(implode(' ', array( | |
| 'SELECT PersonSingle.eventId, single, average, format, name, cellName FROM', | |
| '((SELECT personId, eventId, best AS single FROM RanksSingle WHERE personId = ?) PersonSingle', | |
| 'LEFT OUTER JOIN', | |
| '(SELECT personId, eventId, best AS average FROM RanksAverage WHERE personId = ?) PersonAverage', | |
| 'ON PersonSingle.eventId = PersonAverage.eventId)', | |
| 'INNER JOIN Events', | |
| 'ON PersonSingle.eventId = Events.id' | |
| ))); | |
| $stmt->execute(array('2010TERA01', '2010TERA01')); | |
| $results = $stmt->fetchAll(); | |
| // Formatting functions | |
| function time2str($val) { | |
| $val = intval($val, 10); | |
| $decimal = $val % 100; | |
| $second = intval(floor($val / 100), 10); | |
| $minute = intval(floor($second / 60), 10); | |
| $second = $second % 60; | |
| if ($minute !== 0) { | |
| return sprintf("%d:%02d.%02d", $minute, $second, $decimal); | |
| } else { | |
| return sprintf("%d.%02d", $second, $decimal); | |
| } | |
| } | |
| function multi2str($val) { | |
| $failure = intval(substr($val, 7, 2), 10); | |
| $success = 99 - intval(substr($val, 0, 2), 10) + $failure; | |
| $t = intval(substr($val, 3, 4), 10); | |
| $second = $t % 60; | |
| $minute = floor($t / 60); | |
| return $success . '/' . ($success + $failure) . ' ' . sprintf("%d:%02d", $minute, $second); | |
| } | |
| function format($val, $format) { | |
| if ($format === 'time') { | |
| return time2str($val); | |
| } else if ($format === 'multi') { | |
| return time2str($val); | |
| } else { | |
| return $val; | |
| } | |
| } | |
| // View part | |
| ?><html><body> | |
| <table> | |
| <thead> | |
| <tr> | |
| <th>Event</th><th>Single</th><th>Average</th> | |
| </tr> | |
| </thead> | |
| <tbody> | |
| <?php foreach($results as $r) { ?> | |
| <tr> | |
| <td><?php echo $r['name']; ?></td> | |
| <td><?php echo format($r['single'], $r['format']); ?></td> | |
| <td><?php | |
| if ($r['average'] === NULL) { | |
| echo '-'; | |
| } else if ($r['eventId'] === '333fm') { | |
| echo intval($r['average']) / 100.0; | |
| } else { | |
| echo format($r['average'], $r['format']); | |
| } | |
| ?></td> | |
| </tr> | |
| <?php } ?> | |
| </tbody> | |
| </table> | |
| </body></html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment