[Resolved] MIN and ORDER BY on QUERY fail

laugharne

I amtrying to use Min and Order By on a query but get error. This form is selected by a previous form populated by a drop down box from mysql. It works by selecting an event(track event say 100m SM) and runs a query pulling all SM 100 results. I want to show only the best time of each athlete and order by the overall best (MIN) performance. Without Order BY and (MIN) works fine, but of little use as shows all athletes in an event and random order, not by best(fastest=min of perf)

Query works , but as i add MIN(perf) - to show only the best performance of each athlete it fails. ORDER BY does not function, a problem with the call up of events from the populated drop down list. Code as below:

$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,MIN(perf),date,meeting FROM out4 WHERE Event LIKE '%" ORDER BY perf . $_GET['Events'] . "%'" );
$num = mysql_num_rows ($result);
if ($num >0) {
echo "<big> There are $num athletes.</big>";

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($result,MYSQL_NUM)) {
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 ($result);

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

Charles
p.s how do I get highlighting to show on posts ?

Roger_Ramjet

You need a GROUP BY clause to group each athelete's performances and then select only the minimum.

"If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. " ie list all rows.

For your query, you will have to exclude columns with unique data, or again each row will be unique and therefore it's own minimum. This all boils down to using the group by in a subquery so that you can add the other details in the main query.

To highlight code use the [ p h p ] and [ / p h p ] tags (remove spaces)

$sql="SELECT id, athfnam,athsurnam,club,perf,date,meeting,event FROM out4 WHERE id IN (SELECT id, MIN(perf) FROM out4 WHERE Event LIKE "%" GROUP BY perf) ORDER BY perf" ;
//assuming id as the primary key on out4

Incidently, mysql is one of the few DBMS that allows such loose syntax in a group by clause. Most other DBMS will spit the dummy if you do not list id in the group by.

laugharne

I am now getting an error saying the Count number of rows invalid

$result = mysql_query("SELECT id,athfnam,athsurnam,club,perf,date,meeting FROM out4 WHERE id IN (SELECT id, MIN(PERF) FROM out4 WHERE Event LIKE "%" GROUP BY perf)ORDER BY perf");

I had to remove the $_GET[EVENT."%" as otherwise error on that line, from original script.

Is it because trying to count results and I am trying to remove duplicates at same time?

Charles

Roger_Ramjet

I don't follow you.

laugharne

I think I am confusing myself

$num = mysql_num_rows ($result);

if ($num >0) {
echo "<big> There are $num athletes

The script stops running with error on this code, though appreciate it might be earlier. Changing

WHERE Event LIKE '%"

to [/code]WHERE Event LIKE "%" [/code] also causes problem, so removed the $_GET['Events'] . "%'" . Still an error at $NUM, so not sure whther srcipt will run without $GET['events']

I think I need to start again- it took some time to get my original script to work, and maybe it is badly written. It is designed to pull out a selected item from a drop down box , populated from mysql, and state number of records.

Charles

Roger_Ramjet

My fault

$eventvar = $_GET['event'];
//assuming event is a string
$sql = "SELECT id,athfnam,athsurnam,club,perf,date,meeting FROM out4 WHERE id IN (SELECT id, MIN(PERF) FROM out4 WHERE Event LIKE ' " . $eventvar . " ' GROUP BY perf)ORDER BY perf";
$result = mysql_query($sql) or die($sql . '<br />' . mysql_error());
//will print the actual query as submitted as well as the error message if the query fails
//get into the habit of coding it this way as it makes debugging so much easier

//also, I'm working in 3 different dbms, each with it's own dialect of sql, so I can not guarantee that I have the correct syntax, 
//or that the subquery as coded is even possible in mysql. 
//An alternative would be
$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";

laugharne

Thanks- tied up at moment(not literally) trying to move house and loads of hassle- not that this is relevant. Will try and report back. Sure not what U want on the board, but rude not to reply- appreciate your help.
Charles

Roger_Ramjet

Thanks is assumed: but always appreciated. What we really do like to see though is people who mark their threads as RESOLVED when they are. Use the link at the bottom of the thread to do this.

laugharne

Still struggling

$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">athfname</td>
<td>athsurnam</td>
<td>club</td>
<td>perf</td>
<td>date</td>
<td>meeting</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);

Get error:-Parse error: parse error, unexpected '{' in /home/sc010/domains/athleticsresults.co.uk/public_html/athperformances4.php on line 40

Charles

Roger_Ramjet

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);

knowing what line 40 is would help here

laugharne

Line 40 was

while ($row=mysql_fetch_array($sql) {

Charles

Roger_Ramjet

At a guess, you have an open while/if/for structure earlier in the script that encloses the first echo, so php is surprised by the while.

One point, you should stick to the single quote form when formatting your echos, makes the \ unneccessary to escape for the html.
You should also familiarise yourself with the heredoc syntax: http://uk2.php.net/language.types.string

Roger_Ramjet

Originally posted by laugharne
Line 40 was

while ($row=mysql_fetch_array($sql) {

Charles

while ($row=mysql_fetch_array($sql)) {

missed the closing )

laugharne

Thanks- seems to be working now.

Charles