Using search and sorting on input rows with DataTables

arturEC

I am using the DataTables, trying to use the sort and search functions with input elements. I have managed the sorting to work for all elements but I am struggling to get the search function to work.

I have tried following the example available on DataTables and suggestion in JQuery Datatables search within input and select and it seems to be working for the most part, but I am unable to search for some items, for example, I have a product called "Bread Buns (Case / 48)". If I search for "Bread", nothing happens, not even showing "no results", the table doesn't refresh. But if I try "Buns" or "Case / 48", I get the result. The same thing happens with a couple of other items and there is no logic to it AT ALL.

My code for the script is as follows:

$.fn.dataTable.ext.order['dom-text'] = function  ( settings, col )
{
    return this.api().column( col, {order:'index'} ).nodes().map( function ( td, i ) {
        return $('input', td).val();
    } );
}

/* Create an array with the values of all the input boxes in a column, parsed as numbers */
$.fn.dataTable.ext.order['dom-text-numeric'] = function  ( settings, col )
{
    return this.api().column( col, {order:'index'} ).nodes().map( function ( td, i ) {
        return $('input', td).val() * 1;
    } );
}

$.fn.dataTable.ext.order['dom-drop'] = function  ( settings, col )
{
    return this.api().column( col, {order:'index'} ).nodes().map( function ( td, i ) {
        return $('select', td).val();
    } );
}

$.fn.dataTableExt.ofnSearch['html-input'] = function(value) {
    return $(value).val();
};

$(document).ready(function() {

   $('#tbl_store_product_sizes').DataTable( {
        "columnDefs": [
            { "targets": 1, "orderDataType": "dom-text", "type": "string" },
            { "targets": 2, "orderDataType": "dom-drop", "type": "string" },
            { "targets": 3, "orderDataType": "dom-text-numeric" },
            { "targets": 4, "orderDataType": "dom-text-numeric" },
            { "targets": 5, "orderDataType": "dom-text-numeric" },
            { "type": "html-input", "targets": [1, 2, 3, 4, 5] }
        ],
        "order": [[ 5, "desc" ]]
         });


} );

I don't understand why the search works in some cases, but not others? I inspected the page and the value for those TDs doesn't have any weird characters or anything like that.

I also can't find anything on how to be able to make the dropdown searchable so a suggestion on that would be greatly appreciated.

My code for the actual table:

