Displaying a multi-dimensional array and accumulating totals

How to properly display data from a multi-dimensional array has been a classic problem for any web developer. For illustration, assume you wish to display a list of customers and their purchases. For each customer, you wish to show their name, phone number, account balance, and so on. This already represents a two dimensional array where the x axis represents customers and the y axis represents data for that customer. Now add in purchases and you have a third axis! How can you represent a 3D model on a 2D screen? One possible solution would be to incorporate "hidden" division tags with a simple JavaScript visibility toggle.

How to do it...

First we need to generate a 3D array from a SQL statement that uses a number of JOIN clauses. We will use the Application/Database/Connection class introduced in Chapter 1, Building a Foundation, to formulate an appropriate SQL query. We leave two parameters open, min and max, in order to support pagination. Unfortunately, we cannot use a simple LIMIT and OFFSET in this case, as the number of rows will vary depending on the number of purchases for any given customer. Accordingly, we can restrict the number of rows by placing restrictions on the customer ID that presumably (hopefully) is incremental. To make this work properly, we also need to set the primary ORDER to customer ID:
```
define('ITEMS_PER_PAGE', 6);
define('SUBROWS_PER_PAGE', 6);
define('DB_CONFIG_FILE', '/../config/db.config.php');
include __DIR__ . '/../Application/Database/Connection.php';
use Application\Database\Connection;
$conn = new Connection(include __DIR__ . DB_CONFIG_FILE);
$sql  = 'SELECT c.id,c.name,c.balance,c.email,f.phone, '
  . 'u.transaction,u.date,u.quantity,u.sale_price,r.title '
  . 'FROM customer AS c '
  . 'JOIN profile AS f '
  . 'ON f.id = c.id '
  . 'JOIN purchases AS u '
  . 'ON u.customer_id = c.id '
  . 'JOIN products AS r '
  . 'ON u.product_id = r.id '
  . 'WHERE c.id >= :min AND c.id < :max '
  . 'ORDER BY c.id ASC, u.date DESC ';
```
Next we can implement a form of pagination, based on restrictions on the customer ID, using simple $_GET parameters. Note that we add an extra check to make sure the value of $prev does not go below zero. You might consider adding another control that ensures the value of $next does not go beyond the last customer ID. In this illustration, we just allow it to increment:
```
$page = $_GET['page'] ?? 1;
$page = (int) $page;
$next = $page + 1;
$prev = $page - 1;
$prev = ($prev >= 0) ? $prev : 0;
```

We then calculate the values for $min and $max, and prepare and execute the SQL statement:

$min  = $prev * ITEMS_PER_PAGE;
$max  = $page * ITEMS_PER_PAGE;
$stmt = $conn->pdo->prepare($sql);
$stmt->execute(['min' => $min, 'max' => $max]);

A while() loop can be used to fetch results. We use a simple fetch mode of PDO::FETCH_ASSOC for the purpose of this example. Using the customer ID as a key, we store basic customer information as array parameters. We then store an array of purchase information in a sub-array, $results[$key]['purchases'][]. When the customer ID changes, it's a signal to store the same information for the next customer. Note that we accumulate totals per customer in an array key total:

$custId = 0;
$result = array();
$grandTotal = 0.0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  if ($row['id'] != $custId) {
    $custId = $row['id'];
    $result[$custId] = [
      'name'    => $row['name'],
      'balance' => $row['balance'],
      'email'   => $row['email'],
      'phone'   => $row['phone'],
    ];
    $result[$custId]['total'] = 0;
  }
  $result[$custId]['purchases'][] = [
    'transaction' => $row['transaction'],
    'date'        => $row['date'],
    'quantity'    => $row['quantity'],
    'sale_price'  => $row['sale_price'],
    'title'       => $row['title'],
  ];
  $result[$custId]['total'] += $row['sale_price'];
  $grandTotal += $row['sale_price'];
}
?>

Next we implement the view logic. First, we start with a block that displays primary customer information:

<div class="container">
<?php foreach ($result as $key => $data) : ?>
<div class="mainLeft color0">
    <?= $data['name'] ?> [<?= $key ?>]
</div>
<div class="mainRight">
  <div class="row">
    <div class="left">Balance</div>
           <div class="right"><?= $data['balance']; ?></div>
  </div>
  <div class="row">
    <div class="left color2">Email</div>
           <div class="right"><?= $data['email']; ?></div>
  </div>
  <div class="row">
    <div class="left">Phone</div>
           <div class="right"><?= $data['phone']; ?></div>
    </div>
  <div class="row">
        <div class="left color2">Total Purchases</div>
    <div class="right">
<?= number_format($data['total'],2); ?>
</div>
  </div>

