Work with mySQL database and php pages

Hello Motiv-8!

While I'm not associated with MDBootstrap's staff, I do have a lot of experience using MDB Pro as I'm currently using it in production on my company website, control panel, all of which runs MySQL and embedded SQLite.

It's important to remember that MDBootstrap is a Design Framework. It doesn't do a lot of things automatically.

To get datatables working with server sided generated data, you need both JavaScript and PHP, since they don't speak with each other so to say, you need to convert the data that you get from MySQL to JSON, through either returning or echoing out json_decode($yourData);

Here's an example we use in production using SLIM Framework v3 for our API :

<?php
// Setup
use \Psr\Http\Message\ResponseInterface as Response;
use \Psr\Http\Message\ServerRequestInterface as Request;

/**
 * Get transactions done by a user
 *
 * @param  \Psr\Http\Message\ServerRequestInterface $req  PSR7 request
 * @param  \Psr\Http\Message\ResponseInterface      $res  PSR7 response
 * @param  array                                    $args Route parameters
 *
 * @return \Psr\Http\Message\ResponseInterface
 */
$api->get('/v1/transactions/{id:[0-9]+}', function (Request $request, Response $response, array $args) {
    $id = $args['id'];

    if (!is_numeric($id)) {
        return $response->withStatus(400);
    }

    // Get the users transactions
    $transactions = $this->db->prepare('SELECT * FROM transactions WHERE userId = ?');
    $transactions->execute(array($id));

    // Return any response (even empty) as JSON
    return $response->withJson($transactions->fetchAll(PDO::FETCH_ASSOC));
});
?>

When ran, the returned data looks similar to this:

[{
  "id": 58,
  "ref": "6eee48f9-f88c-44f0-ba20-e337ef587a49",
  "uuid": "ec87c215-e2c2-4d46-871c-b810d8e85130",
  "status": "COMPLETED",
  "transactionDate": "2019-01-13 23:52:14",
  "transactionUpdate": "2019-02-18 10:10:33",
  "amount": 87500,
  "userId": "e5d9fbb3-8916-4b6b-8aee-030f70553de0",
  "cardNumber": "123456XXXXXX1234",
  "productId": 4,
  "env": "live",
}, {
  "id": 58,
  "ref": "6eee48f9-f88c-44f0-ba20-e337ef587a49",
  "uuid": "ec87c215-e2c2-4d46-871c-b810d8e85130",
  "status": "COMPLETED",
  "transactionDate": "2019-01-13 23:52:14",
  "transactionUpdate": "2019-02-18 10:10:33",
  "amount": 87500,
  "userId": "e5d9fbb3-8916-4b6b-8aee-030f70553de0",
  "cardNumber": "123456XXXXXX1234",
  "productId": 4,
  "env": "live",
}, ... ]

On the client side, you structure the data with dataTables. There are a lot of guides on datatables.net on how to do this, here's an example on how we inject the data into datatables. Given the following datatable example:

<div class="table-wrapper">
  <table class="table table-hover mb-0 table-responsive table-responsive-sm" cellspacing="0" width="100%" id="transactions">
    <thead>
      <tr>
        <th><a>ID</a></th>
        <th><a>Price</a></th>
        <th><a>Product ID</a></th>
        <th class="th-lg"><a>Purchase Date</a></th>
        <th class="th-lg"><a>Card Number</a></th>
        <th><a>Status</a></th>
      </tr>
    </thead>
    <tbody></tbody>
  </table>
</div>

We use the following JavaScript snippet to read the returned MySQL data and structure it within the datatable. Notice how the amount of variables we're pushing to the table.row.add([]) array equals the amount of headers we have in the datatable HTML:

const $userId = ' ... ';
$.get(`https://api.example.com/v1/transactions/${$userId}`, (data) => {
  if (data && data.length > 0) {
    // Store a reference to the datatable
    const table = $('#transactions').dataTables({ ... });

    // Removes all data already in the datatable
    table.clear();

    // Parse the data into the datatable
    data.forEach((entry) => {
      table.row.add([
        entry.id,
        entry.amount,
        entry.productId,
        entry.transactionDate,
        entry.cardNumber,
        entry.status
      ]);
    });
  }
}).fail(); // Handle HTTP errors

I hope this example can help you on your way. :)