[SOLVED] MySQL order by

[james19]

Hi,

i am wondering if there is a way of getting the data from a mysql database in order of how many records there are with a certain field value. (e.g. title='Hello World')

[revraz]

SELECT * FROM table WHERE fieldvalue = 'hello world';

[james19]

thanks, but i am trying to gather data to generate a Google Chart pie chart, and for each level, it selects a colour from a list. Currently it orders the selection by the title, but instead i would rather it order by the number of records with a specific title, so the largest percentage of the pie chart is always the same colour. Currently this is what i have:

 

		$location_query = mysql_query("SELECT title FROM ".TABLE." ORDER BY title ASC");		
	while ($location_row = mysql_fetch_array($location_query, MYSQL_NUM)) {
		if ($location_row[0]!=$prev_location_result) {
			switch ($location_type_no) {
				case 1:
					$vals_ChartColors .= COLOUR1;
					break;
				case 2:
					$vals_ChartColors .= COLOUR2;
					break;
				case 3:
					$vals_ChartColors .= COLOUR3;
					break;
			}
			$location_type_no = $location_type_no + 1;

			$result = mysql_query("SELECT COUNT(id) AS numrows FROM ".TABLE." WHERE title='{$location_row[0]}'");
			$row = mysql_fetch_array($result, MYSQL_NUM);
			$location_percentage = round(($row[0] / $total_stats)*100, 2);
			if ($location_percentage < 1) {
				$other_ChartData = $other_ChartData + $location_percentage;
			} else {
				$vals_ChartData .= $location_percentage.',';
				$vals_Labels .= $location_row[0].' ('.$location_percentage.'%) | ';
			}

			$prev_location_result = $location_row[0];
		}
	}

 

It display the results in alphabetical order, not number of records (%), so the colour of each section can tend to vary.

 

Any ideas?

[revraz]

Sounds like GROUP BY may be a good idea here.

[james19]

cool - thanks