Next comes the logic to display a list of purchases for this customer:

<!-- Purchases Info -->
<table>
  <tr>
  <th>Transaction</th><th>Date</th><th>Qty</th>
   <th>Price</th><th>Product</th>
  </tr>
  <?php $count  = 0; ?>
  <?php foreach ($data['purchases'] as $purchase) : ?>
  <?php $class = ($count++ & 01) ? 'color1' : 'color2'; ?>
  <tr>
  <td class="<?= $class ?>"><?= $purchase['transaction'] ?></td>
  <td class="<?= $class ?>"><?= $purchase['date'] ?></td>
  <td class="<?= $class ?>"><?= $purchase['quantity'] ?></td>
  <td class="<?= $class ?>"><?= $purchase['sale_price'] ?></td>
  <td class="<?= $class ?>"><?= $purchase['title'] ?></td>
  </tr>
  <?php endforeach; ?>
</table>

For the purposes of pagination, we then add buttons to represent previous and next:

<?php endforeach; ?>
<div class="container">
  <a href="?page=<?= $prev ?>">
        <input type="button" value="Previous"></a>
  <a href="?page=<?= $next ?>">
        <input type="button" value="Next" class="buttonRight"></a>
</div>
<div class="clearRow"></div>
</div>

The result so far, unfortunately, is nowhere near neat and tidy! Accordingly we add a simple JavaScript function to toggle the visibility of a <div> tag based on its id attribute:

<script type="text/javascript">
function showOrHide(id) {
  var div = document.getElementById(id);
  div.style.display = div.style.display == "none" ? "block" : "none";
}
</script>

Next we wrap the purchases table inside an initially invisible <div> tag. Then, we can place a limit of how many sub-rows are initially visible, and add a link that reveals the remaining purchase data:

<div class="row" id="<?= 'purchase' . $key ?>" style="display:none;">
  <table>
    <tr>
      <th>Transaction</th><th>Date</th><th>Qty</th>
                 <th>Price</th><th>Product</th>
    </tr>
  <?php $count  = 0; ?>
  <?php $first  = TRUE; ?>
  <?php foreach ($data['purchases'] as $purchase) : ?>
    <?php if ($count > SUBROWS_PER_PAGE && $first) : ?>
    <?php     $first = FALSE; ?>
    <?php     $subId = 'subrow' . $key; ?>
    </table>
    <a href="#" onClick="showOrHide('<?= $subId ?>')">More</a>
    <div id="<?= $subId ?>" style="display:none;">
    <table>
    <?php endif; ?>
  <?php $class = ($count++ & 01) ? 'color1' : 'color2'; ?>
  <tr>
  <td class="<?= $class ?>"><?= $purchase['transaction'] ?></td>
  <td class="<?= $class ?>"><?= $purchase['date'] ?></td>
  <td class="<?= $class ?>"><?= $purchase['quantity'] ?></td>
  <td class="<?= $class ?>"><?= $purchase['sale_price'] ?></td>
  <td class="<?= $class ?>"><?= $purchase['title'] ?></td>
  </tr>
  <?php endforeach; ?>
  </table>
  <?php if (!$first) : ?></div><?php endif; ?>
</div>

We then add a button that, when clicked, reveals the hidden <div> tag:

<input type="button" value="Purchases" class="buttonRight" 
    onClick="showOrHide('<?= 'purchase' . $key ?>')">

How it works...

Place the code described in steps 1 to 5 into a file, chap_10_html_table_multi_array_hidden.php.

Just inside the while() loop, add the following:

printf('%6s : %20s : %8s : %20s' . PHP_EOL, 
    $row['id'], $row['name'], $row['transaction'], $row['title']);

Just after the while() loop, add an exit command. Here is the output:

You will notice that the basic customer information, such as the ID and name, repeats for each result row, but purchase information, such as transaction and product title, varies. Go ahead and remove the printf() statement.

Replace the exit command with the following:

echo '<pre>', var_dump($result), '</pre>'; exit;

Here is how the newly composed 3D array looks:

You can now add the display logic shown in steps 5 to 7. As mentioned, although you are now showing all data, the visual display is not helpful. Now go ahead and add the refinements mentioned in the remaining steps. Here is how the initial output might appear:

When the Purchases button is clicked, initial purchase info appears. If the link to More is clicked, the remaining purchase information shows:

Previous Chapter

Implementing a search engine

Next Chapter

11. Implementing Software Design Patterns

Table of Contents for PHP 7: Real World Application Development

Displaying a multi-dimensional array and accumulating totals

How to do it...

How it works...

Table of Contents for
PHP 7: Real World Application Development