Query grouping and sorting question...

[ozolinse]

Greetings all... I have a quick question and I'll explain it to the best of my ability in hopes of obtaining a quick answer.

 

I have a mysql database for the website which logs 'fish catches' for an online fishing tournament I run. The original person who wrote the code is no longer able to help due to family issues thus I am trying to learn as much as I can.

 

- I have tables for species, anglers, images, submissions (submit), regions, and teams.

- Under the species table are relevant columns species_id (numerical number in system just for organization), species_name (name of fish species ex. Tuna), and a few others for region/location etc.

- Under the submit table are columns length, submit_id, angler, team_id, species_id, date, image...water...etc

 

Since this particular page we are discussing is the 'all-time records' page, the script was 'grouped' and joined with the query. My issue is the current records are fully working like always have been but are being displayed in order of species_id where as I need it to be displayed by species_name in alphabetical order instead.

 

Here is part of the current code which I need to change and can't figure it out to get it to display in order of species_name... I've learned quite a bit of php but have not come familar with grouping and joining.

 

Any help would be greatly appreciated. I can provide the online address of the page for reference if needed.

 

function get_species()  {
    global $species_id;
    global $speciesname;

    $query_a = "SELECT species_name
        FROM species
        WHERE species_id='$species_id'";
      $results_a = mysql_query($query_a) or die(mysql_error());
    $row_a = mysql_fetch_array($results_a);
    extract ($row_a);
    $speciesname = $species_name;
}

      //Query for records region 1
      $records1 = "SELECT
                       angler
                       ,team_id
                       ,dt.species_id
                       ,length
                       ,image
                 ,yyyy
                 ,mm
                 ,dd
                       FROM submit
                         join (SELECT
                                  species_id, max(length) as mlength
                               FROM submit
                                WHERE submit.region_id = 1
                                GROUP BY species_id
                               ) dt
                           ON (submit.species_id,submit.length) = (dt.species_id,dt.mlength)
                        WHERE submit.region_id = 1
                        ORDER BY submit.species_id
                                  ";
      $records_result1 = mysql_query($records1) or die(mysql_error());


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

    $length = $row['length'];
    $species_id = $row['species_id'];
    $angler = $row['angler'];
    $image = $row['image'];
    $yyyy = $row['yyyy'];
    $dd = $row['dd'];
    $mm = $row['mm'];
     

       //associate names with proper id numbers
    get_angler_handle($angler);
    get_species($species_id);

 

[Jessica]

ORDER BY submit.species_id

Change that.

[ozolinse]

That was the first thing I tried before posting... I changed that to   ORDER BY submit.species_name  and also tried ORDER BY species_name   

and with both different changes I get an error that pops up on the page like

"Unknown column 'submit.species_name' in 'order clause''

[kicken]

You need to join to the species table in order to gain access to the name column.

SELECT

	angler

	,team_id

	,dt.species_id

	,length

	,image

	,yyyy

	,mm

	,dd

FROM submit

join (SELECT

	  species_id, max(length) as mlength

       FROM submit

	WHERE submit.region_id = 1

	GROUP BY species_id

       ) dt

   ON (submit.species_id,submit.length) = (dt.species_id,dt.mlength)

INNER JOIN species ON species.species_id=submit.species_id

WHERE submit.region_id = 1

ORDER BY species.species_name

[mikosiko]

Kichen's answer is pointing you in the right direction, with that most likely your $ query_a seems to dont be needed; also will be interesting to see what the 2 last functions get_angler_handle() and get_species () are used for..if those are only getting the angler name and the specie name then they could be not needed with kichen's solution... same as the variable $speciesname what it is used for?

[Jessica]

That's what I get for reading so late at night.

[ozolinse]

Thanks Kicken... you were spot on!   Thanks for the help regarding the join command!