Exact match search with serverside mode?

Exact match search with serverside mode?

WebfaceWebface Posts: 9Questions: 2Answers: 0

Dear community,
Is this possible exact match search with serverside mode?
I mean that i search ex. "124", i want to see only row which contain 124, not 1247, 1249, 12498, etc.
I try to put double quote in the search field, but it doesn't work...
My Javascript is:

`   <script type="text/javascript" language="javascript" class="init">
    

function format ( d ) {
        return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
        '<tr>'+
            '<td>Lakcím:</td>'+
            '<td>'+d.tagok_irszam+' '+d.tagok_varos+', '+d.tagok_utca+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Szolgáltatásra jogosult:</td>'+
            '<td>'+d.tagok_szolgaltatasra_jogosult+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Azonosítva?:</td>'+
            '<td>'+d.tagok_is_azonositva+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Születési dátum:</td>'+
            '<td>'+d.tagok_szuletesidatum+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Telefonszám:</td>'+
            '<td>'+d.tagok_telefonszam+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>TAJ szám:</td>'+
            '<td>'+d.tagok_tajszam+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>E-mail:</td>'+
            '<td>'+d.tagok_email+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Tagdíj fizetés módja:</td>'+
            '<td>'+d.tagok_tagdijbefizetes_modja+'</td>'+
        '</tr>'+        
    '</table>';     
}

$(document).ready(function() {
    var dt = $('#example').DataTable( {
         "search": [
            {
              "bRegex": true,
              "bSmart": false,
            }
         ],
        "processing": true,
        "serverSide": true,
        "ajax": "server_side/scripts/ids-objects.php",
        "columns": [ 
            {
                "class":          "details-control",
                "orderable":      false,
                "data":           null,
                "defaultContent": ""
            },
            { "data": "tagok_nev" },
            { "data": "tagok_tagkod" },
            { "data": "tagok_ezevben_fizetett_osszes_tagdij" },
            { "data": "tagok_hatralek" }
        ],
        
        "order": [[1, 'asc']]
        
    } );
    //BEGIN COLUMN SEARCH
    $('#example tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Keresés '+title+'" />' );
    } );
 
    // DataTable
    var table = $('#example').DataTable();
 
    // Apply the search
    table.columns().every( function () {
        var that = this;
 
        $( 'input', this.footer() ).on( 'keyup change', function () {
            if ( that.search() !== this.value ) {
                that
                    .search( this.value )
                    .draw();
            }
        } );

    //END OF COLUMN SEARCH  
    } );
    
    
    // Array to track the ids of the details displayed rows
    var detailRows = [];
    
    $('#example tbody').on( 'click', 'tr td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = dt.row( tr );
        var idx = $.inArray( tr.attr('id'), detailRows );

        if ( row.child.isShown() ) {
            tr.removeClass( 'details' );
            row.child.hide();

            // Remove from the 'open' array
            detailRows.splice( idx, 1 );
        }
        else {
            tr.addClass( 'details' );
            row.child( format( row.data() ) ).show();

            // Add to the 'open' array
            if ( idx === -1 ) {
                detailRows.push( tr.attr('id') );
            }
        }
    } );

    // On each draw, loop over the `detailRows` array and show any child rows
    dt.on( 'draw', function () {
        $.each( detailRows, function ( i, id ) {
            $('#'+id+' td.details-control').trigger( 'click' );
        } );
    } );
} );

    </script>`

Any help appreciate!

This question has an accepted answers - jump to answer

