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

Lt Llama · March 3, 2006

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 " <center>Skill@TFC4ever.de - Speed Run Records ";
?>

<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"> Mapname </th>
<th align="center" bgcolor="#403F2E"> Nickname </th>
<th align="center" bgcolor="#403F2E"> Record Date </th>
<th align="center" bgcolor="#403F2E"> Runtime </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"><? echo "$mapname"; ?></td>
 <td align="center" bgcolor="#4c4d43"><? echo "$nickname"; ?></td>
 <td align="center" bgcolor="#4C5844"><? echo "$curdate"; ?></td>
 <td align="center" bgcolor="#4c4d43"><? echo "$recordtime"; ?></td>
 </tr>
 <?
 }
 }
 }
 ++$i;
 }
}
echo "</table>";
?>
[/code]

villav · March 3, 2006

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.

wickning1 · March 3, 2006

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 " <center>Skill@TFC4ever.de - Speed Run Records ";
?>

<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"> Mapname </th>
<th align="center" bgcolor="#403F2E"> Nickname </th>
<th align="center" bgcolor="#403F2E"> Record Date </th>
<th align="center" bgcolor="#403F2E"> Runtime </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"><? echo $mapname; ?></td>
 <td align="center" bgcolor="#4c4d43"><? echo $nickname; ?></td>
 <td align="center" bgcolor="#4C5844"><? echo $curdate; ?></td>
 <td align="center" bgcolor="#4c4d43"><? echo $formatTime; ?></td>
 </tr>
 <?
 }
}
echo "</table>";
?>[/code]

Lt Llama · March 4, 2006

[!--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

wickning1 · March 4, 2006

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.

Sign In

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

Recommended Posts

Lt Llama

Link to comment

Share on other sites

villav

Link to comment

Share on other sites

wickning1

Link to comment

Share on other sites

Lt Llama

Link to comment

Share on other sites

wickning1

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information