how to define a button to run a different MySQL query view of a datatable

cpshart · February 2022

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

I want to use a datatables button to toggle between two different MySQL queries to produce a different view of the datatable.

so the default view would be created by say the MySQL view below

v_income_byexdivdate

and when pressing the button I should like to toggle between the two queries, or if not possible define two buttons to submit the appropriate query view.

If using the toggle option could the text on the button alternate between say forecast and declared dependent upon the query.

v_income_byexdivdate_f

so I need to be able to define the button to submit the appropriate query and I am not sure if this is possible

myTable.button().add(0, {
            text: 'forecast',
            action: function (e, dt, button, config) {
                //some action to resubmit query ????
            }

this is an extract of the client code

...
<?php

<table id="income-by-exdivdate" class="display compact nowrap" style="width:100%">
<thead>
<tr>
<th>Portfolio</th>
<th>Symbol</th>
<th>Name</th>
<th>Status</th>
<th>ExDate</th>
<th>PayDate</th>
<th>Dividend</th>
<th>EQuantity</th>
<th>EPayment</th>
<th>ECumulative</th>
<th>FQuantity</th>
<th>FPayment</th>
</tr>
</thead>
    
<tbody>     
<?php   
global $wpdb;    
global $current_user;
wp_get_current_user();
$user_id = $current_user->ID;
... 

$rows = $wpdb->get_results("
SELECT * FROM v_income_byexdivdate_f
WHERE 
user_id = $user_id
AND
paydate >= CURDATE()
ORDER BY paydate
");
    
foreach ($rows as $row ){
    echo "<tr>";
    echo "<td>$row->portfolio</td>";
    echo "<td>$row->symbol</td>";
    echo "<td>$row->name</td>";
    echo "<td>$row->status</td>";
    echo "<td>$row->edate</td>";
    echo "<td>$row->paydate</td>";
    echo "<td>$row->dividend</td>";
etc ...
}
echo "</table>";

Let me know if you need access to my system to replicate or investigate the problem, I can send you a PM with access details with thanks.

Best

Colin

rf1234 · February 2022

Looks like you are not using Editor. How do you submit your current query?

cpshart · February 2022

I am not using the editor in this instance, I have plenty of scripts where I use the editor successfully in my system, but this is a report where no editing is required.

My system is constructed within a wordpress environment.

The query is submitted using a wordpress command
wpdb::get_results( string $query = null, string $output = OBJECT )
$rows = $wpdb->get_results("MySQL Query");

within a PHP script, here is the reference

https://developer.wordpress.org/reference/classes/wpdb/get_results/

I have cut the script down to the minimum to demonstrate the problem below, and is my attempt at coding it ..

My problem is on submitting the button how to return the correct value say 'forecast' or 'declared' from the submit of the button and force the re submission of the query.

code extract code excluding library calls

<html>
<head>
<title>Test Income By ExDiv Date Version 1.00</title>
    
...

</head>

<table id="income-by-exdivdate" class="display compact nowrap" style="width:100%">
<thead>
<tr>
<th>Portfolio</th>
<th>Symbol</th>
<th>Name</th>
<th>Status</th>
<th>ExDate</th>
<th>PayDate</th>
<th>Dividend</th>
</tr>
</thead>

    
<tbody>     
<?php   
global $wpdb;    
global $current_user;
wp_get_current_user();
$user_id = $current_user->ID;
        
if ($button != 'forecast') {
    
$rows = $wpdb->get_results("
SELECT * FROM v_income_byexdivdate
WHERE 
user_id = $user_id
AND
paydate >= CURDATE()
ORDER BY paydate
");
    
} else {
    
$rows = $wpdb->get_results("
SELECT * FROM v_income_byexdivdate_f
WHERE 
user_id = $user_id
AND
paydate >= CURDATE()
ORDER BY paydate
");
}
    
    
foreach ($rows as $row ){
    echo "<tr>";
    echo "<td>$row->portfolio</td>";
    echo "<td>$row->symbol</td>";
    echo "<td>$row->name</td>";
    echo "<td>$row->status</td>";
    echo "<td>$row->edate</td>";
    echo "<td>$row->paydate</td>";
    echo "<td>$row->dividend</td>";
echo "</tr>";
}
echo "</table>";
        
?>
    
<input type='hidden' id='passuserid' value='<?php echo $current_user->ID; ?>'>
    
<script type="text/javascript">
(function($) {
var $button = 'declared';
    
  $(document).ready(function(){
var myTable =   $('#income-by-exdivdate').DataTable( {
        ordering: true,
        dom: "lBfrtipP",
        "scrollY":      true,
        "scrollX":      true,
        
        ordering: true,
        order:          [[ 0, 'asc' ]],     
        
} );
      
myTable.button().add(0, {
            text: $button,
            action: function (e, dt, button, config) {              
                if ( $button == 'declared' ) {
                    let $button = 'forecast'
                    return $button
                } else {                    
                    let $button = 'declared'
                    return $button
                }
            }
});   
      
}); 
}(jQuery));</script>

I hope this makes sense

Thanks Colin

rf1234 · February 2022

Sorry, I don't know how Datatables interacts with Wordpress. I have never used PHP at the front end, only server side.

In "my world" I would submit an ajax call to the server which returns the needed values to the client. The button would also execute a reload of the table.

Here is an example from my coding. This button copys a selected contract server side and then executes an ajax reload to load the copied contract into the front end.

//custom button to copy a contract with its elements and cash flows
$.fn.dataTable.ext.buttons.copyContract = {
    //only enabled when one row is selected (like edit / delete)
    extend: 'selectedSingle', //alternative would be 'selected' (multiple rows)
    text: copyContractLabel,
    className: "copyContractButton",
    action: function ( e, dt, button, config ) {            
        var selected = dt.row( {selected: true} );
        if (selected.any()) {
            $.busyLoadFull("show");
            $.ajax({
                type: "POST",
                url: 'actions.php?action=copyContract',
                async: true,
                data: {
                    contractId: selected.data().contract.id
                },
                success: function () {                        
                    dt.rows({ selected: false }).nodes().to$().removeClass('hidden');
                    dt.rows({ selected: true }).deselect();            
                    dt.buttons('showAllButton:name').nodes().addClass('hidden');
                    ajaxReloadTbls( [dt] );
                    $.busyLoadFull("hide");
                }
            });
        }
    } 
};

Maybe the Word Press community can answer your question better?!

cpshart · February 2022

Hi rf1234

Many Thanks for your example, I can easily modify my code to integrate your functionality and by changing my system to server side script.

I may attempt to get the client side working as well as I have made some progress, but both will be very useful for a number of my scripts.

It also means that I can reduce menu options, by using buttons to launch different report queries on the same webpage.

Best

Colin

how to define a button to run a different MySQL query view of a datatable

how to define a button to run a different MySQL query view of a datatable

Answers

Howdy, Stranger!

Categories

DataTables

how to define a button to run a different MySQL query view of a datatable

how to define a button to run a different MySQL query view of a datatable

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables