Data picker

antoniocib

Hi guys,
i need to add at my code one data picker by date
in nutshell i nedd to load data from the server by a this input
can i help me for add this?

rf1234

Could you be a little more specific please. Thanks.

Hi Antonio!
I saw in your profile that you are Italian. So you would probably prefer writing your questions and replies in Italian. If so there is a great tool for you: deepl.com
Just give it a try. It is free and I believe much better than Google Translate.
https://www.deepl.com/translator
They even have a great windows app that allows you to use it really swiftly. I'll try and translate this into Italian now.
Best regards
Roland

Ciao Antonio!
Ho visto nel tuo profilo che sei italiano. Quindi probabilmente preferiresti scrivere le tue domande e risposte in italiano. Se è così c'è un grande strumento per te: deepl.com
Provaci. È gratuito e credo molto meglio di Google Translate.
https://www.deepl.com/translator
Hanno anche una grande app per Windows che permette di utilizzarla in modo molto veloce. Ora cercherò di tradurlo in italiano.
Cordiali saluti
Roland

translated by deepl.com

rf1234

Not sure but if you just need the java script code for a date picker and a time picker then this might be useful:

//if you use moment you might also want to enable ordering by date:

var momentLocale = 'it'; //or whichever language you need
$.fn.dataTable.moment( 'L', momentLocale );


}, {
    label: lang === 'de' ? 'Angebotsfrist:' : 'Bid Period End Date:',
    name:  "yourDateField",
    attr: {
        class: 'yourClass' //if any
    },
    type:  "datetime",
    def:   function () { return <someDefaultDateVariable>},
    format: 'L',
    opts:  {
        showWeekNumber: true,
        yearRange: 40,
        momentLocale: momentLocale 
    }
}, {
    label: lang === 'de' ? 'Angebotsfrist Uhrzeit:' : 'Bid Period End Time:',
    name:  "yourTimeField",
    attr: {
        class: 'yourClass' //if any
    },
    type:  "datetime",
    def:   function () { return <someDefaultTimeVariable>},
    format:  'HH:mm'
}, {

I use this with moment.js because I support two languages with different labels and date formats.

antoniocib

Oh that's nice, that's just what I needed to thank you Ronald, anyway I need a date selector that for that date I set only loads me that data in the page and not all the data in the database.

antoniocib

@rf1234 Ronald, on the other hand, in this date picker situation, I really don't understand. Can you help me?

rf1234

Just read the docs please:
https://editor.datatables.net/examples/dates/dates
https://editor.datatables.net/reference/field/datetime
https://momentjs.com/

That is all that you need! If you want more: My examples above show more options than you can find in the example referenced above.

antoniocib

@rf1234 Maybe before I did not explain myself well now I use DeepL, the date picker I need that according to the selected date must load me in the tables the data and in the examples you wrote me there is nothing of this.

rf1234

Well, date pickers don't load data into tables as far as I can tell ... Sorry I am afraid I can't help with that.
If you want to pass a selected date to the server in order to retrieve something take a look at ajax.data in the docs please. Good Luck!

kthorngren

Here is a SO thread showing how to use Ajax to send the selected date to the server. You will need to create a PHP script to retrieve the date from the parameters and build a SQL query for the date range.

Kevin

rf1234

The SO thread Kevin quoted is pretty good! Since you have an Editor license you might want to use Editor to retrieve the data.

I am doing a very similar thing here:
- my "date picker" is just a drop down indicating how many months to go back in time for the selection of expired contracts

Using ajax.data with your data table will send the selected value from your date picker to the server so that you can use it there for your where clause in your PHP Editor instance:
https://datatables.net/reference/option/ajax.data

excerpt from data table definition. "nMonthsAgo" is just a little helper function that determines the date from the number of "months ago" (YYYY-MM-DD format).

.....
ajax: {
    url: 'actions.php?action=myAction',
    type: 'POST',
    data: function ( d ) {   
        d.startDateExpired = nMonthsAgo( $('#monthsSelect').val() );
    }
},

and this is part of the where clause of the PHP editor instance retrieving the data:

............
if ( isset($_POST['startDateExpired']) ) {
    $q  ->where( function ( $r ) { 
        $r  ->where( 'ctr.expired', 0 )
            ->or_where( function ( $s ) { 
                $s  ->where( 'ctr.expired', 1 )
                    ->where( 'ctr.end_date', $_POST['startDateExpired'] . ' 00:00:00', '>='); 
            } );
    } );
}

P.S.: Don't forget the ajax reload of your data table "on change" of the date picker.

antoniocib

@rf1234 I think that I have not explained well now I send you some photos Ronald because what you are talking about is not what I need, it is not a reproach this ahahah, indeed it is only my fault that I do not know your language

In my main page I added this:

With the corresponding code:

<input type="date" id="myDate" value="">
<input type="submit" id="datestart">

And when the button "Invia" is pressed it must load the data in the table, but until the button is pressed and consequently the date has not been sent to the server, the tables must be empty.

Translated with www.DeepL.com/Translator (free version)

kthorngren

until the button is pressed and consequently the date has not been sent to the server, the tables must be empty.

Initialize an empty Datatable, ie, remove the ajax option.

when the button "Invia" is pressed it must load the data in the table

Use a click event for the button to fetch the data via jQuery Ajax similar to what is shown in the SO thread I link to before.

In the success function use clear() to clear the table data, then use rows.add() to add the response data.

Here is a simple example not using the Datepicker:
http://live.datatables.net/xegokuma/12/edit

On your page start with this. Once you get the data loading then add the datepicker.

Kevin

antoniocib

If I knew how to do it Kevin, I'm going crazy that I don't understand anything and every line I have to use the translator, what a nervousness I have

antoniocib

@kthorngren or @rf1234 If you can, and you have some time you can make an example on my code to make my life less difficult, because I don't understand anything, I would be very grateful!

Code: http://damoratraffico.netsons.org/project/scrivania1.html

rf1234

Hi Antonio,

I understand what you need .... not so much different from what I proposed. Depending on how you manipulate the WHERE clause in PHP you can achieve the effect that you need.

How would that work?
If the meaning of the date you are using is "start date" then it is really easy: On initial load you just pass a date to the server like "31/12/2999". The result will be that nothing will be returned from the server: The table is empty as per your request!
When the user picks a date you do an ajax reload with the "picked" start date. That's it. Same WHERE clause etc. Nothing special at all ...

Don't know whether you don't know my language, Antonio! It certainly is NOT English ...

... and when the year 3k problem occurs ("31/12/2999") we won't be around anyway ...

antoniocib

@rf1234
I found a couple of videos on youtube where they were doing this I figured something out I wrote this js code:

$(document).ready(function() {
$.datepicker.setDefaults({
dateFormat:'d/m/Y' 
})
$(function(){
$("#from_date").datepicker();
$("#to_date").datepicker();
});
$('#filter').click(function()
var from_date = $('#from_date').val();
var to_date = $('#to_date').val();
 if(from_date != '' && to_date != '')
  {
    $.ajax({
      url: "php/table.scrivania1.php",
      method: "POST",
      data:{from_date:from_date , to_date:to_date},
      success: function(data)
      {
        var table = $('#scrivania1').DataTable();
        table.clear();
        table.rows.add().draw();
      }
    });
  }
  else {
    alert("Seleziona una Data");
  }
});
} );

