LFS Forum - [OT] MySQL Help

#1 - JamesF1

[OT] MySQL Help

Sat, 26 Jan 2008 15:49

I'm having a little bit of a problem. I've never gotten myself too involved in learning the finer, clever, intricacies of MySQL before - but I'm working on it at the moment. But I've hit a stumbling block that I can't seem to find the answer to (though it's probably blindingly obvious).

For the application I'm working on, I have a table of times users have submitted (a user can have multiple times) associated to the user by way of the userId.

I need to find, in that table, the position of that user in the ranking (i.e. the position that user falls in a ranking of fastest time by each user ordered fastest>slowest).

For example:


uid    time
==============
1    24.28
4    28.21
2    32.11
2    21.29
3    23.86
4    43.21

Would need to be sorted:


uid    time
==============
2    21.29
3    23.86
1    24.28
4    28.21

Then if I want to get uid 1's position, I get back '3'.

Complex, I know... but hey - I'm sure it must be doable.

Any help appreciated

#2 - AndroidXP

Sat, 26 Jan 2008 15:56

I'm not sure if this works, but maybe it does the trick:

SELECT uid, min(time) as time FROM [?table?] GROUP BY uid ORDER BY time

E:

E2: I obviously can't try it out and I'm not sure whether I have to include aggregate functions in the group by clause, so just try to mess around with it a bit if you get SQL errors.

#3 - JamesF1

Sat, 26 Jan 2008 16:10

Well, rather, I'm looking to find the *position* of the user (by their user id). So, in the second example block in the first post, uid 2 is in P1, uid 3 is in P2, etc.

I'm trying to query the table with uid X, getting the lowest time for each uid, ordering them by lowest>highest and finding X's position within that list. If that makes sense?

#4 - AndroidXP

Sat, 26 Jan 2008 16:15

Oh, now I understand, but sorry, no idea how to accomplish that within MySQL. Why does it have to be done via SQL anyway? Don't you have PHP or similar available?

#5 - JamesF1

Sat, 26 Jan 2008 16:21

Thanks anyway... as for PHP, I could do, but I don't particularly fancy parsing a table of hundreds of rows long to find the position every time a user needs to find his/her position.

#6 - thisnameistaken

Sat, 26 Jan 2008 16:34

I don't think* there's any way to do this with MySQL. Presumably you've got at least one other table relating UIDs to other user info - why not do a select and loop to figure out where the user ranks when a time is submitted and store that in the user info table?

It would take a bit of messing about to keep the ranking column in order (re-ranking anybody inbetween the driver's old position and new position) but you could do that with one update.

* MySQL does surprise me with some cleverly useful language constructs sometimes, but I've never seen it do anything like this.

#7 - JamesF1

Sat, 26 Jan 2008 17:03

I'm struggling to think of a relatively efficient way to achieve this - but there must be one. Aside from that idea above Kev (which I'm not sure will work well when having to ignore all but the lowest time by each uid), are there any decent ways to achieve this?

I have that time table, then I also have the user table with corresponding data for the uid.

This one has me a bit stumped.

#8 - thisnameistaken

Sat, 26 Jan 2008 17:11

Quote from JamesF1 :I'm struggling to think of a relatively efficient way to achieve this - but there must be one. Aside from that idea above Kev (which I'm not sure will work well when having to ignore all but the lowest time by each uid), are there any decent ways to achieve this?

AndroidXP already demonstrated how to select just the lowest time set by every UID. So you'd do this:

- User sets a time.
- Run the select AXP provided, loop through the results to find the position of the new time, and the current position of this user (if any).
- If the user's ranking has improved, store the new ranking in the user table.
- Update the user table to increment the ranking of every user who ranked inbetween the user's old ranking and the user's new ranking (inclusive).

Job done. If a user wants to look up his/her ranking, you can just select the ranking column in your user DB and join it to the select AXP provided for pulling out the user's best time.

Does that sound sensible?

-

(JamesF1) DELETED by JamesF1 : No longer needed.

Sat, 26 Jan 2008 17:47

#9 - JamesF1

Sat, 26 Jan 2008 20:27

Thanks for the help guys - I've got it working, I will just need to double-check the re-ordering on a large dataset. But that's a job for another day.

