I've decided to put something in here as it took me an age to find out how I could do it.

This is when using a MySQL query within a PHP script.  The process is used often to do a statistics table or top ten chart of your data (eg. movies, music, etc).

My aim is to do the following:

1. retrieve data from a table,
2. count the number of times each data exists,
3. sort it in reverse order so that the most frequent is at the top of the list
4. print out each row with the number of times that particular data appeared in a row

My old method was to:

1. Select distinct column1.table1 FROM table1
2. PHP script would loop through all rows of the above mysql query
3. Count for each row how many times the column1.table1 value appears in table1
4. Format this count result by prefixing with leading zeros (for sorting purposes - eg. so 10 is not before 2)
5. Store both the formatted count and the value of column1.table in the next array entry
6. Reverse this array
7. Display the first 10 rows of this array (if we are doing a top ten)

My new method is:

1. SELECT column1, COUNT(*) AS CountOrder FROM table1 GROUP BY column1 ORDER BY CountOrder DESC LIMIT 0,10
2. PHP script to loop through the results of the above query
3. Display the value of column1 and CountOrder

The old code would have been:

echo '# | Search Term | Count' . "\n";

# create an array of search terms with their frequency count
$search_table_results=mysql_query("SELECT DISTINCT column1 FROM table1");
while($row=mysql_fetch_assoc($search_table_results)) {
	$this_value=trim($row['column1']);
	$this_value_num=mysql_num_rows(mysql_query("SELECT column1 FROM table1 WHERE column1='$this_value'")); 
	if (($this_value!="") && ($this_value_num>5)) {
		$this_value_num=sprintf("%010d",$this_value_num);
		$fresh_array[]=$this_value_num.'|'.$this_value;
	}
}

rsort($fresh_array);

for ($i=0;$i<10;$i++) {
	$temp_array=array();
	$temp_array=explode("|", $fresh_array[$i]);
	$this_value=$temp_array[1];
	$this_value_num=$temp_array[0]*1;
	echo ($i+1).' | '.$this_value.' | '.$this_value_num . "\n";
}

The new code is just:

echo '# | Search Term | Count' . "\n";

$num_records=0; $num_results=mysql_query("SELECT column1, COUNT(*) AS CountOrder FROM table1 GROUP BY column1 ORDER BY CountOrder DESC LIMIT 0,10");
while($row=mysql_fetch_assoc($num_results)) {
	$num_records++; 
	$this_value=trim($row['column1']);
	$this_value_num=$row['CountOrder']*1;
	echo $num_records.' | '.$this_value.' | '.$this_value_num . "\n";
}

Obviously method 2 is unsurprisingly quicker, both for you and the server...