Hi again!
Still working on that game. I'm working on the high scores for that game (which I'm able to retrieve just fine), but I'm struggling to figure out how to get a persons Overall and Difficulty ranks upon completing the game. Or how to show the rank on the userbar (where I display their personal best).
I have a games table set up like this:
CREATE TABLE `jw_games` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`letters` varchar(10) NOT NULL,
`start` datetime NOT NULL,
`end` datetime DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `jw_games_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `jw_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
I also have a guesses table, where input is stored for calculation of the final score at the end of the game. It looks like this:
CREATE TABLE `jw_guesses` (
`game_id` int(11) unsigned NOT NULL,
`word` varchar(10) NOT NULL,
KEY `game_id` (`game_id`),
CONSTRAINT `jw_guesses_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `jw_games` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB
Finally this is the query I run at the end of the game to get the player's final score:
SELECT IFNULL(SUM(wordPoint),0) as totalPoints FROM
(SELECT IF((CHAR_LENGTH( `word` ) - 2) = :length,(:length+2),(CHAR_LENGTH( `word` ) - 2)) as wordPoint
FROM `'.TBL_PREFIX.'guesses` WHERE `game_id` = :gameid) as wordPoints
Question one: How do I check if this score would be in the top ten overall? And Q2:How do I check for different difficulty levels (different timer / word length)
I also have this function for getting the top scores:
public function getTopScores($limit = 10,$offset = 0,$start_date = NULL,$end_date = NULL,$difLength = NULL,$difTimer = NULL) {
$sql = 'SELECT u.`username`,g.`score`,(g.`timer`/60) as timer,CHAR_LENGTH( g.`letters` ) as length
FROM `'.TBL_PREFIX.'games` g
JOIN `'.TBL_PREFIX.'users` u
ON u.`id` = g.`user_id`
WHERE g.`score` > 0';
if( !is_null($start_date) && !is_null($end_date) ) {
$sql .= ' AND g.`end` BETWEEN :start AND :end';
} elseif( is_null($start_date) && !is_null($end_date) ) {
$sql .= ' AND g.`end` < :end';
} elseif( !is_null($start_date) && is_null($end_date) ) {
$sql .= ' AND g.`end` BETWEEN :start AND NOW()';
}
if( !is_null($difLength) ) {
$sql .= ' AND CHAR_LENGTH( g.`letters` ) = :difLength';
}
if( !is_null($difTimer) ) {
$sql .= ' AND g.`timer` = :difTimer';
}
$sql .= ' ORDER BY g.`score` DESC LIMIT :offset,:limit';
try {
$stmt = $this->PDO->prepare($sql);
if( $stmt === FALSE ) {
$err = $stmt->errorInfo();
throw new PDOException('There was a database error; '.$err[2]);
}
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
if( !is_null($start_date) ) $stmt->bindParam(':start', $start_date,PDO::PARAM_STR);
if( !is_null($end_date) ) $stmt->bindParam(':end', $end_date, PDO::PARAM_STR);
if( !is_null($difLength) ) $stmt->bindParam(':difLength',$difLength, PDO::PARAM_INT);
if( !is_null($difTimer) ) $stmt->bindParam(':difTimer', $difTimer, PDO::PARAM_INT);
$result = $stmt->execute();
if( $result === FALSE ) {
$err = $stmt->errorInfo();
throw new PDOException('There was a database error; '.$err[2]);
}
if( $stmt->rowCount() > 0 ) {
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
return FALSE;
} catch (PDOException $e) {
myError($e->getMessage());
}
}
Am I going to have to run this function twice to get the answers to my previous questions? IE Select overall top ten, and see if this game is in it? then Select the top ten for the current difficulty and loop thru to see if this game is in that list? It seems like there should be a way to do this in MySQL and just return the rank of the score, instead of looping through in PHP to find the rank using a counter (as in the future when there are thousands, maybe even millions of games this would be resource intensive).
