Jump to content

Archived

This topic is now archived and is closed to further replies.

Lt Llama

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

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]

Share this post


Link to post
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.

Share this post


Link to post
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]

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×

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.