Individual column searching issue with format date Moment

aurelien40 · April 2021

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

colin · April 2021

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

aurelien40 · April 2021

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

colin · April 2021

Thanks, that test case helps.

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

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

Colin

aurelien40 · April 2021

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

aurelien40 · April 2021

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

kthorngren · April 2021

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

aurelien40 · 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

allan · May 2021

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

aurelien40 · May 2021

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

aurelien40 · May 2021

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 ?

tangerine · May 2021

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

aurelien40 · May 2021

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

allan · May 2021

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

aurelien40 · May 2021

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

allan · May 2021

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

aurelien40 · May 2021

Hi,

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

Aurélien

allan · May 2021

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

Allan

aurelien40 · June 2021

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"]]}

allan · June 2021

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

Allan

aurelien40 · June 2021

Hi Allan,

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

Aurelien

aurelien40 · July 2021

Hi,

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

Thank you,

Aurelien

Individual column searching issue with format date Moment

Individual column searching issue with format date Moment

Answers

Howdy, Stranger!

Categories

DataTables

Individual column searching issue with format date Moment

Individual column searching issue with format date Moment

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables