Jump to content

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


Lt Llama

Recommended Posts

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

[code]
<?
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>";
?>
[/code]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

See if this works for you:

[code]<?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>";
?>[/code]
Link to comment
Share on other sites

[!--quoteo(post=351436:date=Mar 3 2006, 10:36 PM:name=villav)--][div class=\'quotetop\']QUOTE(villav @ Mar 3 2006, 10:36 PM) [snapback]351436[/snapback][/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) [snapback]351451[/snapback][/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
Link to comment
Share on other sites

Try changing the query to this:
[code]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[/code]

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.