Implementing jQuery DataTables PHP lookups

Another approach to secondary lookups is to have the frontend generate the request. In this recipe, we will make a slight modification to the secondary lookup code presented in the preceding recipe, Embedding secondary lookups into QueryResults. In the previous recipe, even though the view logic is performing the lookup, all processing is still done on the server. When using jQuery DataTables, however, the secondary lookup is actually performed directly by the client, in the form of an Asynchronous JavaScript and XML (AJAX) request issued by the browser.

How to do it...

First we need to spin-off the secondary lookup logic (discussed in the recipe above) into a separate PHP file. The purpose of this new script is to perform the secondary lookup and return a JSON array.
The new script we will call chap_05_jquery_datatables_php_lookups_ajax.php. It looks for a $_GET parameter, id. Notice that the SELECT statement is very specific as to which columns are delivered. You will also note that the fetch mode has been changed to PDO::FETCH_NUM. You might also notice that the last line takes the results and assigns it to a data key in a JSON-encoded array.
Tip
It is extremely important when dealing with zero configuration jQuery DataTables to only return the exact number of columns matching the header.
```
$id  = $_GET['id'] ?? 0;
sql = 'SELECT u.transaction,u.date, u.quantity,u.sale_price,r.title '
   . 'FROM purchases AS u '
   . 'JOIN products AS r '
   . 'ON u.product_id = r.id '
   . 'WHERE u.customer_id = :id';
$stmt = $conn->pdo->prepare($sql);
$stmt->execute(['id' => (int) $id]);
$results = array();
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
  $results[] = $row;
}
echo json_encode(['data' => $results]); 
```

Next, we need to modify the function that retrieves customer information by ID, removing the secondary lookup embedded in the previous recipe:

function findCustomerById($id, Connection $conn)
{
  $stmt = $conn->pdo->query(
    'SELECT * FROM customer WHERE id = ' . (int) $id);
  $results = $stmt->fetch(PDO::FETCH_ASSOC);
  return $results;
}

After that, in the view logic, we import the minimum jQuery, DataTables, and stylesheets for a zero configuration implementation. At a minimum, you will need jQuery itself (in this example jquery-1.12.0.min.js) and DataTables (jquery.dataTables.js). We've also added a convenient stylesheet associated with DataTables, jquery.dataTables.css:

<!DOCTYPE html>
<head>
  <script src="https://code.jquery.com/jquery-1.12.0.min.js">
  </script>
    <script type="text/javascript" 
      charset="utf8" 
      src="//cdn.datatables.net/1.10.11/js/jquery.dataTables.js">
    </script>
  <link rel="stylesheet" 
    type="text/css" 
    href="//cdn.datatables.net/1.10.11/css/jquery.dataTables.css">
</head>

We then define a jQuery document ready function, which associates a table with DataTables. In this case, we assign an id attribute of customerTable to the table element that will be assigned to DataTables. You'll also notice that we specify the AJAX data source as the script defined in step 1, chap_05_jquery_datatables_php_lookups_ajax.php. As we have the $id available, this is appended to the data source URL:
```
<script>
$(document).ready(function() {
  $('#customerTable').DataTable(
    { "ajax": '/chap_05_jquery_datatables_php_lookups_ajax.php?id=<?= $id ?>' 
  });
} );
</script>
```

In the body of the view logic, we define the table, making sure the id attribute matches the one specified in the preceding code. We also need to define headers that will match the data presented in response to the AJAX request:

<table id="customerTable" class="display" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th>Transaction</th>
      <th>Date</th>
      <th>Qty</th>
      <th>Price</th>
      <th>Product</th>
    </tr>
  </thead>
</table>

Now, all that remains to do is to load the page, choose the customer ID (in this case, at random), and let jQuery make the request for the secondary lookup.

How it works...

Create a chap_05_jquery_datatables_php_lookups_ajax.php script, which will respond to an AJAX request. Inside, place the code to initialize auto-loading and create a Connection instance. You can then append the code shown in step 2 of the preceding recipe:

<?php
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);

Next, create a chap_05_jquery_datatables_php_lookups.php calling program that will pull information on a random customer. Add the function described in step 3 of the preceding code:

<?php
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);
// add function findCustomerById() here
$id     = random_int(1,79);
$result = findCustomerById($id, $conn);
?>

The calling program will also contain the view logic that imports the minimum JavaScript to implement jQuery DataTables. You can add the code shown in step 3 of the preceding code. Then, add the document ready function and the display logic shown in steps 5 and 6. Here is the output:

There's more...

For more information on jQuery, please visit their website at https://jquery.com/. To read about the DataTables plugin to jQuery, refer to this article at https://www.datatables.net/. Zero configuration data tables are discussed at https://datatables.net/examples/basic_init/zero_configuration.html. For more information on AJAX sourced data, have a look at https://datatables.net/examples/data_sources/ajax.html.

Previous Chapter

Embedding secondary lookups into query results

Next Chapter

6. Building Scalable Websites

Table of Contents for PHP 7: Real World Application Development

Implementing jQuery DataTables PHP lookups

How to do it...

Tip

How it works...

There's more...

Table of Contents for
PHP 7: Real World Application Development