3
\$\begingroup\$

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):

<?php

// 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(*)
  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 "6":
            $rating6 = $rating['COUNT(*)'];
            break;
          case "5":
            $rating5 = $rating['COUNT(*)'];
            break;
          case "4":
            $rating4 = $rating['COUNT(*)'];
            break;
          case "3":
            $rating3 = $rating['COUNT(*)'];
            break;
          case "2":
            $rating2 = $rating['COUNT(*)'];
            break;
          case "1":
            $rating1 = $rating['COUNT(*)'];
            break;
          case "0":
            $rating0 = $rating['COUNT(*)'];
            break;
        }
      }

Here is when it gets quite repetitive. What I'm doing is displaying a graph, where the mode (the rating value with the most votes given) is width=100%. Then every other ratings' width is relative to that. In this example, "Bad" or 1/7 is the one with the most votes given, and is therefore 100%. Therefore $key in this case is 1, and the case "1" block runs.

enter image description here

      // since some ratings might be missing, I initialised all
      // ratings = 0. Now I need to check which rating is the mode

      $dataArray = array($rating0,$rating1,$rating2,$rating3,$rating4,$rating5,$rating6,$rating7);
      $maxValue = max($dataArray);
      if ($maxValue === 0){
        $maxValue = 1; // prevent division by zero error
      }

      $key = array_search($maxValue, $dataArray);

      if ($key === false){
        $key = -1;
      }

      // transform each to relative widths. Max value = 100% width

      switch($key){
        case "7":
          $rating7width = 1;
          $rating6width = $rating6/$maxValue;
          $rating5width = $rating5/$maxValue;
          $rating4width = $rating4/$maxValue;
          $rating3width = $rating3/$maxValue;
          $rating2width = $rating2/$maxValue;
          $rating1width = $rating1/$maxValue;
          $rating0width = $rating0/$maxValue;
          break;
        case "6":
          $rating7width = $rating7/$maxValue;
          $rating6width = 1;
          $rating5width = $rating5/$maxValue;
          $rating4width = $rating4/$maxValue;
          $rating3width = $rating3/$maxValue;
          $rating2width = $rating2/$maxValue;
          $rating1width = $rating1/$maxValue;
          $rating0width = $rating0/$maxValue;
          break;
        case "5":
          $rating7width = $rating7/$maxValue;
          $rating6width = $rating6/$maxValue;
          $rating5width = 1;
          $rating4width = $rating4/$maxValue;
          $rating3width = $rating3/$maxValue;
          $rating2width = $rating2/$maxValue;
          $rating1width = $rating1/$maxValue;
          $rating0width = $rating0/$maxValue;
          break;
        case "4":
          $rating7width = $rating7/$maxValue;
          $rating6width = $rating6/$maxValue;
          $rating5width = $rating5/$maxValue;
          $rating4width = 1;
          $rating3width = $rating3/$maxValue;
          $rating2width = $rating2/$maxValue;
          $rating1width = $rating1/$maxValue;
          $rating0width = $rating0/$maxValue;
          break;
        case "3":
          $rating7width = $rating7/$maxValue;
          $rating6width = $rating6/$maxValue;
          $rating5width = $rating5/$maxValue;
          $rating4width = $rating4/$maxValue;
          $rating3width = 1;
          $rating2width = $rating2/$maxValue;
          $rating1width = $rating1/$maxValue;
          $rating0width = $rating0/$maxValue;
          break;
        case "2":
          $rating7width = $rating7/$maxValue;
          $rating6width = $rating6/$maxValue;
          $rating5width = $rating5/$maxValue;
          $rating4width = $rating4/$maxValue;
          $rating3width = $rating3/$maxValue;
          $rating2width = 1;
          $rating1width = $rating1/$maxValue;
          $rating0width = $rating0/$maxValue;
          break;
        case "1":
          $rating7width = $rating7/$maxValue;
          $rating6width = $rating6/$maxValue;
          $rating5width = $rating5/$maxValue;
          $rating4width = $rating4/$maxValue;
          $rating3width = $rating3/$maxValue;
          $rating2width = $rating2/$maxValue;
          $rating1width = 1;
          $rating0width = $rating0/$maxValue;
          break;
        case "0":
          $rating7width = $rating7/$maxValue;
          $rating6width = $rating6/$maxValue;
          $rating5width = $rating5/$maxValue;
          $rating4width = $rating4/$maxValue;
          $rating3width = $rating3/$maxValue;
          $rating2width = $rating2/$maxValue;
          $rating1width = $rating1/$maxValue;
          $rating0width = 1;
          break;
        default:
          $rating7width = $rating7/$maxValue;
          $rating6width = $rating6/$maxValue;
          $rating5width = $rating5/$maxValue;
          $rating4width = $rating4/$maxValue;
          $rating3width = $rating3/$maxValue;
          $rating2width = $rating2/$maxValue;
          $rating1width = $rating1/$maxValue;
          $rating0width = $rating0/$maxValue;
       }