#10 - thisnameistaken

Sun, 27 Jan 2008 01:58

It should work OK for large datasets, given that the user table will have a unique primary key. One problem you might come across is indexing on your uid|time table slowing things down, given that each UID can have multiple times - there's no way to index it unless you create an index based on both columns. You're still better off doing that than having no index at all, though.

#11 - dawesdust_12

Sun, 27 Jan 2008 04:37

James, just make me a bunch of users, I'll upload my (shitty) times.

#12 - Krammeh

Sun, 27 Jan 2008 07:47

everytime that there's a new entry into the database, why not do a local loop and add it into the database?

It saves one hell of a lot of work, to be honest!

#13 - Dygear

Sun, 27 Jan 2008 08:35

And then you can also join two tables by there associative UID to get the most of if the information. Such as ...

SELECT
	users.id,
	users.name,
	times.uid,
	min(times.time) as time
GROUP BY
	times.uid
ORDER BY
	time
FROM
	`users`,
	`times`
AND
	(users.id = times.uid);

That SHOULD work

.

As for getting the place of the person, I think that would have to be a function call, running through a loop.



<?php 
$sql = new mysqli('host', 'user', 'pass', 'base');

function get_place($uid) {
    global $sql;
    if ($result = $mysqli->query('SELECT users.id, users.name, times.uid, min(times.time) as time GROUP BY times.uid ORDER BY time FROM `users`, `times` AND (users.id = times.uid);')) {
        $i = 0;
        while ($row = $result->fetch_assoc()) {
            if ($row['uid'] != $i) {
                $i++;
            }
        }
        $result->close();
    } else
        return FALSE;
    return $i;
}
?>

#14 - BuddhaBing

Sun, 27 Jan 2008 08:51

I haven't tested either of these two approaches but they should get you close to a working solution:


SET @num = 1;

CREATE VIEW time_ordered_results AS
SELECT 
    uid, 
    MIN(time) AS time,
    @num := @num + 1 AS row_number
FROM <TABLENAME> 
ORDER BY time

SELECT
    row_number 
FROM time_ordered_results
WHERE uid = <UID YOU'RE INTERESTED IN>


SET @num = 1

SELECT row_number 
FROM (
    SELECT
        uid,
        MIN(time) AS time,
        @num := @num + 1 as row_number
    FROM <TABLENAME>
    ORDER BY time
) AS x
WHERE uid = <UID YOU'RE INTERESTED IN>

#15 - JamesF1

Sun, 27 Jan 2008 09:41

Thanks guys, I'll be looking over these solutions later - as I'm just off out. Looks like I have some learning to do

Dustin, catch me on MSN later, and I'll set you up with a few.

#16 - Dygear

Wed, 6 Feb 2008 05:44

What is the best way to handle time, in particular the unix epoch, in a MySQL table? I would think a full int (11), but what's the best way to make it the human readable in PHPMyAdmin. Basically, I just don't like looking at the date in it's raw seconds from in PHPMyAdmin. Any idea how to chance that?

Another one for you. Playing with strings in MySQL.
What's the best way to place two strings into one result. For example, this is my table.

+====+================+================+
| id |      fame      |      lame      |
+====+================+================+
| 01 |Scawen          |Roberts         |
+----+----------------+----------------+
| 02 |Eric            |Bailey          |
+----+----------------+----------------+
| 03 |Victor          |Vlaardingen     |
+----+----------------+----------------+

What query could I use to return the strings ... 'Scawen Roberts', 'Eric Bailey', & 'Victor Vlaardingen' as just one row called `name`?

#17 - Dygear

Wed, 6 Feb 2008 10:57

Look Ma, I can awanser my own questions too!

SELECT CONCAT(fame,', ',lame) AS name FROM `table` WHERE 1;

