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
cpshart
Posts: 246Questions: 49Answers: 5
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
This question has accepted answers - jump to:
Answers
Looks like you are not using Editor. How do you submit your current query?
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>
";
<?php > ID; ?>'> (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)); ``` I hope this makes sense ?>Thanks Colin
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.
Maybe the Word Press community can answer your question better?!
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