Creating a Top 10 chart with less code

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:

copyraw
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";
}
  1.  echo '# | Search Term | Count' . "\n"; 
  2.   
  3.  # create an array of search terms with their frequency count 
  4.  $search_table_results=mysql_query("SELECT DISTINCT column1 FROM table1")
  5.  while($row=mysql_fetch_assoc($search_table_results)) { 
  6.      $this_value=trim($row['column1'])
  7.      $this_value_num=mysql_num_rows(mysql_query("SELECT column1 FROM table1 WHERE column1='$this_value'"))
  8.      if (($this_value!="") && ($this_value_num>5)) { 
  9.          $this_value_num=sprintf("%010d",$this_value_num)
  10.          $fresh_array[]=$this_value_num.'|'.$this_value
  11.      } 
  12.  } 
  13.   
  14.  rsort($fresh_array)
  15.   
  16.  for ($i=0;$i<10;$i++) { 
  17.      $temp_array=array()
  18.      $temp_array=explode("|", $fresh_array[$i])
  19.      $this_value=$temp_array[1]
  20.      $this_value_num=$temp_array[0]*1
  21.      echo ($i+1).' | '.$this_value.' | '.$this_value_num . "\n"; 
  22.  } 

 

The new code is just:

copyraw
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";
}
  1.  echo '# | Search Term | Count' . "\n"; 
  2.   
  3.  $num_records=0$num_results=mysql_query("SELECT column1, COUNT(*) as CountOrder FROM table1 GROUP BY column1 ORDER BY CountOrder DESC LIMIT 0,10")
  4.  while($row=mysql_fetch_assoc($num_results)) { 
  5.      $num_records++
  6.      $this_value=trim($row['column1'])
  7.      $this_value_num=$row['CountOrder']*1
  8.      echo $num_records.' | '.$this_value.' | '.$this_value_num . "\n"; 
  9.  } 

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

Category: Personal Home Page :: Article: 242

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

assoc   count   create   rows   entry   data   times   column1   query   code   before   first   number   doing   0,10   loop   terms   display   echo   value   purposes   fetch   zeros   order   while   select   frequency   table   array   desc   mysql   next   group   rsort   distinct   method   would   chart   script   limit   reverse   store   results   countorder   above   been   search   formatted   table1   term   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.