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)) &#123;

	printf("<tr>
				<td>$the_color</td></tr>\n",
	
	$the_color = $Row&#1111;'the_color']);

&#125;
	
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.