Data picker

Data picker

antoniocibantoniocib Posts: 277Questions: 62Answers: 1

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?

This question has an accepted answers - jump to answer

«1

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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.

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    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.

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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.

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    @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.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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!

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited March 2020

    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.

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1
    edited March 2020

    @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)

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    edited March 2020

    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

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    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

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    @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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited March 2020

    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 ... :smiley:

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

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1
    edited March 2020

    @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">
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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?

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    @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...

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    @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>
    
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1

    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"

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited May 2020

    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']
    
  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1
    edited May 2020

    @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?

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1
    edited May 2020

    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..

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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.

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1
    $_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?

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

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

    What did you find?

    Kevin

  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1
    edited May 2020

    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();
    

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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'] ); 
       }
     )
    
  • antoniocibantoniocib Posts: 277Questions: 62Answers: 1
    edited May 2020

    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.

This discussion has been closed.