But since I use Editor then I don't know how to enter this:

 $query = "
  SELECT * FROM scrivania1
  WHERE data_di_scarico BETWEEN ' ".$_POST["from_date"]."' AND ' " .$_POST["to_date"]. "'
  "; 

and HTML

<input type="text" name="from_date" value="" id="from_date" >
<input type="text" name="to_date" value="" id="to_date" >
<input type="button" name="filter" value="" id="filter">

rf1234

Yep, you don't seem to know what Editor can do for you ... What you post here is “read only“. I use Editor because that is usually not good enough. I want to CRUD, not just to R. Read the docs ...

You say since you are using Editor you don't know how to enter this (SELECT statement). Well it is Editor's purpose to make it redundant for you ... because it generates all of those statements! If it didnt: Why would you use Editor?

antoniocib

@rf1234 I translated what you said Ronald and I don't understand what it means, please can you help me I'm in trouble. I've read entire pages of commands and stuff but I don't know how to do it, I have no idea...

antoniocib

@rf1234 and @kthorngren

i use this library but dont work datepicker..

<script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/dt/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.20/af-2.3.4/b-1.6.1/b-colvis-1.6.1/b-flash-1.6.1/b-html5-1.6.1/b-print-1.6.1/cr-1.5.2/fc-3.3.0/fh-3.1.6/kt-2.5.1/r-2.2.3/rg-1.1.1/rr-1.2.6/sc-2.0.1/sp-1.0.1/sl-1.3.1/datatables.min.js"></script>
        <script   type="text/javascript" charset="utf-8"  src="https://cdn.datatables.net/plug-ins/1.10.20/sorting/datetime-moment.js"></script>
        <script  type="text/javascript" charset="utf-8"  src="https://cdn.datatables.net/plug-ins/1.10.19/dataRender/datetime.js"></script>
    <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>

