Sorting data

[bddotnet]

I have a table of data in a MySQL database. It is a list of horses that qualified in a certain class. Classes can have more than one entry.

If I have this:

Horse Owner Sire Dam Class
Horse1 Owner1 Sire1 Dam1 Class1
Horse2 Owner2 Sire2 Dam2 Class2
Horse3 Owner3 Sire3 Dam3 Class1
Horse4 Owner4 Sire4 Dam4 Class3
Horse5 Owner5 Sire5 Dam5 Class2
Horse6 Owner6 Sire6 Dam6 Class2

How would the query work to output the list so that it is grouped by class? (i.e. all the Class 1 horses are listed alphabetically, Class 2 horses then listed alphabetically, etc. separated by class)?

Thanks!

[HalfaBee]

SELECT column_name(s)
FROM table_name
ORDER BY column1, column2

[BerksWebGuy]

Also check out "GROUP BY".

[HalfaBee]

Does group by sort it?

[BerksWebGuy]

No. If you want to return all of the rows in order, ORDER BY would be the best method. GROUP BY is more of an aggregate function where you can results per group (how many in class1, etc).

[bddotnet]

Thanks guys! I wound up using the first suggestion, sorta.

http://www.okfqhr.com/?pg=entries

Here's what I did:


<?php
## DB CONNECTION & SELECT ###############################################
include "connection library information";
$connect=mysql_connect($database,$username,$passwo rd);
$result=mysql_select_db($database) or die( "This database is not working");
################################################## #######################

## TOTAL ROWS ################################################## ########
$searchresult = mysql_query("SELECT * FROM tbl_name WHERE criteria_name = '2009' ");
$totalrows = mysql_num_rows($searchresult);
################################################## ######################
?>

<?php

//build the table
echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\" width=96%>";

while ($data = mysql_fetch_array($searchresult)) {

// ADD THIS LINE TO SEARCH FOR ENTRIES MATCHING CRITERIA
$foundit++;

//build the table row
echo "<tr>";

//Class Name and Column Headings
if(!empty($data[class_name])) {
echo "<tr>
<td class=sectionheader colspan=5 align=center>$data[class_name]</td>
</tr>
<tr>
<td align=center><strong>Horse</strong></td>
<td align=center><strong>Owner</strong></td>
<td align=center><strong>Sire</strong></td>
<td align=center><strong>Dam</strong></td>
</tr>
";
} else echo ("");

## ECHO HORSES QUALIFIED FOR EACH CLASS #################################
$id = "$data[ID]";
$searchhorses = mysql_query("SELECT * FROM `tbl_name` WHERE ID = '$id' ORDER BY horse_name ASC");

//echo the horses that qualified in each class
echo "</tr>";
echo "<tr>";

while ($horses = mysql_fetch_array($searchhorses)) {

//Horse Name
echo "<td>$horses[horse_name]</td>";

// Owner
echo "<td>$horses[owner]</td>";

//Sire
echo "<td>$horses[sire]</td>";

//Dam
echo "<td>$horses[dam]</td>";

} // end of horse list for each class

echo "</td>";
echo "</tr>";
}

// end of class results
echo "</table>";

##// ADD THIS LINE TO ECHO A STATEMENT IF THERE ARE NO RESULTS###########
if (!$foundit) { echo '<BR><BR><center>There are no results available at this time. Please check back later.</center>'; }
################################################## ########################

?>