<table style=' height:10px; margin-left:-10px' id="tbl_store_product_sizes" class="table table-fixed table-striped table-bordered datalist"  cellspacing="0">
        <thead>

                <tr >
                     <th style='width:10%;'> No. </th>
                     <th style='width:40%;'> Product Name  </th>
                     <th style='width:20%;'> Department Name </th>
                     <th style='width:10%;'> Unit Cost </th>
                     <th style='width:10%;'> Quantity Counted </th>
                     <th style='width:10%;'> Total Cost </th>

                 </tr>


        </thead> 
        <tbody>

            <?php
                $query          =   "select id from view_stocktake_master where store_id = '$store_id' order by stock_date desc limit 1";
                //echo $query;
                $retval         =   f_select_query($query, $datarows);              
                $stocktake_id                       =    f_htmlspecialchars_decode($datarows[0]->id , ENT_QUOTES);
                $query      = "";
                $rows       = "";
                $rowcount   = 0;
                $retval     = 0;

                $query      =   "select sp.product_id AS product_id, sp.department_id AS department_id, sp.product_name AS product_name, sd.department_name AS department_name, sp.unit_cost AS unit_cost, SUM(sp.quantity_units) AS quantity_counted, ROUND(SUM(sp.quantity_units) * sp.unit_cost,2) AS total_cost from stocktake_details sp INNER JOIN store_departments sd ON sd.id = sp.department_id where sp.stocktake_id = $stocktake_id GROUP BY sp.product_id ORDER BY total_cost DESC";
                //echo $query;
                $retval     =   f_select_query($query, $datarows);

                if ($retval == -1 ) 
                {
                    $connected = false;
                    return -1;
                }
                $department_id_dropdown     =   f_get_dropdown("department_name", "department_name", "store_departments", '', "department_id", " store_id = $store_id", '', '', '', false, false, true);

                $rowcount =  count($datarows);
                for ($counter = 0; $counter< $rowcount; $counter++)
                {
                    $lint_product_id                =    f_htmlspecialchars_decode($datarows[$counter]->product_id , ENT_QUOTES);

                    $lstr_product_name              =    truncate(f_htmlspecialchars_decode($datarows[$counter]->product_name , ENT_QUOTES), 150);

                    $lstr_department_name           =    truncate(f_htmlspecialchars_decode($datarows[$counter]->department_name , ENT_QUOTES), 150);

                    $lint_department_id             =    f_htmlspecialchars_decode($datarows[$counter]->department_id , ENT_QUOTES);
                    $lint_department_id             =    intval($lint_department_id);

                    $lint_unit_cost                 =    f_htmlspecialchars_decode($datarows[$counter]->unit_cost , ENT_QUOTES);
                    $lint_unit_cost                 =    floatval($lint_unit_cost);
                    $lint_unit_cost                 =    number_format($lint_unit_cost, 2);

                    $lint_quantity_counted                  =    f_htmlspecialchars_decode($datarows[$counter]->quantity_counted , ENT_QUOTES);
                    $lint_quantity_counted                  =    floatval($lint_quantity_counted);
                    $lint_quantity_counted                  =    number_format($lint_quantity_counted, 2);

                    $lint_total_cost                    =    f_htmlspecialchars_decode($datarows[$counter]->total_cost , ENT_QUOTES);
                    $lint_total_cost                    =    floatval($lint_total_cost);
                    $lint_total_cost                    =    number_format($lint_total_cost, 2);

                    $department_id_dropdown     =   f_get_dropdown("department_name", "department_name", "store_departments", $lstr_department_name, "department_id", " store_id = $store_id", '', '', '', false, false, true);


                    $row_counter = intval($counter) + 1;
                    if ($lint_quantity_counted == 0)
                    {
                        echo "<tr>";                    
                        echo "<td style='color:red; width:10%;' > $row_counter </td>";
                        echo "<td><input type='text' name='product_name' class='form-control product_name' style='width:80%;' value='$lstr_product_name' /></td>";
                        echo "<td class='text-right' style='width:20%;'> $department_id_dropdown </td>";
                        echo "<td style='color:red;' > <input type='text' name='unit_cost' class='form-control unit_cost' style='width:80%;' value='$lint_unit_cost' /> <input name='product_id' class='form-control product_id' value='$lint_product_id' type='hidden'/> </td>";
                        echo "<td style='color:red;' > <input type='text' tabindex='-1' readonly name='quantity_counted' class='form-control editable_control number_format unit_cost' style='width:80%;' value='$lint_quantity_counted' />  </td>";
                        echo "<td style='color:red;' > <input type='text' tabindex='-1' readonly name='total_cost' class='form-control editable_control number_format unit_cost' style='width:80%;' value='$lint_total_cost' />  </td>";


                        echo "</tr>";

                    }
                    else {
                        echo "<tr> ";                   
                        echo "<td style='width:10%;'> $row_counter  </td>";
                        echo "<td><input type='text' name='product_name' class='form-control product_name' style='width:80%;' value='$lstr_product_name' /></td>";
                        echo "<td class='text-right' style='width:20%;'> $department_id_dropdown </td>";
                        echo "<td> <input type='text' name='unit_cost' class='form-control unit_cost' style='width:80%;' value='$lint_unit_cost' /> <input name='product_id' class='form-control product_id' value='$lint_product_id' type='hidden'/> </td>";
                        echo "<td> <input type='text' tabindex='-1' readonly name='quantity_counted' class='form-control editable_control number_format unit_cost' style='width:80%;' value='$lint_quantity_counted' />  </td>";
                        echo "<td> <input type='text' tabindex='-1' readonly name='total_cost' class='form-control editable_control number_format unit_cost' style='width:80%;' value='$lint_total_cost' />  </td>";


                        echo "</tr>";
                    }


                }
            ?>


        </tbody>
    </table>

I know it's quite a lot of code, but I wanted to show everything to make sure I am not missing something simple.

ddurose

Were you ever able to figure out the search for this? I too am stuck in trying to get the search to work but the sorting is working fine. Thanks.

colin

Hi @ddurose ,

This here might help, it was @kthorngren 's example from a different thread. This has both ordering and sorting.

Cheers,

Colin