Answers

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

    Hi @Webface ,

    Yep, you'll need to do a regex search() on line 84 - so change

    .search( this.value )
    

    to be

    .search('^' + this.value + '$', true, false)
    

    Cheers,

    Colin

  • WebfaceWebface Posts: 9Questions: 2Answers: 0

    Hi Colin,
    Thanks, but unfortunatelly it's doesn'work...
    With this change cant find anything... :(

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

    Hi @Webface ,

    That's definitely the syntax you need. Take a look at this example here - this is doing the same full search as I gave above. If you type 'London' into that third column, it only shows when fully matched.

    Can you compare that with yours please, and if still no joy, it would be worth creating a similar example showing it not working.

    Cheers,

    Colin

  • WebfaceWebface Posts: 9Questions: 2Answers: 0

    Dear Collin,

    Thank you for your help and kindness!
    I check the syntax, and it seems that fine, but the script doesnt work for me...
    Please take a look, and do a search in age, example: https://uat.tvep.hu/datatable/
    My scrpts is:
    HTML+Javascript:

    `<!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8">
        <link rel="shortcut icon" type="image/ico" href="http://www.datatables.net/favicon.ico">
        <meta name="viewport" content="initial-scale=1.0, maximum-scale=2.0">
        <title>DataTables example - Row details</title>
        <link rel="stylesheet" type="text/css" href="media/css/jquery.dataTables.css">
        <link rel="stylesheet" type="text/css" href="resources/syntax/shCore.css">
        <link rel="stylesheet" type="text/css" href="resources/demo.css">
        <style type="text/css" class="init">
        
    td.details-control {
        background: url('resources/details_open.png') no-repeat center center;
        cursor: pointer;
    }
    tr.details td.details-control {
        background: url('resources/details_close.png') no-repeat center center;
    }
    tfoot input {
        width: 100%;
        padding: 3px;
        box-sizing: border-box;
    }
    tfoot {
        display: table-header-group;
    }
        </style>
        <script type="text/javascript" language="javascript" src="//code.jquery.com/jquery-1.12.4.js">
        </script>
        <script type="text/javascript" language="javascript" src="media/js/jquery.dataTables.js">
        </script>
        <script type="text/javascript" language="javascript" src="resources/syntax/shCore.js">
        </script>
        <script type="text/javascript" language="javascript" src="resources/demo.js">
        </script>
        <script type="text/javascript" language="javascript" class="init">
        
    
    function format ( d ) {
        return 'Full name: '+d.first_name+' '+d.last_name+'<br>'+
            'Salary: '+d.salary+'<br>'+
            'The child row can contain any data you wish, including links, images, inner tables etc.';
    }
    
    $(document).ready(function() {
        var dt = $('#example').DataTable( {
            "processing": true,
            "serverSide": true,
            "ajax": "server_side/scripts/ids-objects.php",
            "columns": [ 
                {
                    "class":          "details-control",
                    "orderable":      false,
                    "data":           null,
                    "defaultContent": ""
                },
                { "data": "first_name" },
                { "data": "last_name" },
                { "data": "position" },
                { "data": "office" },
                { "data": "age" }
            ],
            "order": [[1, 'asc']]
        } );
    
            //BEGIN COLUMN SEARCH
        $('#example tfoot th').each( function () {
            var title = $(this).text();
            $(this).html( '<input type="text" placeholder="Keresés '+title+'" />' );
        } );
     
        // DataTable
        var table = $('#example').DataTable();
     
        // Apply the search
        table.columns().every( function () {
            var that = this;
     
            $( 'input', this.footer() ).on( 'keyup change', function () {
                if ( that.search() !== this.value ) {
                    that
                        .search('^' + this.value + '$', true, false)
                        .draw();
                }
            } );
    
        //END OF COLUMN SEARCH  
        } );
        
            
        // Array to track the ids of the details displayed rows
        var detailRows = [];
    
        $('#example tbody').on( 'click', 'tr td.details-control', function () {
            var tr = $(this).closest('tr');
            var row = dt.row( tr );
            var idx = $.inArray( tr.attr('id'), detailRows );
    
            if ( row.child.isShown() ) {
                tr.removeClass( 'details' );
                row.child.hide();
    
                // Remove from the 'open' array
                detailRows.splice( idx, 1 );
            }
            else {
                tr.addClass( 'details' );
                row.child( format( row.data() ) ).show();
    
                // Add to the 'open' array
                if ( idx === -1 ) {
                    detailRows.push( tr.attr('id') );
                }
            }
        } );
    
        // On each draw, loop over the `detailRows` array and show any child rows
        dt.on( 'draw', function () {
            $.each( detailRows, function ( i, id ) {
                $('#'+id+' td.details-control').trigger( 'click' );
            } );
        } );
    } );
    
        </script>
    </head>
    <body class="dt-example">
        <div class="container">
            <section>
                <h1>DataTables example <span>Row details</span></h1>
    
                <div class="demo-html"></div>
                <table id="example" class="display" cellspacing="0" width="100%">
                    <thead>
                        <tr>
                            <th></th>
                            <th>First name</th>
                            <th>Last name</th>
                            <th>Position</th>
                            <th>Office</th>
                            <th>Age</th>
                        </tr>
                    </thead>
                    <tfoot>
                        <tr>
                            <th></th>
                            <th>First name</th>
                            <th>Last name</th>
                            <th>Position</th>
                            <th>Office</th>
                            <th>Age</th>
                        </tr>
                    </tfoot>
                </table>
    
            </section>
        </div>
        <br/>
        <br/>
        <br/>
    </body>
    </html>`
    

    ids-objects.php:

    `<?php
    
    /*
     * DataTables example server-side processing script.
     *
     * Please note that this script is intentionally extremely simply to show how
     * server-side processing can be implemented, and probably shouldn't be used as
     * the basis for a large complex system. It is suitable for simple use cases as
     * for learning.
     *
     * See http://datatables.net/usage/server-side for full details on the server-
     * side processing requirements of DataTables.
     *
     * @license MIT - http://datatables.net/license_mit
     */
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */
    
    // DB table to use
    $table = 'xxxxx';
    
    // Table's primary key
    $primaryKey = 'id';
    
    // Array of database columns which should be read and sent back to DataTables.
    // The `db` parameter represents the column name in the database, while the `dt`
    // parameter represents the DataTables column identifier - in this case object
    // parameter names
    $columns = array(
        array(
            'db' => 'id',
            'dt' => 'DT_RowId',
            'formatter' => function( $d, $row ) {
                // Technically a DOM id cannot start with an integer, so we prefix
                // a string. This can also be useful if you have multiple tables
                // to ensure that the id is unique with a different prefix
                return 'row_'.$d;
            }
        ),
        array( 'db' => 'first_name', 'dt' => 'first_name' ),
        array( 'db' => 'last_name',  'dt' => 'last_name' ),
        array( 'db' => 'position',   'dt' => 'position' ),
        array( 'db' => 'office',     'dt' => 'office' ),
        array( 'db' => 'age',     'dt' => 'age' ),
        array(
            'db'        => 'start_date',
            'dt'        => 'start_date',
            'formatter' => function( $d, $row ) {
                return date( 'jS M y', strtotime($d));
            }
        ),
        array(
            'db'        => 'salary',
            'dt'        => 'salary',
            'formatter' => function( $d, $row ) {
                return '$'.number_format($d);
            }
        )
    );
    
    $sql_details = array(
        'user' => 'xxxxx',
        'pass' => 'xxxxx',
        'db'   => 'xxxxx',
        'host' => 'xxxxx'
    );
    
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP
     * server-side, there is no need to edit below this line.
     */
    
    require( 'ssp.class.php' );
    
    echo json_encode(
        SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
    );
    
    `
    

    Big thanks!!!!!

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

    Sorry, I missed the fact that you've got serverSide enabled - our server-side scripts don't support regex. The rationale is that you would only enable server-side if you've got 1000s or records - and a regular expression on that would be brutal. If your expected dataset is going to be small, you could ditch serverSide and do the processing on the client.

    Cheers,

    Colin

  • WebfaceWebface Posts: 9Questions: 2Answers: 0
    edited May 2018

    Dear Colin,
    Thanks for your quick reply!
    Unfortunatelly we have a large database with more than 17 000 records...
    So, i think we have to compromise...:)
    Thanks again,
    Best regards,
    Laci

This discussion has been closed.