How to sort the results of a ranking?

Asked

Viewed 461 times

6

I am developing a game, and in it I need to make a ranking according to the time it took the user to reach the end of the game.

My question is the following: what kind of data should I use in my database to organize when showing the ranking?

I have the time variable ex : "00:32:54" however she is a string, as I will do to arrange her in a ranking?

php mysql sql

  • Why not use the guy team ? Would the ranking simply be the ordered time decreasing? If yes, just sort in the same query.

  • If you only have one time per player, it should work sort by string. Otherwise you need to use TIME as they’re suggesting to you.

  • This question is being debated at the goal: http://meta.pt.stackoverflow.com/q/4504/132

2 answers

6


You could use either the TIME type or the INT or DOUBLE/FLOAT/NUMERIC type (depending on the database). Implementation of the type TIME (Mysql):

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;

Exit:

| 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 type FLOAT (Mysql):

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;

The decimal places of the type float are the thousandths, if using only second, you can use the field type INT calculating only integers that would represent the number of seconds.

Exit:

| id |      nome |    tempo |
|----|-----------|----------|
|  2 |  Beltrano | 1792.584 |
|  3 |   Ciclano | 1819.287 |
|  4 | Treuslano | 1880.518 |
|  1 |    Fulano | 1974.523 |
|  5 |  Peuslano | 2114.261 |

Examples working on Sqlfiddle

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

  • 4

    My dear friend, I will be eternally grateful for you, our ball show your answer and fixed my problem, THANK YOU VERY MUCH, SUCCESS FOR YOU !

4

I would use the PHP to convert the time into seconds, and then record only the number in the database, can make a scoring system, example 1 second is equal to 3 points, lost a life -100 points, then multiplies the seconds by 3 and sums up everything at the end.

Example of the function in PHP that converts time into 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");

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.