Displaying a 0-7 ratings graph

I store ratings in a table called ratings.

The table has these columns:


id | user_id | value | ip | showcase_id

value is an integer between 0 and 7 (the value of the rating). i.e. there is 8 different ratings to pick from. They are, in English:

• 7/7 = Amazing

• 6/7 = Great

• 5/7 = Good

• 4/7 = Decent

• 3/7 = Meh

• 2/7 = Poor

• 1/7 = Bad

• 0/7 = Awful

What I'm doing is selecting 12 items (for now):

prepare(
 "SELECT s.id,s.date,s.title,s.views,s.image,s.hidpi,u.display_name,u.avatar
  FROM showcase AS s
  INNER JOIN users AS u
  ON s.user_id = u.id
  LIMIT 12
  ");
$stmt->execute();
$showcase = $stmt->fetchAll();

// prepare to get the number of ratings given for each value 0-7 for each item

$ratingsQuery1 = $db->prepare(
 "SELECT value,COUNT(*)
  FROM ratings
  WHERE showcase_id = :showcase_id
  GROUP BY value
 ");

// prepare the average value and total number of ratings for an item

$ratingsQuery2 = $db->prepare(
 "SELECT AVG(value) as average, COUNT(*) as total
  FROM ratings
  WHERE showcase_id = :showcase_id
 ");

Then, I loop through each item and execute the two prepared queries based on them:

```
foreach($showcase as $item):

$rating0 = $rating1 = $rating2 = $rating3 = $rating4 = $rating5 = $rating6 = $rating7 = 0;

$ratingsQuery1->bindParam(":showcase_id",$item['id']);
$ratingsQuery1->execute();
$ratings = $ratingsQuery1->fetchAll();

$ratingsQuery2->bindParam(":showcase_id",$item['id']);
$ratingsQuery2->execute();
$ratingsInfo = $ratingsQuery2->fetchAll();

$average = $ratingsInfo[0]['average'];
$total = $ratingsInfo[0]['total'];

// here I loop through the ratings (0-7) and use a switch
// statement to check if the current item in the loop is
// either 0-7, since there are gaps in them

foreach ($ratings as $rating){
switch ($rating['value']){
case "7":
$rating7 = $rating['COUNT(*)'];
break;
case

You can dramatically reduce your code in several ways.

First you have to replace the eight $rating... variables by an array like $rating_counts, and similarly the eight $rating...Width variables by an array like $rating_widths.
Then there are three major possible improvements:

• When assigining counts to their associated values, you can replace the whole switch ($rating['value']){ by a simple (one-line body) foreach().

• When computing widths, you can replace the whole switch($key){ by a for() loop, and here again it ends in a one-line body.

• HTML code generation can be replaced by two foreach() loops, one for the circles part (as added by your comment), the other for the graph.

With the above changes, your whole code (including the HTML circles part) becomes like this:

// constant values
$titles = ['Amazing', 'Great', 'Good', 'Decent', 'Meh', 'Poor', 'Bad', 'Awful'];
$colors = ['BE6EC8', 'A77FD3', '8892dd', '8eb9e5', '7FC2D6', '8CD2D2', 'A9DAC4', 'BBD6BD'];

// select 12 items
$stmt = $db->prepare('
SELECT s.id, s.date, s.title, s.views, s.image, s.hidpi, u.display_name, u.avatar
FROM showcase AS s
  INNER JOIN users AS u ON s.user_id = u.id
LIMIT 12
');
$stmt->execute();
$showcase = $stmt->fetchAll();

// prepare to get the number of ratings given for each value 0-7 for each item
$ratingsQuery1 = $db->prepare('
SELECT value, COUNT(*) as `count`
FROM ratings
WHERE showcase_id = :showcase_id
GROUP BY value
');

// prepare the average value and total number of ratings for an item
$ratingsQuery2 = $db->prepare('
SELECT AVG(value) as `average`, COUNT(*) as `total`
FROM ratings
WHERE showcase_id = :showcase_id
');

foreach ($showcase as $item) {

  // get counts
  $ratingsQuery1->bindParam(":showcase_id",$item['id']);
  $ratingsQuery1->execute();
  $ratings = $ratingsQuery1->fetchAll();

  // get average and total
  $ratingsQuery2->bindParam(":showcase_id",$item['id']);
  $ratingsQuery2->execute();
  $ratingsInfo = $ratingsQuery2->fetchAll();
  $average = $ratingsInfo[0]['average'];
  $total = $ratingsInfo[0]['total'];

  // assign counts depending on their value
  $ratingCounts = array_fill(0, 8, 0);
  foreach ($ratings as $rating) {
    $ratingCounts[$rating['value']] = $rating['count'];
  }

  // compute mode
  $maxCount = max($ratingCounts);
  if ($maxCount === 0){
    $maxCount = 1; // prevent division by zero error
  }
  $key = array_search($maxCount, $ratingCounts);
  if ($key === FALSE) {
    $key = -1;
  }

  // compute widths by value. Max value = 100% width
  for ($i = 0; $i 
  
 $width) {
?>
    "
      data-value="" title=""
      class="rating-circle" style="border-color:#"
      data-total="">

  

  
 $count) {
    $baseId = 'matrix' . $item['id'] . '-inner';
?>
    
      
        
      
      
        "
          style="background-color:#">
          
        
      
    

  
}

StackExchange Code Review Q#121322, answer score: 2