Page 1 of 1
ordering results from db
Posted: Tue Jun 11, 2002 11:18 am
by jmandango
Okay, How can I sort results from a mySql table using PHP so that multiple fields are grouped and then sorted from largest group to smallest.
this is what the results would look like:
----------------------
colors
----------
red
red
red
red
blue
blue
green
Posted: Tue Jun 11, 2002 11:59 am
by Wandrer
have you tried using 'group by' or 'order by' in your SQL statement ?
Posted: Tue Jun 11, 2002 12:17 pm
by jmandango
Code: Select all
<html>
<body>
<?php
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("mydb");
$result = mysql_query("SELECT the_color, COUNT(the_color) FROM the_table ORDER BY MAX;");
echo "<table border=1 class=graybody>";
echo "<tr bgcolor=999999><td>The Color</td></tr>";
while ($Row = mysql_fetch_array($result)) {
printf("<tr>
<td>$the_color</td></tr>\n",
$the_color = $Rowї'the_color']);
}
echo "</table>\n";
?>
</body>
</html>
Here is my code. I have several fields in the table that will have a variety of different entries. I want to be able to click on each field header on a page which will then do a query which will group the results of the list (i.e. put all the reds together) and then put them in descending order. (the most reds, followed by blues, greens, etc.) I might even want to have the number of each color in a table next to each color (i.e. red - 5, blue - 3, green - 1) Help please, I'm stuck.