I'm developing a game, and in it I need to do a ranking according to the time it took the user to reach the end of the game.
My question is, what kind of data should I use in my database to be able to organize when displaying the ranking?
I have the time variable ex: "00:32:54" but it is a string, how do I get it organized in a ranking?
You could use either TIME type or type INT or DOUBLE / FLOAT / NUMERIC (depending on the database). TIME (MySQL) implementation:
CREATE TABLE rankingbytime (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
nome VARCHAR(40),
tempo TIME
);
INSERT INTO rankingbytime (nome, tempo) VALUES
('Fulano', '00:32:54'),
('Beltrano', '00:29:52'),
('Ciclano', '00:30:19'),
('Treuslano', '00:31:20'),
('Peuslano', '00:35:14');
SELECT id, nome, tempo FROM rankingbytime ORDER BY tempo ASC;
Output:
| id | nome | tempo |
|----|-----------|---------------------------|
| 2 | Beltrano | January, 01 1970 00:29:52 |
| 3 | Ciclano | January, 01 1970 00:30:19 |
| 4 | Treuslano | January, 01 1970 00:31:20 |
| 1 | Fulano | January, 01 1970 00:32:54 |
| 5 | Peuslano | January, 01 1970 00:35:14 |
Implementing the FLOAT (MySQL) type:
CREATE TABLE rankingbyseconds (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
nome VARCHAR(40),
tempo FLOAT(13,3)
);
INSERT INTO rankingbyseconds (nome, tempo) VALUES
('Fulano', 1974.523),
('Beltrano', 1792.584),
('Ciclano', 1819.287),
('Treuslano', 1880.518),
('Peuslano', 2114.261);
SELECT id, nome, tempo FROM rankingbyseconds ORDER BY tempo ASC;
Decimal float are the thousandths, if you use only seconds, you can use the INT field type by calculating only integers that would represent the number of seconds.
Output:
| id | nome | tempo |
|----|-----------|----------|
| 2 | Beltrano | 1792.584 |
| 3 | Ciclano | 1819.287 |
| 4 | Treuslano | 1880.518 |
| 1 | Fulano | 1974.523 |
| 5 | Peuslano | 2114.261 |
You can also use DATETIME type and save the start date and time, date and end time, this template is for you to search.
I would use PHP to convert the time in seconds, and then only record the number in the database, can do a scoring system, example 1 second equals 3 points, lost a life -100 points, then multiply the seconds by 3 and add everything in the end.
Example of the function in PHP that converts time in seconds:
<?php
function seconds_from_time($time) {
list($h, $m, $s) = explode(':', $time);
return ($h * 3600) + ($m * 60) + $s;
}
echo seconds_from_time("00:32:54");