Using MySql Views in PHP reports

[rpierce]

Is there a trick to using a MySql VIEW in a php report. I've experimented but had no luck. I would think you could use a view just like a table in php, but that doesn't seem to be the case. It seems that things would be a lot simpler if I could do the query in PhpMyAdmin, save it as a view and call for the fields from that view.

[pbottcher]

Hi,
of course you can do that. If you just want to use the view in a php, you can do that. If you want to use the generated TV you can create a table in AppGini with all the fields needed and then generate your App. Next, delete the table with phpmyadmin or equivalent and create your view.

[Alisson]

This i what I use for reports: Create a file in the hooks folder named "reports.php"
Add the code below and change the query to your desired database table:

Code: Select all

<link href="https://unpkg.com/[email protected]/dist/css/tabulator.min.css" rel="stylesheet">
<script type="text/javascript" src="https://unpkg.com/[email protected]/dist/js/tabulator.min.js"></script>
<?php
    define('PREPEND_PATH', '../');
    $hooks_dir = dirname(__FILE__);
    include("$hooks_dir/../lib.php");
    include("$hooks_dir/../header.php");
     
    /* grant access to all logged users */
    $mi = getMemberInfo();
    
    if(!$mi['username'] || $mi['username'] == 'guest'){
        echo "Access denied";
        exit;
    }

    $query = sql("SELECT * FROM `yourTableNameHere`", $eo); //Change this line to use your desired table
    
    $json_array = array();  
        while($row = mysqli_fetch_assoc($query))  
        {  
            $json_array[] = $row;
        }  
    $json = json_encode($json_array, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK);
?>

<div id="table"></div>
<script>
  var tabledata = <?php echo $json;?>;

  var table = new Tabulator("#table", {
      data:tabledata,           //load row data from array
      layout:"fitColumns",      //fit columns to width of table
      responsiveLayout:"hide",  //hide columns that dont fit on the table
      pagination:"local",       //paginate the data
      paginationSize:7,         //allow 7 rows per page of data
      movableColumns:true,      //allow column order to be changed
      resizableRows:true,       //allow row order to be changed
      // groupBy:"dept",       //allow row order to be changed
      autoColumns:true, //create columns from data field names
  });

</script>

After that you can customize it the way you want.

More information here: http://tabulator.info/examples/5.1

[rpierce]

Thank you Alisson, I will experiment.