Individual column searching issue with format date Moment

Individual column searching issue with format date Moment

aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

Hello,

I'm stuck on the individual column searching for a few days.. I succeeded to change display date format with the moment library but it doesn't apply to the search box. It still expected a date like "YYYY-MM-DD".

This my code :

        var tableCheck = $('#histo').DataTable({
            scrollX: true,
            "sScrollY": "120em",
            "sScrollX": "100%",
            "bScrollCollapse": true,
            initComplete: function() {
                $('div.dataTables_scrollHeadInner thead tr#filterCheckHisto [name="filter"]').each(function() {
                    $(this).html('<input id="input' + $(this).index() + '" type="text" class="form-control" placeholder="' + $(this).text() + '" />');
                    $(this).on('keyup change', function() {
                        var val;
                        val = $('#input' + $(this).index()).val();

                        var title = $(this).text();
                        console.log('titre =' + title);

                        console.log('laa');
                        tableCheck
                            .column($(this).attr("data-num"))
                            .column($(this).index()).search(val)
                            .draw();

                    });
                });
            },

            orderCellsTop: true,
            fixedHeader: true,

            dom: 'Blfrtip',
            "searching": true,
            "processing": true,
            "serverSide": true,
            "ajax": "../server_side/scripts/server_processing_tb_check.php",

            "order": [7, "desc"],


            "columnDefs": [{
                    targets: '_all',
                    defaultContent: '-'
                },
                {
                    "targets": 5,
                    "type": "date-euro",
                    "render": function(data, type, row, meta) {
                         return moment(data).format('DD-MM-YYYY HH:mm');               
                    }
                }
            ],

            "pageLength": 25,

        });

Basically, I'd like to add a "data-order" on <td> but I don't know how can I figure out.

