GET SQL data from table as JSON into Bootstrap Table script-CodePudding

Due to some function like Excel and PDF download i want to implement some script on my site. I have found a solution at bootstrap-table.com I failed to retrieve the table data as they are a json source (data-url) in the script. My data for the table are available in a SQL database. I have tried to create a query and create a output in json format but i failed. I a m asking for help on this point.

This is the base code i have found:

<link href="https://unpkg.com/[email protected]/dist/bootstrap-table.min.css" rel="stylesheet">

<script src="https://cdn.jsdelivr.net/npm/[email protected]/tableExport.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/libs/jsPDF/jspdf.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/libs/jsPDF-AutoTable/jspdf.plugin.autotable.js"></script>
<script src="https://unpkg.com/[email protected]/dist/bootstrap-table.min.js"></script>
<script src="https://unpkg.com/[email protected]/dist/extensions/export/bootstrap-table-export.min.js"></script>

<style>
#toolbar {
  margin: 0;
}
</style>

<div id="toolbar" >
  <select >
    <option value="">Export Basic</option>
    <option value="all">Export All</option>
    <option value="selected">Export Selected</option>
  </select>
</div>

<table id="table"
  data-show-export="true"
  data-pagination="true"
  data-side-pagination="server"
  data-click-to-select="true"
  data-toolbar="#toolbar"
  data-show-toggle="true"
  data-show-columns="true"
  data-url="https://examples.wenzhixin.net.cn/examples/bootstrap_table/data">
</table>

<script>
  var $table = $('#table')

  $(function() {
    $('#toolbar').find('select').change(function () {
      $table.bootstrapTable('destroy').bootstrapTable({
        exportDataType: $(this).val(),
        exportTypes: ['json', 'xml', 'csv', 'txt', 'sql', 'excel', 'pdf'],
        columns: [
          {
            field: 'state',
            checkbox: true,
            visible: $(this).val() === 'selected'
          },
          {
            field: 'id',
            title: 'ID'
          }, {
            field: 'name',
            title: 'Item Name'
          }, {
            field: 'price',
            title: 'Item Price'
          }
        ]
      })
    }).trigger('change')
  })
</script>

My problem is as explained that my data are available in my SQL database. So i retrieve my data like:

$statement = $pdo->prepare("SELECT id, vorname, rolle FROM users WHERE cid = :cid");
$result = $statement->execute(array(':cid' => $user['cid']));
$users = $statement->fetch();

I thought to change the data-url from this data-url="https://examples.wenzhixin.net.cn/examples/bootstrap_table/data" to this data-url="<?=json_encode($users)?> will work but i get no data shown in my table.

Any idea to get this working correctly by using my PDO Query?

CodePudding user response：

I belive this would soulve your problem, first of all set the headers for your page as json, this should come before any output on the page:

header('Content-type: application/json; charset=utf-8');

Then fetch your users just like you normally would.

$statement = $pdo->prepare("SELECT id, vorname, rolle FROM users WHERE cid = :cid");
$result = $statement->execute(array(':cid' => $user['cid']));
$users = $statement->fetch();

And finnaly echo that result:

echo $users

So in summary, your script should look like this:

<?php
   header('Content-type: application/json; charset=utf-8');

   $statement = $pdo->prepare("SELECT id, vorname, rolle FROM users WHERE cid = :cid");
   $result = $statement->execute(array(':cid' => $user['cid']));
   $users = $statement->fetch();

   echo json_encode($users);
?>

FINAL EDIT

Create a new PHP file, with the content in the code above, i dont know how your URL is structured, but you should be able to access this file via a url for example http://yoursite.com/users/jsondata, if you need to pass parameters to alter the result you could use query parameter for instance http://yoursite.com/users/jsondata?minprice=2&maxprice=10, and alter your data accordingly. When you visit this url you should be able to to see your data structured similarly to what you have here: https://examples.wenzhixin.net.cn/examples/bootstrap_table/data

Then in your table you can have:

<table id="table"
  ...
  data-url="http://yoursite.com/users/jsondata<?=($_GET['minprice'] ? '?minprice=' . $_GET['minprice'] : '').($_GET['maxprice'] ? '&maxprice=' . $_GET['maxprice'] : '')?>">
</table>

There's actually a whole lot more you could do to make your code even more elegant but this should give you a head start.

EDIT

After looking at the example link you provided, I had to come up with this.

$users = $statement->fetch();
$data = [
   'total' => count($users),
   'totalNotFiltered' => count($users),
   'rows' => $users,
];
echo json_encode($data, JSON_FORCE_OBJECT);

I believe this should work.