Finally, I render the HTML:

      <div class="graph">

          <div class="row">
            <div class="col-sm-2"><span class="graph-text">Amazing</span></div>
            <div class="col-sm-10 col-fixed-height">
              <div class="matrix-inner" id="matrix<?php echo $item['id']; ?>-inner7" style="background-color:#BE6EC8"><?php if ($rating7 !== 0){ echo $rating7; } ?></div>
            </div>
          </div>
          <div class="row">
            <div class="col-sm-2"><span class="graph-text">Great</span></div>
            <div class="col-sm-10 col-fixed-height">
              <div class="matrix-inner" id="matrix<?php echo $item['id']; ?>-inner6" style="background-color:#A77FD3"><?php if ($rating6 !== 0){ echo $rating6; } ?></div>
            </div>
          </div>
          <div class="row">
            <div class="col-sm-2"><span class="graph-text">Good</span></div>
            <div class="col-sm-10 col-fixed-height">
              <div class="matrix-inner" id="matrix<?php echo $item['id']; ?>-inner5" style="background-color:#8892dd"><?php if ($rating5 !== 0){ echo $rating5; } ?></div>
            </div>
          </div>
          <div class="row">
            <div class="col-sm-2"><span class="graph-text">Decent</span></div>
            <div class="col-sm-10 col-fixed-height">
              <div class="matrix-inner" id="matrix<?php echo $item['id']; ?>-inner4" style="background-color:#8eb9e5"><?php if ($rating4 !== 0){ echo $rating4; } ?></div>
            </div>
          </div>
          <div class="row">
            <div class="col-sm-2"><span class="graph-text">Meh</span></div>
            <div class="col-sm-10 col-fixed-height">
              <div class="matrix-inner" id="matrix<?php echo $item['id']; ?>-inner3" style="background-color:#7FC2D6"><?php if ($rating3 !== 0){ echo $rating3; } ?></div>
            </div>
          </div>
          <div class="row">
            <div class="col-sm-2"><span class="graph-text">Poor</span></div>
            <div class="col-sm-10 col-fixed-height">
              <div class="matrix-inner" id="matrix<?php echo $item['id']; ?>-inner2" style="background-color:#8CD2D2"><?php if ($rating2 !== 0){ echo $rating2; } ?></div>
            </div>
          </div>
          <div class="row">
            <div class="col-sm-2"><span class="graph-text">Bad</span></div>
            <div class="col-sm-10 col-fixed-height">
              <div class="matrix-inner" id="matrix<?php echo $item['id']; ?>-inner1" style="background-color:#A9DAC4"><?php if ($rating1 !== 0){ echo $rating1; } ?></div>
            </div>
          </div>
          <div class="row">
            <div class="col-sm-2"><span class="graph-text">Awful</span></div>
            <div class="col-sm-10 col-fixed-height">
              <div class="matrix-inner" id="matrix<?php echo $item['id']; ?>-inner0" style="background-color:#BBD6BD"><?php if ($rating0 !== 0){ echo $rating0; } ?></div>
            </div>
          </div>

      </div>

