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