Input field feeding mysql HAVING clause of a raw function

carrarachristophe

Hello,

I am feeding a Datatable with a mysql SELECT raw function.

<?php
// Include config file
require_once "config.php";

// Attempt select query execution
$sql = "
SELECT
....
GROUP BY 
    patrimoine_instruments.`instrument`
HAVING 
    (
        quantite <> 0 
    AND 
        patrimoine_transactions.datetime <= '2024-12-31'
    )
";
if($result = $pdo->query($sql)){
if($result->rowCount() > 0){
while($row = $result->fetch()){     ?>

I am trying to replace '2024-12-31' by the value of an input field that I included above the datatable:

<h3 class="pull-left">Mon patrimoine au <input type="date" id="max-date" value="<?php echo date('Y-m-d'); ?>" data-date-format="dd-mm-yyyy"></h3>
<table id="patrimoines" class="display compact cell-border nowrap" width="100%">

Knowing that the datatable would have to reload everytime the value changes.

Did anyone implement something similar to that?

kthorngren

Not sure I fully understand what you are asking for. It sounds like you want to send the input value to the server to filter the results for the Datatable. If this is the case then use ajax.data as a function, like this example, to send the value as a parameter. Get the parameter in your server script to use in the SQL query.

Kevin

carrarachristophe

Hi Kevin,
Thank you for your suggestion but I am not sure how I can use it for my need.
I had a look to https://datatables.net/forums/discussion/comment/72061/#Comment_72041 and https://www.w3schools.com/php/php_mysql_prepared_statements.asp but am not sure it is the right track.

kthorngren

Sorry, I guess I don't understand your requirements. Please provide more details about your solution, requirements and the problem you are trying to solve.

Kevin

allan

I interpreted your question the same way Kevin has. Use ajax.data to get the value from your input element, and then use that as part of your query.

Is the problem that you don't know how to use the value as part of the query? For that, you should refer to the PHP PDO documentation for binding values. You'd get the value from $_REQUEST.

Allan

rf1234

In Kevin's example https://datatables.net/examples/server_side/custom_vars.html
I can't see what is done with the custom variable on the server side.

$_POST["myKey"] doesn't show up in the server script.

I use "ajax.data" all the time to send extra variables to the server. On the server they are present in the $_POST array accordingly and I can use them wherever I like.

Here is a simple example.

I send the variable "startDate" to the server:

I use the variable in my Editor "where" clause like this:

You don't need Editor server side to make this work.

I am trying to replace '2024-12-31' by the value of an input field that I included above the datatable:

Same in my example
$('#periodSelected').val()
is the value of an input field which is above the data table. The function "nMonthsAgo" converts the selected period into a valid date.

allan

DataTables Ajax request is GET by default, so $_POST['myKey'] would indeed be undefined. $_GET['myKey'] or $_REQUEST['myKey'] on the other hand should be present and work.

Allan

rf1234

This is confusing. The Data Tables "ajax" option is GET by default and the Editor "ajax" option is POST by default.
https://editor.datatables.net/reference/option/ajax
https://datatables.net/reference/option/ajax

I never really noticed it until now. But now I understand why I need type: 'POST' above and I don't need it using Editor. This has always been confusing me.

$_GET['myKey'] or $_REQUEST['myKey'] on the other hand should be present and work.

Not in the example I quoted above! Hence I don't find the example very helpful.

allan

The Data Tables "ajax" option is GET by default and the Editor "ajax" option is POST by default.

Correct.

DataTables is normally getting data, and Editor is normally writing it, so yes, I totally get that it could be confusing, but I think it is correct.

Good point about the example not actually using the data that is being sent - I was thinking that it appearing in the network connection would be enough, but I'll see if I can think something better up!

Allan

rf1234

DataTables is normally getting data, and Editor is normally writing it, so yes, I totally get that it could be confusing, but I think it is correct.

I never thought about it that way, to be honest. And my code doesn't really reflect it either.

I am usually sending data to the server using the "ajax.data" option of the data table, not the "ajax.data" option of Editor. And it has always been working. Weird.

But maybe it isn't that contradictory at all ... Apart from Editor I am only sending data to the server
- to facilitate data selection (in this case additional parameters for reading data with server side processing which requires server side filtering that is otherwise done on the client side). For this I use "ajax.data" on the Data Table side.
- to do other things that Editor can't handle. For that I use proprietary ajax calls and not Editor's "ajax.data".
Still confused -

Like in here:

Should I have the "ajax.data" option rather in the Editor instance instead of the Data Table?

Good point about the example not actually using the data that is being sent - I was thinking that it appearing in the network connection would be enough, but I'll see if I can think something better up!

Thanks! That would be helpful!

kthorngren

but I'll see if I can think something better up!

Maybe have an Ajax Request tab like the Editor examples and point out in the comments to see the request data in the tab.

I've pointed this example out to a lot of people. Many aren't using server side processing andI point out that serverSide: true is not required. If you are going to work on this example please add some verbiage around not requiring server side processing.

I don't recall anyone mentioning the server script not using the parameter. But it might be good to filter by the Office or something for a more complete example.

Kevin

kthorngren

Should I have the "ajax.data" option rather in the Editor instance instead of the Data Table?

Probably not. I'm assuming you. are using those parameters to filter the data fetched by Datatables. The Editor code probably will have no need.

From a REST API perspective GET is used to read data while technically POST is used for creating data. The default for Editor is to POST edits as well. Unless you do something like this Rest example. If you aren't concerned about the REST spec then stay with what you are using.

Kevin

rf1234

Probably not. I'm assuming you. are using those parameters to filter the data fetched by Datatables. The Editor code probably will have no need.

We had the same idea simultaneously (See my updated post above)

Thanks Kevin!

allan

Excellent ideas as always gentlemen - thank you!

I've committed a change to have an input box do a filter on the office column, and also highlight that serverSide is not required for use of ajax.data.

I'll deploy the update to the site in the next few days.

Allan