I run a huge database using ASP and Access- due to hits I am switching to PHP/MYSQL. I populate a drop down box with athletics events ages, no problem, and can retrieve results- but am failing to get just best performances from each athlete and order by best- which in Track is fastes(lowest time) and in Field Highest(greatest)number.
This ASP code works fine:
<%
Query = "SELECT MIN(Perf) AS MinOfPerf,MIN(Perf2) AS MinOfPerf2,MAX(field) AS MaxOfField, Athfnam, Athsurnam,Club,Country from out4 WHERE event LIKE '%"
Query = Query & Request("event") & "%'"
Query = Query & " GROUP BY Athfnam, Athsurnam, Club,Country ORDER BY Min(Perf)asc, Min(perf2)asc, Max(Field)desc"
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "indoor2"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open Query,DataConn,1,2
%>
<html>
<body>
<h2> Ranking You selected <%=Request("event")%>. <%=Request("Athfnam,Athsurnam,Perf,Club,Country")%></h2>
<table border=1>
<tr><td><b> Athlete Firstname</b></td><td><b>Athlete Surname</b></td><td><b>Perf</b></td><td><b>Perf</b></td><td><b>Field</b></td><td><b>Club</b></td><td><b>Country</b></td></tr>
<%Do While Not RSlist.EOF%>
<tr>
<td><%=RSlist("Athfnam")%></td>
<td><%=RSlist("Athsurnam")%></td>
<td><%=RSlist("MinOfPerf")%></td>
<td><%=RSlist("MinOfPerf2")%></td>
<td><%=RSlist("MaxOfField")%></td>
<td><%=RSlist("Club")%></td>
<td><%=RSlist("Country")%></td>
</tr>
<%RSlist.Movenext
Loop
%>
</table>
<a href="../outlist.asp"> Return to list</a>
</body>
</html>
But trying to convert into PHP I am running into loads of problems. So far I have got:
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
$selected = mysql_select_db("sc010_indoor")
or die("Could not select first_test");
$sql = "SELECT id,athfnam,athsurnam,club,perf,date,meeting FROM out4 WHERE event LIKE ' " . $eventvar . " ' AND id IN (SELECT id, MIN(PERF), event FROM out4 GROUP BY perf)ORDER BY perf";
echo '<table align="center"
cellspacing="2" cellpadding="2">
<tr><td align="left"><b>athfname</b></td>
<td><b>athsurnam</b></td>
<td><b>club</b></td>
<td><b>perf</b></td>
<td><b>date</b></td>
<td><b>meeting</b></td>
</td></tr>
';
while ($row=mysql_fetch_array($sql)) {
echo "<tr><td align=\"left\">" .stripslashes($row[0]) . "</td><td align=\"left\">$row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td><td>$row[5]</td></tr>\n";
}
echo '</table>';
mysql_free_result ($sql);
mysql_close();
But get an error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sc010/domains/athleticsresults.co.uk/public_html/athperformances4.php on line 41
Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/sc010/domains/athleticsresults.co.uk/public_html/athperformances4.php on line 46.
Even if I could get this to run, does not deal with MAX for field.
I am in a bit of a mess as I need to have site back up by Sunday- help appreciated..
Charles