I have JavaScript to animate the widths to go from 0 to to their specified width %, but that is out of the scope here and I'm fine with the JS I've got.

This is all working fine, but it's repetitive and I'd like to clean it up.

\$\endgroup\$
4
  • \$\begingroup\$ Please clarify this: I think I understand that foreach($showcase as $item): covers all the following posted code, including HTML part (so you display a complete graph for each id. Correct? \$\endgroup\$ – cFreed Feb 28 '16 at 0:05
  • \$\begingroup\$ @cFreed yep that's correct. \$\endgroup\$ – user3040456 Feb 28 '16 at 0:06
  • \$\begingroup\$ Thanks. Another question: where the computed $rating...widths are supposed to be used? \$\endgroup\$ – cFreed Feb 28 '16 at 0:13
  • \$\begingroup\$ Those little circles that can be clicked have them stored as data-total. puu.sh/nnSii/b9916f2b43.png \$\endgroup\$ – user3040456 Feb 28 '16 at 0:14
2
\$\begingroup\$

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 <= 7; $i++) {
    $ratingWidths[$i] =
      round(100 * ($i == $key ? 1 : $ratingCounts[$key] / $maxCount));
  }

  // generate circles
?>
  <div class="col-sm-6">
<?php
  foreach ($ratingCounts as $rank => $width) {
?>
    <div data-toggle="tooltip" id="rating<?php echo $rank . '-' . $item['id']; ?>"
      data-value="<?php echo $rank; ?>" title="<?php echo $titles[$rank]; ?>"
      class="rating-circle" style="border-color:#<?php echo $colors[$rank]; ?>"
      data-total="<?php echo $width; ?>"></div>
<?php
  }
?>
  </div>
<?php

  // generate graph
?>
  <div class="graph">
<?php
  foreach ($ratingCounts as $rank => $count) {
    $baseId = 'matrix' . $item['id'] . '-inner';
?>
    <div class="row">
      <div class="col-sm-2">
        <span class="graph-text"><?php echo $titles[$rank]; ?></span>
      </div>
      <div class="col-sm-10 col-fixed-height">
        <div class="matrix-inner" id="<?php echo $baseId . $rank; ?>"
          style="background-color:#<?php echo $colors[$rank]; ?>">
          <?php echo $count ? $count : NULL; ?>
        </div>
      </div>
    </div>
<?php
  }
?>
  </div>
}
\$\endgroup\$
5
  • \$\begingroup\$ Nice! Only issue is, this isn't computing relative widths with max at 100% anymore... will have to take a look at the code further now \$\endgroup\$ – user3040456 Feb 28 '16 at 2:33
  • \$\begingroup\$ There were some initial errors in your code - 1. One of the colors in the array has the #, 2. there were two $$ before $ratingCounts. I went ahead and fixed those errors, then I made some modifications and got it working however. \$\endgroup\$ – user3040456 Feb 28 '16 at 3:07
  • \$\begingroup\$ @user3040456 I edited my answer regarding the # and $$errors, and also added missing <?php before "// generate graph". In the other hand, I don't understand what is the issue with relative width 100%: it should be taken in account by the "1" branch of the ternary operation ($i == $key ? 1 : $ratingCounts[$key] / $maxCount). BTW, might you condiser to accept my answer? TIA. \$\endgroup\$ – cFreed Feb 28 '16 at 10:24
  • \$\begingroup\$ Your code was generating data-total to be the number of votes given for the rating, I think. Maybe I implemented it incorrectly. I made several modifications that fixed the issue though. And I've never used ternary operations before, so I'm not too sure about that! \$\endgroup\$ – user3040456 Feb 28 '16 at 11:43
  • \$\begingroup\$ @user3040456 Thanks for the precision about 100% issue. Since I'm pretty sure the ternary I proposed is quite equivalent of what you originally coded, and since you're not sure to have exactly implemented it so, I hardly recommend you try it: you'd be very satisfied to have this "tool" available for the future. In the other hand, thank you for accepting my answer. \$\endgroup\$ – cFreed Feb 28 '16 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.