allan

Here is how I would do it with Editor and its PHP libraries:

In your HTML:

<input type="date" id="startDate" value="">
<input type="date" id="endData" value="">
<input type="submit" id="dateSearch">

In your DataTables configuration (Javascript):

ajax: {
  url: '...whatever...',
  data: function (d) {
    d.startDate = $('#startDate').val();
    d.endDate = $('#endDate').val();
  }
}

Then add a call to ajax.reload() to the button:

$('#dateSearch').on('click', function () {
  table.ajax.reload();
});

And on the server-side (PHP) in the Editor chain:

->where( function ($q) {
  if ($_REQUEST['startDate'] && $_REQUEST['endDate']) {
    $q->where('startColumn', $_REQUEST['startDate'], '>=');
    $q->where('endColumn', $_REQUEST['endDate'], '<=');
  }
  else {
    // Dates not submitted - use a condition that can never be true
    $q->where('startColumn', '0000-00-00');
} )

So what's missing from this? I hadn't shown a date picker, nor is there any validation on the input. Your SQL server will most likely want ISO8601 date input, so with the above your user would need to type in an ISO8601 format and then click submit. Any other format would give an error. Likely you will want to change that, but whatever date picker library you use will probably be able to help with that.

Regards,
Allan

antoniocib

Hi. @allan or evrybody read this,

i try to import this in my project in this way:

PHP:

    ->where( function ($q) {
  if ($_REQUEST['startDate') {
    $q->where('data_di_scarico' = $_REQUEST['startDate']);

  }
 )

    var table = $('#scrivania1').DataTable( {
    //  serverSide: 'true',


        dom: 'PBfrtip',
    //  dom: 'Pfrtip',
        ajax: 'php/table.scrivania1.php',
        order: [5, 'desc'],
        data: function (d) {
        d.startDate = $('#startDate').val();
      },

        columns: [



            {
                "data": "drit"
            },
            {
                "data": "ddt"
            },
            {
                "data": "cliente"
            },
            {
                "data": "carico"
            },

$('#dateSearch').on('click', function () {
  table.ajax.reload();
});

<input type="date" id="startDate" value="">
<input type="submit" id="dateSearch">

But doesnt work, return me this error:

"Cannot retrieve the 'DT_RowId' property of a null or undefined reference"

colin

could you link to the page where this is happening please. That error is normally caused when the fields expected in the table initialisation aren't matched with those in the JSON response.

Colin

rf1234

You syntax looks wrong. The option is "ajax.data" not "data".
https://editor.datatables.net/reference/option/ajax.data

This:

var table = $('#scrivania1').DataTable( {
//  serverSide: 'true',
 
 
    dom: 'PBfrtip',
//  dom: 'Pfrtip',
    ajax: 'php/table.scrivania1.php',
    order: [5, 'desc'],
    data: function (d) {
    d.startDate = $('#startDate').val();
  }, ..............

should rather be this:

var table = $('#scrivania1').DataTable( {
    dom: 'PBfrtip',
    ajax: {
        url: 'php/table.scrivania1.php',
        type: 'POST',
        data: function (d) {
            d.startDate = $('#startDate').val();
         }
    },
    order: [5, 'desc'],
    ............

This would create this super global server side:

$_POST['startDate']

antoniocib

@rf1234
Hi Ronald, i change the script as u told,
but return me tn1 error, and the problem is in datables.min.js i guess

i use this library:

        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/dt/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.20/af-2.3.4/b-1.6.1/b-colvis-1.6.1/b-flash-1.6.1/b-html5-1.6.1/b-print-1.6.1/cr-1.5.2/fc-3.3.0/fh-3.1.6/kt-2.5.1/r-2.2.3/rg-1.1.1/rr-1.2.6/sc-2.0.1/sp-1.0.1/sl-1.3.1/datatables.min.js"></script>
        <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/vfs_fonts.js"></script>
        <script   type="text/javascript" charset="utf-8"  src="https://cdn.datatables.net/plug-ins/1.10.20/sorting/datetime-moment.js"></script>
        <script  type="text/javascript" charset="utf-8"  src="https://cdn.datatables.net/plug-ins/1.10.19/dataRender/datetime.js"></script>
        <script type="text/javascript" charset="utf-8"src="https://cdnjs.cloudflare.com/ajax/libs/selectize.js/0.9.0/js/standalone/selectize.js"></script>
        <script type="text/javascript" charset="utf-8"src="js/selectiz.js"> </script>
    <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>

And how do a super global variable?

antoniocib

i declare the superglobal variable in this way:

    $_POST['startDate'] = $startDate,
    ->where( function ($q) {
  if ($startDate) {
    $q->where('data_di_scarico', = $startDate );

  }

but i guess is wrong..

rf1234

You don't need to declare the POST variable. It exists already. It is created by PHP automatically because you are sending it to the server.

You are overwriting the POST variable in your code. Don't do it, just use it instead of $startDate.

antoniocib

$_POST['startDate'],
    ->where( function ($q) {
  if ($_POST['startDate']) {
    $q->where('data_di_scarico' = $_POST['startDate']);

  }
 )

So in this way?

Anyway, I don't know how to fix the invalid Json's mistake. Can you help me?

kthorngren

Did you follow the troubleshooting steps provided in the link?
https://datatables.net/manual/tech-notes/1

What did you find?

Kevin

antoniocib

The problem is in PHP but i dont know where is the problem:

    ->where( function ($q) {
  if ($_POST['startDate']) {
    $q->where('data_di_scarico', $_POST['startDate'], '>=');

  }); // line 119

    ->process( $_POST )
    ->json();

rf1234

I don't understand your code, see inline commenting

$_POST['startDate'], //what is this line supposed to mean?
    ->where( function ($q) {
  if ($_POST['startDate']) { //this doesn't check whether the the variable is set
    $q->where('data_di_scarico' = $_POST['startDate']); //wrong syntax!
 
  }
 )

This looks a little better hopefully. Please read the manual BEFORE posting your code. Thank you.

->where( function ($q) {
   if ( isset($_POST['startDate']) ) {
       $q->where( 'data_di_scarico', $_POST['startDate'] ); 
   }
 )

antoniocib

Excuse me @rf1234 , I have read but I do not understand why it gives me the problem on that line next to " ) " what can I do? I posted the problem with the correct code on it.