Thanks in advance for your help

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

    Cheers,

    Colin

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi colin,

    Thank you for your answer. You can find my code here :
    https://jsfiddle.net/oh0q4zu6/4/

    As I said, if you try to search a date in the "transaction date" column it doesn't find anything..

    Thanks for your help

    Aurélien

  • colincolin Posts: 15,240Questions: 1Answers: 2,599
    Answer ✓

    Thanks, that test case helps.

    The problem is two-fold, both related to columns.render:

    1. you're only returning something if the condition matches - you need to return something for the other cases too
    2. as you're changing the format for display it would make sense to use the same change for filter,

    Colin

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi Colin,

    Thank you for you answer, it works !

    For those who will be interested by this topic you can find my code here :https://jsfiddle.net/3nwsqjd6/

    Aurélien

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi,

    It works with static data like we've seen in the previous fiddle, but when I want to use my datatable as a processing table the individual search box not works anymore.

    When I say it not works, it means that the individual search box expect a date like this "YYYY-MM-DD HH:mm" instead of 'DD-MM-YYYY HH:mm'..

    I didn't succeed to add my processing script in the fiddle..

    Aurélien

  • kthorngrenkthorngren Posts: 21,315Questions: 26Answers: 4,948

    You have serverSide: true enabling server side processing. Your server script is responsible for searching, sorting and paging. Take a look at your server script to see how its handling the date format. You may need to convert it to a format that your server script can use to search your data source.

    Kevin

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0
    edited April 2021

    Thank you Kevin for you answer,

    How can I change the format on the server script ?
    You'll find below my server script :

        <?php
        session_start(); // On démarre la session AVANT toute chose
    
        // Connexion BDD
        include('../../admin/config.php');
    
        $dbDetails = array(
            'host' => $host_name,
            'user' => $user_name,
            'pass' => $password,
            'db'   => $database
        );
    
    
        // Nom de la table
        $table = 'gmao_Articles_Transaction';
    
        // Clé primaire Table
        $primaryKey = 'ID_TRANS';
    
        // Chaine de caractère à récupérer 
        // `db` represente le nom de la colonne dans la BDD
        // `dt` represente les identifiants de la colonne DataTables
        $columns = array(
            array('db' => 'INDEX_ART', 'dt' => 0),
            array('db' => 'TypeTransaction',      'dt' => 1),
            array('db' => 'Quantite',     'dt' => 2),
            array('db' => 'PrixTransaction',     'dt' => 3),
            array('db' => 'ArticlePris',    'dt' => 4),
            array('db' => 'ArticleUtilise',    'dt' => 5),
            array('db' => 'INDEX_BI',  'dt' => 6),
            array('db' => 'INDEX_CMDE',  'dt' => 7),
            array('db' => 'DateTransaction',    'dt' => 8),
            array('db' => 'CreePar',    'dt' => 9),
            array('db' => 'DeviseTransaction',     'dt' => 10)
        );
    
        // Include SQL query processing class 
        require 'ssp.class.php';
    
        $whereAll = "EntiteTransaction = '".$_SESSION['entiteMembre']."' AND Etat<>2";
    
        // data au format json
        echo json_encode(
            // SSP::simple($_GET, $dbDetails, $table, $primaryKey, $columns)
            SSP::complex($_GET, $dbDetails, $table, $primaryKey, $columns, $whereResult = null, $whereAll)
    
        );
    

    Something like : array('db' => moment('DateTransaction').format('DD-MM-YYYY HH:mm'), 'dt' =>8) ?

    Aurélien

  • allanallan Posts: 63,482Questions: 1Answers: 10,467 Site admin

    You could do something like:

    $data = $_GET;
    
    $date_str = $data['columns'][0]['search']['value'];
    $date = date_create_from_format('...', $date_str);
    $data['columns'][0]['search']['value'] = date_format( $date, 'Y-m-d' );
    
    echo json_encode(
      SSP::complex( $data, ... )
    );
    

    Change the [0] to be whatever the column index is that your date is in. And obviously fill in the ... parts.

    The other option would be to use something like our DateTime picker which can be configured to give you a wireFormat in ISO, while displaying in whatever locale you want.

    Regards,
    Allan

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi Allan,

    Thanks for your reply,
    It still not work.. I've never used "date_create_from_format" so I've certainly done something wrong :

        `<?php
        session_start();
        include('../../admin/config.php');
    
        $dbDetails = array(
            'host' => $host_name,
            'user' => $user_name,
            'pass' => $password,
            'db'   => $database
        );
    
        // Nom de la table
        $table = 'gmao_Articles_Transaction';
    
        // Clé primaire Table
        $primaryKey = 'ID_TRANS';
    
        // Chaine de caractère à récupérer 
        // `db` represente le nom de la colonne dans la BDD
        // `dt` represente les identifiants de la colonne DataTables
        $columns = array(
            array('db' => 'INDEX_ART', 'dt' => 0),
            array('db' => 'TypeTransaction',      'dt' => 1),
            array('db' => 'Quantite',     'dt' => 2),
            array('db' => 'PrixTransaction',     'dt' => 3),
            array('db' => 'ArticlePris',    'dt' => 4),
            array('db' => 'ArticleUtilise',    'dt' => 5),
            array('db' => 'INDEX_BI',  'dt' => 6),
            array('db' => 'INDEX_CMDE',  'dt' => 7),
            array('db' => 'DateTransaction',    'dt' => 8),
            array('db' => 'CreePar',    'dt' => 9),
            array('db' => 'DeviseTransaction',     'dt' => 10)
        );
    
        // Include SQL query processing class 
        require 'ssp.class.php';
    
        $whereAll = "EntiteTransaction = '".$_SESSION['entiteMembre']."' AND Etat<>2";
    
        $data = $_GET;
    
        $date_str = $data['columns'][8]['search']['value'];
        $date = date_create_from_format("Y-m-d", $date_str);
        $data['columns'][8]['search']['value'] = date_format($date_str, "d-m-Y");
    
        // data au format json
        echo json_encode(
            SSP::complex($data, $dbDetails, $table, $primaryKey, $columns, $whereResult = null, $whereAll)
        );
    

    Thank you

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi Allan,

    Thanks for your reply,

    It still not work.. It certainly because I've never used date_create_from_format()

        <?php
        session_start(); 
    
        // Connexion BDD
        include('../../admin/config.php');
    
        $dbDetails = array(
            'host' => $host_name,
            'user' => $user_name,
            'pass' => $password,
            'db'   => $database
        );
    
    
        // Nom de la table
        $table = 'gmao_Articles_Transaction';
    
        // Clé primaire Table
        $primaryKey = 'ID_TRANS';
    
        // Chaine de caractère à récupérer 
        // `db` represente le nom de la colonne dans la BDD
        // `dt` represente les identifiants de la colonne DataTables
        $columns = array(
            array('db' => 'INDEX_ART', 'dt' => 0),
            array('db' => 'TypeTransaction',      'dt' => 1),
            array('db' => 'Quantite',     'dt' => 2),
            array('db' => 'PrixTransaction',     'dt' => 3),
            array('db' => 'ArticlePris',    'dt' => 4),
            array('db' => 'ArticleUtilise',    'dt' => 5),
            array('db' => 'INDEX_BI',  'dt' => 6),
            array('db' => 'INDEX_CMDE',  'dt' => 7),
            array('db' => 'DateTransaction',    'dt' => 8),
            array('db' => 'CreePar',    'dt' => 9),
            array('db' => 'DeviseTransaction',     'dt' => 10)
        );
    
        // Include SQL query processing class 
        require 'ssp.class.php';
    
        $whereAll = "EntiteTransaction = '".$_SESSION['entiteMembre']."' AND Etat<>2";
    
        $data = $_GET;
    
        $date_str = $data['columns'][8]['search']['value'];
        $date = date_create_from_format("Y-m-d", $date_str);
        $data['columns'][8]['search']['value'] = date_format($date_str, "d-m-Y");
    
        // data au format json
        echo json_encode(
            SSP::complex($data, $dbDetails, $table, $primaryKey, $columns, $whereResult = null, $whereAll)
    
        );
    

    Can you see something wrong ?

  • tangerinetangerine Posts: 3,365Questions: 39Answers: 395

    I can't say if this is a solution, but you are assigning a value to $date which is subsequently not used.

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Exact, made a mistake :

        <?php
        session_start(); // On démarre la session AVANT toute chose
    
        // Connexion BDD
        include('../../admin/config.php');
    
        $dbDetails = array(
            'host' => $host_name,
            'user' => $user_name,
            'pass' => $password,
            'db'   => $database
        );
    
    
        // Nom de la table
        $table = 'gmao_Articles_Transaction';
    
        // Clé primaire Table
        $primaryKey = 'ID_TRANS';
    
        // Chaine de caractère à récupérer 
        // `db` represente le nom de la colonne dans la BDD
        // `dt` represente les identifiants de la colonne DataTables
        $columns = array(
            array('db' => 'INDEX_ART', 'dt' => 0),
            array('db' => 'TypeTransaction',      'dt' => 1),
            array('db' => 'Quantite',     'dt' => 2),
            array('db' => 'PrixTransaction',     'dt' => 3),
            array('db' => 'ArticlePris',    'dt' => 4),
            array('db' => 'ArticleUtilise',    'dt' => 5),
            array('db' => 'INDEX_BI',  'dt' => 6),
            array('db' => 'INDEX_CMDE',  'dt' => 7),
            array('db' => 'DateTransaction',    'dt' => 8),
            array('db' => 'CreePar',    'dt' => 9),
            array('db' => 'DeviseTransaction',     'dt' => 10)
        );
    
        // Include SQL query processing class 
        require 'ssp.class.php';
    
        $whereAll = "EntiteTransaction = '".$_SESSION['entiteMembre']."' AND Etat<>2";
    
        $data = $_GET;
    
        $date_str = $data['columns'][8]['search']['value'];
        $date = date_create_from_format("Y-m-d", $date_str);
        $data['columns'][8]['search']['value'] = date_format($date, "d-m-Y");
    
        // data au format json
        echo json_encode(
            SSP::complex($data, $dbDetails, $table, $primaryKey, $columns, $whereResult = null, $whereAll)
    
        );
    

    It's still not working

  • allanallan Posts: 63,482Questions: 1Answers: 10,467 Site admin

    Looks like the logic is invert:

    Line 46 is creating a date from 'Y-m-d', but I thought you wanted to submit in a different format? d-m-Y?
    Line 47 is then converted to 'd-m-Y' which is not the ISO8601 format that the SQL server needs.

    Swap those two formats around.

    If that doesn't work, then print_r($data) and make sure the ['columns'][8]['search']['value'] is the ISO format you expect.

    Allan

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi,

    Yes I tried to swap those date format but it still not work..
    Can we really print_r something in the server-side processing loop ?

        $data = $_GET;
    
        $date_str = $data['columns'][8]['search']['value'];
        $date = date_create_from_format("d-m-Y", $date_str);
        $data['columns'][8]['search']['value'] = date_format($date, "Y-m-d");
    
        print_r($data);
    
        // data au format json
        echo json_encode(
            SSP::complex($data, $dbDetails, $table, $primaryKey, $columns, $whereResult = null, $whereAll)
    
        );
    

    I also tried to declare $data as global and print outside the server-side processing..

    Thanks again for your time

  • allanallan Posts: 63,482Questions: 1Answers: 10,467 Site admin

    Can we really print_r something in the server-side processing loop ?

    Sure. It will make the JSON response invalid, and DataTables will give you a warning about that, but that doesn't matter for debugging. Use your browser's network inspector to see the response from the server which will include your print_r information.

    Allan

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0
    edited May 2021

    Hi,

    Sorry for my late reply,
    Below you'll find a screenshot of my network inspector :

    Aurélien

  • allanallan Posts: 63,482Questions: 1Answers: 10,467 Site admin

    That looks like the preview view, which would strip out the print_r output. You want the raw response.

    Allan

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi,

    I've got this response :

    data GET before date_create =Array ; data after date_create_from_format =Array{"draw":1,"recordsTotal":6,"recordsFiltered":6,"data":[["2233","RECEPTION","20","800","BDM",null,null,"165","2021-05-20 10:47:00","ADAV","\u20ac"],["2233","RECEPTION","30","1200","BDM",null,null,"165","2021-05-20 10:46:00","ADAV","\u20ac"],["1001","SORTIE ARTICLE BI","-1","-11.3333","AUD","AUD","95",null,"2021-05-20 10:40:49","ADAV","\u20ac"],["1023","SORTIE ARTICLE BI","-5","-10","FEN","FEN","89",null,"2021-05-04 08:28:28","ADY","\u20ac"],["1003","SORTIE ARTICLE BI","-4","-8","FEN","FEN","89",null,"2021-05-04 08:28:26","ADY","\u20ac"],["1001","SORTIE ARTICLE BI","-2","-4","FEN","FEN","89",null,"2021-05-04 08:28:23","ADY","\u20ac"]]}

  • allanallan Posts: 63,482Questions: 1Answers: 10,467 Site admin

    I think I'm going to need a link to your page as well as the latest PHP.

    Allan

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi Allan,

    I sent you my personal website by private message few days ago,

    Aurelien

  • aurelien40aurelien40 Posts: 13Questions: 1Answers: 0

    Hi,

    I still need help .. Can someone give me a hand please ?

    Thank you,

    Aurelien

Sign In or Register to comment.