Jump to content


Photo

While logic on SQL handle cracking me up. Plzz help :(


  • Please log in to reply
4 replies to this topic

#1 Lt Llama

Lt Llama
  • Validating
  • PipPip
  • Member
  • 11 posts

Posted 03 March 2006 - 03:59 PM

Hi! My first post on these forums, and a post of desperation.
I have been working on a rank and speedrun system for one of the Half-Life mods for 2 months.
Last part to get working is a php page to show the best speedruns done on the maps.
All info are stored in an SQL database.
I have deleted and remade the while loop several times but its just not working.

The output of the script is
[a href=\"http://skill.tfc4ever.de/skillsrank/runrecords.php\" target=\"_blank\"]http://skill.tfc4ever.de/skillsrank/runrecords.php[/a]

The problem is that the nickname and record date is wrong. Im doing something stupid in the while loop looking through the SQL handle. Can any smart person see where the error/erros are?

The sql array handle which I pick from the db is sorted on mapname and has the 4 columns:
Nickname, Mapname, Date finished, Speed run time.
The outer while loop runs through the sql handle.
The inner while loop compares the speedrun times while the map name is the same.
When the inner loop is done it saves the best time and prints it.
The speed run times are stored in seconds.

Any help would be appreciated, as this is the last part of the system before its done.

Thx

/Lt

<?
include("dbinfo.inc.php");
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT `nickNames`, `mapName`, `curDate`, `runTime` FROM `skillmaps` ORDER BY `mapName` ASC";
$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

echo "<b><br><center><u><font face=verdana size=3 color=F4A21B>Skill@TFC4ever.de - Speed Run Records</b></u></font><br><br><br>";
?>

<title>Skill@TFC4ever.de - Speed run records</title>
<head>
<STYLE type="text/css">
BODY {scrollbar-3dlight-color:#2E8B57;
    scrollbar-arrow-color:#F4A21B;
    scrollbar-base-color:#4D5546;
    scrollbar-track-color:#33382E;
    scrollbar-darkshadow-color:#000000;
    scrollbar-face-color:#4D5546;
    scrollbar-highlight-color:#000000;
scrollbar-shadow-color:#404040;}
</STYLE>
</head>
<BODY BGCOLOR="#4D5546" TEXT="#FFFFFF" LINK="#ff9900" VLINK="#ff9900" ALINK="#ff9900" leftmargin=0 rightmargin=0 topmargin=0 bottommargin=0 marginheight=0 marginwidth=0>

<div align="center">
<table border="1" bordercolor="#000000" cellspacing="0" cellpadding="0" width="570" style="border-collapse: collapse">
<tr> 
<th align="center" bgcolor="#403F2E"><font face="verdana, Arial, Helvetica, sans-serif" size="2">  Mapname </font></th>
<th align="center" bgcolor="#403F2E"><font face="verdana, Arial, Helvetica, sans-serif" size="2" color="#00FF00">  Nickname  </font></th>
<th align="center" bgcolor="#403F2E"><font face="verdana, Arial, Helvetica, sans-serif" size="2" color="#00FFFF">  Record Date </font></th>
<th align="center" bgcolor="#403F2E"><font face="verdana, Arial, Helvetica, sans-serif" size="2" color="#FF0000">  Runtime  </font></th>
</tr>

<?
$i=0;
while ($i < $num) {
    $mapname=mysql_result($result,$i,"mapName");
    $nextmap=mysql_result($result,$i,"mapName");
    $recordtime = 0;
    $recordset = 0;
    
    while (($mapname == $nextmap) && ($i < $num)) {
        $runtime=mysql_result($result,$i,"runTime");
        if ($recordset == 0) {
            if ($runtime > 0) {
                $recordtime = $runtime;
                $recordset = 1;
                $nickname=mysql_result($result,$i-1,"nickNames");
                $curdate=mysql_result($result,$i-1,"curDate");
            }
        }
        if ($i < $num-1) {
            $nexttime=mysql_result($result,$i+1,"runTime");
            $nextmap=mysql_result($result,$i+1,"mapName");
            if ($mapname == $nextmap) {
                if ($nexttime < $recordtime && $nexttime > 0) {
                    $recordtime = $nexttime;
                    $nickname=mysql_result($result,$i+1,"nickNames");
                    $curdate=mysql_result($result,$i+1,"curDate");
                } else {
                    $nickname=mysql_result($result,$i,"nickNames");
                    $curdate=mysql_result($result,$i,"curDate");
                }
            } else {
                if ($recordtime > 0) {
                    $hours = ($recordtime / 3600) % 24;
                    if ($hours < 10)
                        $hours = "0" . $hours;
                    $minutes = ($recordtime / 60) % 60;
                    if ($minutes < 10)
                        $minutes = "0" . $minutes;
                    $seconds = $recordtime % 60;
                    if ($seconds < 10)
                        $seconds = "0" . $seconds;
                    $formatTime = $hours . ":" . $minutes . ":" . $seconds;
                    
                    ?>
                    <tr>
                    <td align="left" bgcolor="#4C5844"><font face="verdana, Arial, Helvetica, sans-serif" size="2"><? echo "$mapname"; ?></font></td>
                    <td align="center" bgcolor="#4c4d43"><font face="verdana, Arial, Helvetica, sans-serif" size="2"><? echo "$nickname"; ?></font></td>
                    <td align="center" bgcolor="#4C5844"><font face="verdana, Arial, Helvetica, sans-serif" size="2"><? echo "$curdate"; ?></font></td>
                    <td align="center" bgcolor="#4c4d43"><font face="verdana, Arial, Helvetica, sans-serif" size="2"><? echo "$recordtime"; ?></font></td>
                    </tr>
                    <?
                }
            }
        }
        ++$i;
    }
}
echo "</table>";
?>


#2 villav

villav
  • Members
  • Pip
  • Newbie
  • 3 posts
  • LocationBuffalo Grove, IL

Posted 03 March 2006 - 09:36 PM

you can do it in query:
SELECT `nickNames`, `mapName`, `curDate`, `runTime` FROM `skillmaps` ORDER BY `mapName` ASC", `runTime` ASC
so it'll sort by mapName and then by runtime.
Just out the result.

#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 03 March 2006 - 11:01 PM

See if this works for you:

<?php

include("dbinfo.inc.php");
mysql_connect($host,$username,$password);
mysql_select_db($database) or die( "Unable to select database");

$query="SELECT s1.nickNames, s1.mapName, s1.`curDate`, s1.runTime FROM skillmaps s1 INNER JOIN (SELECT mapName, MIN(runTime) as runTime FROM skillmaps GROUP BY mapName) s2 ON s1.runTime=s2.runTime AND s1.mapName=s2.mapName ORDER BY mapName ASC";
$result=mysql_query($query);

mysql_close();

echo "<b><br><center><u><font face=verdana size=3 color=F4A21B>Skill@TFC4ever.de - Speed Run Records</b></u></font><br><br><br>";
?>

<title>Skill@TFC4ever.de - Speed run records</title>
<head>
<STYLE type="text/css">
BODY {scrollbar-3dlight-color:#2E8B57;
    scrollbar-arrow-color:#F4A21B;
    scrollbar-base-color:#4D5546;
    scrollbar-track-color:#33382E;
    scrollbar-darkshadow-color:#000000;
    scrollbar-face-color:#4D5546;
    scrollbar-highlight-color:#000000;
scrollbar-shadow-color:#404040;}
</STYLE>
</head>
<BODY BGCOLOR="#4D5546" TEXT="#FFFFFF" LINK="#ff9900" VLINK="#ff9900" ALINK="#ff9900" leftmargin=0 rightmargin=0 topmargin=0 bottommargin=0 marginheight=0 marginwidth=0>
<div align="center">
<table border="1" bordercolor="#000000" cellspacing="0" cellpadding="0" width="570" style="border-collapse: collapse">
<tr>
<th align="center" bgcolor="#403F2E"><font face="verdana, Arial, Helvetica, sans-serif" size="2">  Mapname </font></th>
<th align="center" bgcolor="#403F2E"><font face="verdana, Arial, Helvetica, sans-serif" size="2" color="#00FF00">  Nickname  </font></th>
<th align="center" bgcolor="#403F2E"><font face="verdana, Arial, Helvetica, sans-serif" size="2" color="#00FFFF">  Record Date </font></th>
<th align="center" bgcolor="#403F2E"><font face="verdana, Arial, Helvetica, sans-serif" size="2" color="#FF0000">  Runtime  </font></th>
</tr>

<?
while ($row = mysql_fetch_array($result)) {
    $recordtime = $row['runTime'];
    $mapname= $row['mapName'];
    $nickname = $row['nickNames'];
    $curdate = $row['curDate'];
    if ($recordtime > 0) {
        $hours = ($recordtime / 3600) % 24;
        if ($hours < 10)
            $hours = "0" . $hours;
        $minutes = ($recordtime / 60) % 60;
        if ($minutes < 10)
            $minutes = "0" . $minutes;
        $seconds = $recordtime % 60;
        if ($seconds < 10)
            $seconds = "0" . $seconds;
        $formatTime = $hours . ":" . $minutes . ":" . $seconds;
        
        ?>
        <tr>
        <td align="left" bgcolor="#4C5844"><font face="verdana, Arial, Helvetica, sans-serif" size="2"><? echo $mapname; ?></font></td>
        <td align="center" bgcolor="#4c4d43"><font face="verdana, Arial, Helvetica, sans-serif" size="2"><? echo $nickname; ?></font></td>
        <td align="center" bgcolor="#4C5844"><font face="verdana, Arial, Helvetica, sans-serif" size="2"><? echo $curdate; ?></font></td>
        <td align="center" bgcolor="#4c4d43"><font face="verdana, Arial, Helvetica, sans-serif" size="2"><? echo $formatTime; ?></font></td>
        </tr>
        <?
    }
}
echo "</table>";
?>


#4 Lt Llama

Lt Llama
  • Validating
  • PipPip
  • Member
  • 11 posts

Posted 04 March 2006 - 07:41 AM

[!--quoteo(post=351436:date=Mar 3 2006, 10:36 PM:name=villav)--][div class=\'quotetop\']QUOTE(villav @ Mar 3 2006, 10:36 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
you can do it in query:
SELECT `nickNames`, `mapName`, `curDate`, `runTime` FROM `skillmaps` ORDER BY `mapName` ASC", `runTime` ASC
so it'll sort by mapName and then by runtime.
Just out the result.
[/quote]

You just gotta love SQL queries, it never occured to me that I could sort the run times after sorting the map names. Thanks for opening my eyes. And the script will probably be faster to.

[!--quoteo(post=351451:date=Mar 4 2006, 12:01 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 4 2006, 12:01 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
See if this works for you:

[/quote]

Thx wickning1. I could probably learn a lot from looking at your query :).
But its something wrong in the result set. I tried and it gives me:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /srv/www/htdocs/web3/html/skillsrank/slask1.php on line 39

/Lt

#5 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 04 March 2006 - 03:55 PM

Try changing the query to this:
SELECT s1.nickNames, s1.mapName, s1.`curDate`, s1.runTime FROM skillmaps s1 INNER JOIN (SELECT mapName, MIN(runTime) as bestrunTime FROM skillmaps GROUP BY mapName) s2 ON s1.runTime=s2.bestrunTime AND s1.mapName=s2.mapName ORDER BY mapName ASC

Also add "echo mysql_error();" right after "$result=mysql_query($query);" and report back with what it prints out.

If you are using an old version of mysql (<4.1) then this won't work at all, since pre-4.1 didn't support subqueries.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users