Lt Llama Posted March 3, 2006 Share Posted 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 "<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] Quote Link to comment Share on other sites More sharing options...
villav Posted March 3, 2006 Share Posted March 3, 2006 you can do it in query:SELECT `nickNames`, `mapName`, `curDate`, `runTime` FROM `skillmaps` ORDER BY `mapName` ASC", `runTime` ASCso it'll sort by mapName and then by runtime.Just out the result. Quote Link to comment Share on other sites More sharing options...
wickning1 Posted March 3, 2006 Share Posted March 3, 2006 See if this works for you:[code]<?phpinclude("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] Quote Link to comment Share on other sites More sharing options...
Lt Llama Posted March 4, 2006 Author Share Posted 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` ASCso 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 Quote Link to comment Share on other sites More sharing options...
wickning1 Posted March 4, 2006 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.