The Assignment:

When I was hired at Audioproz they had a static website that consisted of a gray background with a header in a red heavy duty font that said Audioproz.com and beneath that was a single gray table that listed a couple hundred random items from inventory. This was a long time ago, and at this point I had never really done a full database driven website, but what they had was so non intimidating that I figured I should volunteer to revamp the website, as they did hire me to be the ‘computer’ guy. The owner was really touchy about it however and held the original designer of the site in very high esteem and did not want me to touch it.

Meanwhile, whenever they wanted me to add an item to the website, or mark an item sold, I had to go into this messy html table and find a needle in a haystack (it was not in any kind of order), and let’s just say I decided to disobey a direct order and replace the ungodly table with a dynamically generated table that drew it’s information from a database so that items could be entered or edited relatively easily at one point and displayed in any order desired. That would be a huge improvement.

There was only two problems. A) I had never done it before, I was still making websites in Dreamweaver. B) I probably shouldn’t tell the boss until long after it has been done and making everyone’s life a lot easier. So I enlisted my cousin Dave to whip me up a basic site that looked like the old site, but had a little cms and a MySql backend. Then I just played around with it until I figured out how it worked and that was my first experience with PHP and MySql.

As the site grew and I got bolder and bolder with the refinements and expansions my boss figured out what I had done. I explained all the advantages of having a data driven website and showed him the increased amount of traffic we were getting and he finally trusted me enough to build Audioproz v2.0 (which was still built on a lot of the original architecture of Dave’s site). Then almost 20 years later I was given the full go ahead to rebuild the site from the ground up.

The current Audioproz site is clean, organized, functional and robust. It has a solid cms for administrating the site and adding content. I developed a fully functional e-commerce system for it with a cart, card processing, and shipping estimator but the owner likes to talk to every customer and wasn’t comfortable with that level of automation.

Audioproz currently has over 10,000 inventoried items in it’s database and gets thousands of hits a day.

So after building the sql query we load up the variables in a controller file.

// Prepare and execute query to get product data
$stmt = $conn->prepare($sql);
$stmt->bind_param("sssssii", $filter, $filter, $filter, $filter, $filter_category, $limit, $offset);
$stmt->execute();
$products = $stmt->get_result();
$stmt->close();

// Prepare and execute query to get adverts
$stmt = $conn->prepare($sql_advert);
$stmt->execute();
$adverts = $stmt->get_result();
$stmt->close();

// Loop through adverts and if category contains match for selected category add advert to $ad_rows
$ad_rows = [];
while ($row = $adverts->fetch_assoc()) {
  $ad_categories = explode(',', $row['category']);
  // Loop through each ad category
  foreach ($ad_categories as $ad_category) {
    // If category is valid (check against config/variables/$valid_advert_categories)
    if (in_array($ad_category, $valid_advert_categories)) {
      // Loop through product categories in config/variables/$valid_advert_subcategories)
      foreach ($valid_advert_subcategories[$ad_category] as $product_category) {
        // If match generate random number and compare to ad priority, if random number lower than ad priority ad row to $ad_rows
        if ($product_category === $selected_category || $selected_category === 'All') {
          $max = ceil(115 - ($limit / 2));
          $rand_num = mt_rand(1, $max);
          $row['random'] = $rand_num . ' of ' . $max;
          if ($ad_category === 'All Categories') {
            if ($selected_category === 'All') {
              $row['priority'] += 10;
            } else {
              $row['priority'] = $row['priority'] / 2;
            }
          } else {
            if ($selected_category === 'All') {
              $row['priority'] = $row['priority'] / 2;
            } else {
              $row['priority'] += 10;
            }
          }
          if ($rand_num < $row['priority']) {
            $ad_rows[] = $row;
          }
        }
      }
    }
  }
}

That bit of code loads up the products array and sprinkles in some ads depending on how things are configured in the cms. Then the template delivers it to the browser in a nice, styled and organized fashion.

    <!-- Main content -->
      <div class="row pt-3">
        <div class="col-12">
          <!-- Show product and insert ad logic -->
          <?php
          $row_counter = 0;
          $ad_counter = 0;
          if ($total_items === 0) {
            echo "<div class='row'>";
            echo "<div class= 'col-12'>";
            echo "<div class='text-center'>Sorry, no products that match search for '" . $_SESSION['search'] . "'.</div>";
            echo "<div class='text-center'>You can contact us to see if we have one available that not listed in our inventory.</div>";
            echo "</div>";
            echo "</div>";
          }
          while ($product = $products->fetch_assoc()) {
            $show_ad = false;
            $row_class = 'product-left';
            $right_class = 'product-right';
            $tagline = '';
            if (($row_counter + $advert_start) % $advert_spacing === 0) {
              $show_ad = true;
              $row_class = 'ad-outer';
            } elseif ($product['price'] === 'SOLD') {
              $row_class = 'sold-left';
              $right_class = 'sold-right';
              $tagline = 'Check for similar item';
            } elseif (ctype_digit($product['price'])) {
              $row_class = 'price-left';
              $right_class = 'price-right';
            } else {
              $tagline = 'Make us an offer';
            }
            $row_counter++;
            if ($show_ad && count($ad_rows) > 0) {
              // wrap around if you run out of ads
              $idx = $ad_counter % count($ad_rows);
              echo "\n\n\n<div class='row'>";
              echo "\n  <div class='col-12 d-flex justify-content-center $row_class'>";
              echo "\n    <div class='ad-container' style='font-size:85%;'>";
              echo $ad_rows[$idx]['advert'];
              echo "\n    </div>";
              echo "\n  </div>";
              echo "\n</div>";
              $ad_counter++;
            }