Creating a Top 10 chart with less code

: Category: Personal Home Page; 18 February 2010; Hits: 17480

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:

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

My old method was to:

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

My new method is:

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

The old code would have been:

copy raw

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";
}

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:

copy raw

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";
}

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";
}