SELECT CONCAT(last_name,', ',f ... table ORDER BY full_name;

#18 - yankman

Wed, 6 Feb 2008 12:32

Quote from Dygear :What is the best way to handle time, in particular the unix epoch, in a MySQL table? I would think a full int (11), but what's the best way to make it the human readable in PHPMyAdmin. Basically, I just don't like looking at the date in it's raw seconds from in PHPMyAdmin. Any idea how to chance that?

Save it as DateTime rather than int(11) using FROM_UNIXTIME(timstamp).
It is drawn YYYY-MM-DD HH:mm:ss .
If u need a timestamp out of mysql use UNIX_TIMESTAMP(`datetime_column`).

#19 - Dygear

Wed, 6 Feb 2008 13:15

Quote from yankman :If u need a timestamp out of mysql use UNIX_TIMESTAMP(`datetime_column`).

The datetime function has a really good effect in PHPMyAdmin of allowing me to edit dates with a calender. But, I have a problem with UNIX_TIMESTAMP in that I can't get it to replace the result set as it's self. I think I'm going to have to specialize the query somewhat.

SELECT *, UNIX_TIMESTAMP(`date`) AS `epoch` FROM `table`;

To something like this ...

SELECT `id`, `title`, UNIX_TIMESPAMP(`date`) AS `date` FROM `table`;

#20 - thisnameistaken

Wed, 6 Feb 2008 13:42

Quote from Dygear :The datetime function has a really good effect in PHPMyAdmin of allowing me to edit dates with a calender. But, I have a problem with UNIX_TIMESTAMP in that I can't get it to replace the result set as it's self. I think I'm going to have to specialize the query somewhat.

SELECT *, UNIX_TIMESTAMP(`date`) AS `epoch` FROM `table`;

That works for me. MySQL version 4.1.20.

#21 - yankman

Wed, 6 Feb 2008 16:07

Quote from Dygear :

SELECT *, UNIX_TIMESTAMP(`date`) AS `epoch` FROM `table`;

To something like this ...

SELECT `id`, `title`, UNIX_TIMESPAMP(`date`) AS `date` FROM `table`;

I think there is a problem putting date in `` if there is date column.
Try it this way:

SELECT `id`, `title`, UNIX_TIMESTPAMP(`date`) AS 'date' FROM `table`;

But be warned UNIX_TIMESTAMP computes the timestamp according to ur timezone.
So check the result before using it, maybe u need to do a little correction.

#22 - SamH

Thu, 7 Feb 2008 19:37

Sorry I got here late. I haven't paid much attention to the solutions suggested so far, but if you haven't got an efficient one-stop "what position in the ranks is he" solution, the way I've done the CTRA ranks was dead simple..

SELECT COUNT(Users) As RankPosition FROM Table WHERE LaptimeInMS < SelectedDriver_LaptimeMS

This gives you an integer, RankPosition, which, when +1, equals the driver's best time in relation to everyone else's. It also conveniently gives everyone, with the same laptime, the same ranking position. Anyone with a millisecond or more longer time gets their appropriate position too. No PHP code looping or anything.

#23 - Dygear

Thu, 7 Feb 2008 21:38

Quote from SamH :

SELECT COUNT(Users) As RankPosition FROM Table WHERE LaptimeInMS < SelectedDriver_LaptimeMS

Show off ... I mean good work

#24 - SamH

Thu, 7 Feb 2008 21:56

LOL! ty!

#25 - SamH

Fri, 8 Feb 2008 02:46

To find the fastest time by a particular person, query something like this (I don't know your table/field names):..

SELECT User_FirstN, User_LastN, Lap_in_MS 
FROM records_table 
WHERE UID=3 
ORDER BY Lap_in_MS ASC 
LIMIT 1

This grabs the fastest laptime that a particular UID has achieved. Then count how many have a better time (above).. add 1 to that result for the guy's ranking, and I think you've got everything you're looking for.

[edit] Hmm.. I'm poking holes in my own code. You'd be better off having only one entry per person - their best result. Any other way, and their ranking would be affected by multiple faster entries from individuals. You'd need to decide if only the best time goes to determining rank. Hmm...

[edit][edit] Okay..

SELECT DISTINCT UID, Lap_in_MS
FROM records_table
GROUP BY UID
ORDER BY Lap_in_MS ASC, UID ASC

That would grab a recordset of one result per individual, and the result grabbed is their best effort. Slap it in a temporary table and find your UID's rank in that.

James, if by some miracle you're not already bent over the great white telephone, calling god at the sight of this stuff, feel free to email me on my LFS username at UKCT.net, and I'll happily give you a hand.