How to sort the results of a ranking?

6

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?

    
asked by Gabriel Longatti 15.09.2015 в 19:05
source

2 answers

6

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 |

Examples Running in SQLFiddle

You can also use DATETIME type and save the start date and time, date and end time, this template is for you to search.

    
answered by 15.09.2015 / 19:45
source
4

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");
    
answered by 15.09.2015 в 19:26