Skip to content

Instantly share code, notes, and snippets.

@kotarot
Created March 8, 2015 08:49
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save kotarot/11007a966b239b717f7a to your computer and use it in GitHub Desktop.
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