Qyery to get the latest record of a list of speedruns

[Lt Llama]

Hi

Getting numb trying to figure this one out.
I have a db with a list of game map names.
Each map can be speedrun.
When someone finish the time is saved.
People can choose not to speedrun, the runtime is then 0.

[code]$query="SELECT `steamId`,`nickNames`, `mapName`, `curDate`, `runTime`,`curTime` FROM `skillmaps` WHERE runTime > 0 ORDER BY `mapName` ASC, `runTime` ASC";[/code]

The query above pics all speedruns which are > 0.
It sorts it on
1. Map name
2. The finish time

Now, from this i want to find out which is the last speed run record done.
Ill come so far that it shows the last map which had a speed run per date.
BUT, if there was several speedruns the same day it doesnt work.

Why? Because the script dont check if the speedrun was done the same day and do a time sort.
I tried but I just mess it up.

The while loop below should return the latest speedrun.

[code]<?
$i=0;
while ($i < $num1) {
    $mapname1=mysql_result($result1,$i,"mapName");
    $curtime=mysql_result($result1,$i,"curTime");
    if ($i < ($num1-1)) {
        $nextmap1=mysql_result($result1,$i+1,"mapName");
        $nextdate1=mysql_result($result1,$i+1,"curDate");
    }
    if ($mapname1 != $nextmap1) {
        if ($recorddate <= $nextdate1 || $recorddate == "") {
            $recorddate = mysql_result($result1,$i+1,"curDate");
            $nickname=mysql_result($result1,$i+1,"nickNames");
            $recordtime=mysql_result($result1,$i+1,"runTime");
            $time=mysql_result($result1,$i+1,"curTime");
            $recordmap=mysql_result($result1,$i+1,"mapName");
        }
    }
    ++$i;
}[/code]

My question is, can I change my sql query so I dont have to mess with the conditions?
I.e. would it be possible to get 1 line in the result handle, only showing the latest record?
That would be awsome.

If not, any suggestion would be appreciated.

The output where i want this is the upper part of this page:

[a href=\"http://skill.tfc4ever.de/skillsrank/runrecords.php\" target=\"_blank\"]http://skill.tfc4ever.de/skillsrank/runrecords.php[/a]

Thx

/Lt.

[AndyB]

If you have auto-incremented records, then the most recent record can be found with [a href=\"http://ca.php.net/manual/en/function.mysql-insert-id.php\" target=\"_blank\"]the mysql-insert-id function[/a]

[fenway]

[!--quoteo(post=369889:date=Apr 29 2006, 10:31 AM:name=Lt Llama)--][div class=\'quotetop\']QUOTE(Lt Llama @ Apr 29 2006, 10:31 AM) [snapback]369889[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]$query="SELECT `steamId`,`nickNames`, `mapName`, `curDate`, `runTime`,`curTime` FROM `skillmaps` WHERE runTime > 0 ORDER BY `mapName` ASC, `runTime` ASC";[/code]
Why? Because the script dont check if the speedrun was done the same day and do a time sort.
[/quote]
Why are you storing curDate and curTime separately? And why are these not present in the order by clause?

[Lt Llama]

[!--quoteo(post=369943:date=Apr 29 2006, 09:27 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Apr 29 2006, 09:27 PM) [snapback]369943[/snapback][/div][div class=\'quotemain\'][!--quotec--]
If you have auto-incremented records, then the most recent record can be found with [a href=\"http://ca.php.net/manual/en/function.mysql-insert-id.php\" target=\"_blank\"]the mysql-insert-id function[/a]
[/quote]

Sorry, i was maybe unclear. With record i mean a speed run record. Not a databse record.
maybe you didnt mean that anyway :).
The best I can do is to sort on map names and then sort ascending on the finish time within the list of maps.
And i was an idiot when i made the db. No auto increment used. Can I add this somehow afterwards?

This is what is in the result handle, i give an example.

[b]steam id ; nickNames ; mapName ; curDate ; runTime ; curTime[/b]
-----------------------------------------------------
row 1: id ; nick ; map_a ; 2006/02/06 ; 200 ; 13:19:05
row 2: id ; nick ; map_a ; 2006/02/06 ; 300 ; 13:40:05
row 3: id ; nick ; map_a ; 2006/02/06 ; 400 ; 13:45:05
row 4: id ; nick ; map_b ; 2006/02/07 ; 300 ; 18:19:05
row 5: id ; nick ; map_b ; 2006/02/07 ; 400 ; 18:40:05
row 6: id ; nick ; map_b ; 2006/02/07 ; 500 ; 19:45:05
row 7: id ; nick ; map_c ; 2006/02/07 ; 300 ; 08:19:05
row 8: id ; nick ; map_c ; 2006/02/07 ; 400 ; 08:40:05
row 9: id ; nick ; map_c ; 2006/02/07 ; 500 ; 09:45:05

The result handle is as you can see sorted on mapName and then column runTime.
The script should return row 4 as this is the most rescent record.
Instead it returns row 7.

[Lt Llama]

[!--quoteo(post=369962:date=Apr 29 2006, 10:50 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 29 2006, 10:50 PM) [snapback]369962[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Why are you storing curDate and curTime separately? And why are these not present in the order by clause?
[/quote]

Just a descision i did at the start.
The time and date comes from the server time and date, picked by a plugin made for a game.
I never thought about that I could have them in the same field.

The reason i dont have them in the order by clause is that I want the result handle to contain groups of map names, and within each map name group i want the best time at the top.

[fenway]

Then I don't understand your question. You want the top speed run for each map, and then how do the date/time columns fit in?

[Lt Llama]

[!--quoteo(post=370004:date=Apr 30 2006, 03:35 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 30 2006, 03:35 AM) [snapback]370004[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Then I don't understand your question. You want the top speed run for each map, and then how do the date/time columns fit in?
[/quote]

I want only one record in the output, the latest speedrun.
I have a script which outputs the speed run record for every map.
Now from those I want to pick the latest.
I find the most rescent date when a record was broken, but the problem is when there are multiple records in the same day.

Sorry if I cant explain any better. If I could I could probably solve it :).

[Lt Llama]

I was tired yesterday.
I have autoincremented id's.
Sorry to confuse you more than necessary.
Used them and it seems to work now.

[code]<?
$i=0;
while ($i < $num1) {
    $mapname1=mysql_result($result1,$i,"mapName");
    $curdbid=mysql_result($result1,$i,"id");
    if ($i < ($num1-1)) {
        $nextmap1=mysql_result($result1,$i+1,"mapName");
        $nextdate1=mysql_result($result1,$i+1,"curDate");
        $nextdbid=mysql_result($result1,$i+1,"id");
    }
    if ($mapname1 != $nextmap1) {
        if ($recorddate <= $nextdate1 || $recorddate == "") {
            if ($highestid < $curdbid) {
                $highestid=mysql_result($result1,$i+1,"id");
                $recorddate = mysql_result($result1,$i+1,"curDate");
                $nickname=mysql_result($result1,$i+1,"nickNames");
                $recordtime=mysql_result($result1,$i+1,"runTime");
                $time=mysql_result($result1,$i+1,"curTime");
                $recordmap=mysql_result($result1,$i+1,"mapName");
            }
        }
    }
    ++$i;
}[/code]

[fenway]

Well, glad you got it working, but there's no reason that you can't do this in MySQL alone; also, mysql_result() is quite inefficient.