MySQL MAX() usage

[Skipjackrick]

For some reason I am only getting one result when I use the following code.

 

Does anybody know what I am doing wrong?

 

<?php
$querySC = "SELECT 
              species_id, 
              team_id, 
              angler, 
              MAX(length), 
              image
            FROM
              submit
            WHERE
              region_id=1
            GROUP BY
              species_id
           ";

$resultSC = mysql_query($querySC) or die (mysql_error());



if (mysql_num_rows($resultSC) > 0){

while($row = mysql_fetch_array($resultSC))
{
$species_id = $row['species_id'];
$angler = $row['angler'];
$team_id = $row['team_id'];
$length = $row['MAX(length)'];
$image = $row['image'];

//get team's name from team table
get_team_name($team_id);
get_species($species_id);
get_angler_handle($angler);
}
$recordsSC .=<<<EOD
<tr>
	<td align='center'><a href='submitted_pics/$image' onclick='return hs.expand(this)' class='highslide' title='Click here to see the $speciesname'>$speciesname</a></td>
	<td align='center'><a href='anglerprofile.php?anglerid=$angler' title='Find out more about $anglerhandle'>$anglerhandle</a></td>
	<td align='center'><a href='teampage.php?team_idvar=$team_id&team=$teamname' title='Find out more about $teamname'>$teamname</a></td>
	<td align='center'>$length"</td>
</tr>
EOD;

} else {
   echo "";
}

$bonus_footer ="</table>";

$bonus_wars =<<<BONUS
	$bonus_header
	$recordsSC
	$bonus_footer
BONUS;

print $bonus_wars;
?>

[mapleleaf]

Is the row returned the one with the Maximum length in region 1?

Do you want the max(length) per team or angler?

[Skipjackrick]

I want the max length per species_id

[Skipjackrick]

The one row returned is the very last species_id and the length is not the max.

 

Its strange.

[PFMaBiSmAd]

Your code that is building $recordsSC is after and outside of your while(){} loop that is retrieving the data, so of course it will only get the last value retrieved. You would need to put that code inside of the while(){} loop for it to do something with each row that is being retrieved.

 

The $length variable should hold the maximum value for each GROUP BY species_id, but you are aware that is only going to give you the maximum length in each group, the other column values are actually gong to be from the first row in each group. If you are actually trying to get the row in each group that has the the maximum length, you will need to use the techniques at this link - http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

[Skipjackrick]

??  THanks... but still not working for me.  But I think I am moving forward.

 

Here is what I've got.

 

I am not sure what the s1 and s2 are meant for but put them in there???  Any help explaining?

 

<?php
$querySC = "SELECT 
              species_id, 
              team_id, 
              angler, 
              length, 
              image
            FROM
              submit s1
            WHERE
              region_id=1 AND length=(SELECT MAX(s2.length) FROM submit s2 WHERE s1.species_id = s2.species_id)";
           ";

$resultSC = mysql_query($querySC) or die (mysql_error());



if (mysql_num_rows($resultSC) > 0){

while($row = mysql_fetch_array($resultSC))
{
$species_id = $row['species_id'];
$angler = $row['angler'];
$team_id = $row['team_id'];
$length = $row['length'];
$image = $row['image'];

//get team's name from team table
get_team_name($team_id);
get_species($species_id);
get_angler_handle($angler);

$recordsSC .=<<<EOD
<tr>
	<td align='center'><a href='submitted_pics/$image' onclick='return hs.expand(this)' class='highslide' title='Click here to see the $speciesname'>$speciesname</a></td>
	<td align='center'><a href='anglerprofile.php?anglerid=$angler' title='Find out more about $anglerhandle'>$anglerhandle</a></td>
	<td align='center'><a href='teampage.php?team_idvar=$team_id&team=$teamname' title='Find out more about $teamname'>$teamname</a></td>
	<td align='center'>$length"</td>
	<td align='center'><a href='submitted_pics/$image' onclick='return hs.expand(this)' class='highslide' title='Click here to see the Fish'>Image</a></td>
</tr>
EOD;
}
} else {
   echo "";
}

$bonus_footer ="</table>";

$bonus_wars =<<<BONUS
	$bonus_header
	$recordsSC
	$bonus_footer
BONUS;

print $bonus_wars;
?>

[PFMaBiSmAd]

Define: but still not working for me. What is it doing vs what you expect? We are not standing right next to you nor do we have access to your server, your database, or your complete code so you must state what you see in front of you if you expect someone in a forum to be able to help you.

 

Based on the color highlighting in the posted code, you have a php syntax error due to an extra "; in the code.

[Skipjackrick]

Sorry...sometimes i don't realize that you guys can't see what I see... I bet that can be irritating...LOL

 

Ok...

 

So it seems to be working sort of...

 

I am now getting more than one row to show up.  However only 2 anglers are showing up.

 

Here is what is output

 

Species--------------Angler--------------Team--------------Length--------------Image

Red Drum--------PierPressure-------Hawg Wild------------51.00" --------------Image

Yellowfin Tuna--------Oz--------------Rockstar--------------44.00" --------------Image

Cobia (Ling)-----------Oz--------------Rockstar--------------53.00" --------------Image

King Mackerel---------Oz--------------Rockstar--------------55.00" --------------Image

Trout--------------------Oz--------------Rockstar--------------31.50" --------------Image

 

 

However there are several more MAX length with different teams and anglers that aren't being displayed....??

<?php
$querySC = "SELECT 
              species_id, 
              team_id, 
              angler, 
              length, 
              image
            FROM
              submit s1
            WHERE
              region_id=1 AND length=(SELECT MAX(s2.length) FROM submit s2 WHERE s1.species_id = s2.species_id)
           ";

$resultSC = mysql_query($querySC) or die (mysql_error());



if (mysql_num_rows($resultSC) > 0){

while($row = mysql_fetch_array($resultSC))
{
   $species_id = $row['species_id'];
   $angler = $row['angler'];
   $team_id = $row['team_id'];
   $length = $row['length'];
   $image = $row['image'];

//get team's name from team table
   get_team_name($team_id);
   get_species($species_id);
   get_angler_handle($angler);

$recordsSC .=<<<EOD
<tr>
      <td align='center'><a href='submitted_pics/$image' onclick='return hs.expand(this)' class='highslide' title='Click here to see the $speciesname'>$speciesname</a></td>
      <td align='center'><a href='anglerprofile.php?anglerid=$angler' title='Find out more about $anglerhandle'>$anglerhandle</a></td>
      <td align='center'><a href='teampage.php?team_idvar=$team_id&team=$teamname' title='Find out more about $teamname'>$teamname</a></td>
      <td align='center'>$length"</td>
      <td align='center'><a href='submitted_pics/$image' onclick='return hs.expand(this)' class='highslide' title='Click here to see the Fish'>Image</a></td>
   </tr>
EOD;
}
} else {
   echo "";
}

$bonus_footer ="</table>";

$bonus_wars =<<<BONUS
      $bonus_header
      $recordsSC
      $bonus_footer
BONUS;

   print $bonus_wars;
?>