retreiving from mysql using drop down list dynamically populated

[laugharne]

I have a form with a drop down box which is populated from
mysql, showing age groups and disciplines for athletics events.On selection I want to retreive the results ( in this case for an age group athletics results, and show how many athletes are say U15 doing 100m. At the moment all I get is the total number of athletes in the database- i.e on selection of an age, the foem handler is not picking up the age group.

Drop down box form is as follows:
<?php
$username = "vv";
$password = "vv";
$hostname = "localhost";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

$selected = mysql_select_db("sc010_indoor",$dbh)
or die("Could not select first_test");
?>
<form action="../athperformances2.php" method="get">
<SELECT NAME="Events">
<option value="xx" SELECTED>Select An Event</option>
<?
$result = mysql_query ("SELECT DISTINCT Event FROM out4 ORDER BY Event ");//populates drop down box-works fine
if ($row = mysql_fetch_array($result)) {
do {
print ("<OPTION VALUE=\"".$row["Event"]."\">");
print $row["Event"];
print ("</OPTION>");
} while($row = mysql_fetch_array($result));
} else {print "";}
?>
<input type="submit" value="search">
</select>
</form>
</body>
</html>

</p>
<p><font face="Arial, Helvetica, sans-serif"><a href="http://www.athletics-results.co.uk">HOME</a></font></p>
</div>

//this works fine and drop down is populated

The handel form is a follows:

<?php
$username = "xx";
$password = "xx";
$hostname = "localhost";
$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");

$result = mysql_query("SELECT athsurnam, club FROM out4 where Event LIKE '%$xx%'");
$num = mysql_num_rows ($result);
if ($num >0) {
echo "<p><big><b> There are $num athletes.</b></big></p>";

echo '<table align="center"
cellspacing="2" cellpadding="2">
<tr><td align="left"><b>athsurnam</b></td>
<td align="left"><b>club</b>
</td></tr>
';

while ($row=mysql_fetch_array($result,MYSQL_NUM)) {
echo "<tr><td align=\"left\">" .stripslashes($row[0]) . "</td><td align=\"left\">$row[1]</td></tr>\n";
}

echo '</table>';
mysql_free_result ($result);

}else{
echo '<p> There are no records at the moment.</p>';
}
mysql_close();

?>
</p>
<p><font face="Arial, Helvetica, sans-serif"><a href="http://www.athletics-results.co.uk">HOME</a></font></p>
</div>

//This only brings out all athletes in the database. Any help much appreciated

Charles

[devinemke]

change this:

$result = mysql_query("SELECT athsurnam, club FROM out4 where Event LIKE '%$xx%'");

to this:

$result = mysql_query("SELECT athsurnam, club FROM out4 where Event LIKE '%" . $_POST['Events'] . "%'");

[laugharne]

I am afraid still brings out all athletes, irrspective of selected group in drop down.

Maybe I did not expalain enough.

Form is populated from mysql, selecting all events that athletes do, so has a drop down of SM 100,Sm 200m,SM 400m etc. I am trying to pull ou athletes results for the selected item- i.e SM 100m. Instead I just get all athletes in the database. I know I have not included code to pull down performances but I can add that, it is just trying to get the selected item in the drop down box rather than all records that I am struggling with. I have played around this evening, but caused more problems than I solved.

So drop down has(event)- there are loads covering all disciplines and ages- down to U11B LJ

SM100m
SM 200m
SM 400m
etc

on selection I am trying to just pull out info on selected (event). I have this working fine in asp, but am struggling to replicate in PHP, as I am moving from asp/access to php/mysql.

My asp coding is as follows:
<%
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>

Basically I am trying to do the same in php

Charles

[laugharne]

This now works- thanks for help. All required now is to remove duplicates of athletes to just show best performance and order by performance- does min and max work. Also is it possible to take best calculate all performances- i.e time * number athletes to create an average ??

$username = "xx";
$password = "xx";
$hostname = "localhost";
$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");

$result = mysql_query("SELECT athfnam,athsurnam,club,date FROM out4 where Event LIKE '%" . $_GET['Events'] . "%'");
$num = mysql_num_rows ($result);
if ($num >0) {
echo "<p><big><b> There are $num athletes.</b></big></p>";

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></tr>
';

while ($row=mysql_fetch_array($result,MYSQL_NUM)) {
echo "<tr><td align=\"left\">" .stripslashes($row[0]) . "</td><td align=\"left\">$row[1],$row[2],$row[3],$row[4]</td></tr>\n";
}

echo '</table>';
mysql_free_result ($result);

}else{
echo '<p> There are no records at the moment.</p>';
}
mysql_close();

[Roger_Ramjet]

You need GROUP BY http://dev.mysql.com/doc/mysql/en/Group_by_functions_and_modifiers.html and the aggregate finctions (min, max, etc)http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html to remove your duplicates and find best performances. You will also need to create a calculated field to return your average. You can create your own function to do this, the same as in Access http://dev.mysql.com/doc/mysql/en/CREATE_FUNCTION.html