asked on

Could you point a way on how to implement a dynamic filter by using Datatables with Codeigniter/ jQuery?

Hi Experts

Could you point a way on how to implement a dynamic filter by using Datatables with Codeigniter/ jQuery?

Accordingly to:

When the user is clicking the columns of the Datatables grid must to present only the lines that correspond to the filter
in the example only the columns that have prefix = 91.

The view's Code:

 <div class="row">

	<div class="col-xs-12 mb15">
		<div class="input-group">
			<span class="input-group-addon ">
				<i class="fa fa-search c-gray"></i>
			</span>
			<input type="text" name="_prefix_search" id="_prefix_search" class="form-control gui-input br-light light" placeholder="<?php echo $this->lang->language['label_prefix'] ?>">
		</div>
	</div>
	
</div>

// jQuery code I found elsewhere for the same funcionality

$("#_prefix_search").on("change", function (e) {

	   table = $('#datatables').dataTable({

			"columnDefs": [
				{
					"searchable": false,
					"targets": [9]
				}
			],
			"lengthChange": false,
			"displayLength": 15,           
			"language": {
				"search": "Buscar:",
				"info": "Exibindo _START_ a _END_ do total de _TOTAL_ registros",
				"infoEmpty": "Exibindo _START_ a _END_ do total de _TOTAL_ registros",
				"zeroRecords": "Nenhum Registro Encontrado",
				"infoFiltered": "(filtrado de _MAX_ registros)",
				"paginate": {
					"next": "Próximo",
					"previous": "Anterior"
				}
			},
			"oTableTools": {
				"aButtons": [
					{
						'sExtends': 'copy',
		'sButtonText': '<i class="copy icon"></i>',
		'sToolTip': '{$language_msg->language['export_table_copy']}',
						'mColumns': [0, 1, 2, 3, 4, 5, 6, 7, 8]
					},
					{
						'sExtends': 'csv',
		'sButtonText': '<i class="file text outline icon"></i>',
						'sToolTip': '{$language_msg->language['export_table_csv']}',
						'mColumns': [0, 1, 2, 3, 4, 5, 6, 7, 8]
					},
					{
						'sExtends': 'xls',
		'sButtonText': '<i class="file excel outline icon"></i>',
						'sToolTip': '{$language_msg->language['export_table_xls']}',
						'sFileName': '*.xls',
						'mColumns': [0, 1, 2, 3, 4, 5, 6, 7, 8]
					},
					{
						'sExtends': 'pdf',
		'sButtonText': '<i class="file pdf outline icon"></i>',
						'sToolTip': '{$language_msg->language['export_table_pdf']}',
						'mColumns': [0, 1, 2, 3, 4, 5, 6, 7, 8],
						'sPdfSize': 'A4'
					},
		{
						'sExtends': 'print',
		'sButtonText': '<i class="print icon"></i>',
						'sToolTip': '{$language_msg->language['export_table_print']}',
						'mColumns': [0, 1, 2, 3, 4, 5, 6, 7, 8]
					}
				]
			}
		});

		if ($.fn.dataTable.TableTools) {
		var tableTools = new $.fn.dataTable.TableTools(table, {
			'buttons': [
			   'copy',
			   'csv',
			   'xls'
			]
		});

		$(tableTools.fnContainer()).appendTo($('.data.content .top.toolbar .tools.control'));
	}

	});

Open in new window

Thanks in advance

HainKurt

maybe something like this:

table
        .columns( 3 )
        .search( $("#_prefix_search").value )
        .draw();

Open in new window

where table is defined @ line 18

HainKurt

if you want whole table

table.search( $("#_prefix_search").value ).draw();

Open in new window

Eduardo Fuerte

ASKER

Hi

Sorry.
The jQuery code is just a reference I obtained elsewhere. It's not yet functional.
Something is preventing it to run and I'm seeking what could be, before to try your sugestions.
If you have any other code with the dinamic filter feature - I could try it also.

Eduardo Fuerte

ASKER

I found this code in Datatables site (with my own reductions)

<!DOCTYPE html>
<html>
<head>
    

    
    <style type="text/css" class="init">
	tfoot input {
		width: 100%;
		padding: 3px;
		box-sizing: border-box;
	}

	</style>
    

	
    <script type="text/javascript" language="javascript" src="//code.jquery.com/jquery-1.12.4.js">
	</script>
	
    
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js">
	</script>

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

$(document).ready(function() {
	// Setup - add a text input to each footer cell
	$('#example tfoot th').each( function () {
		var title = $(this).text();
		$(this).html( '<input type="text" placeholder="Search '+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();
			}
		} );
	} );
} );
</script>


</head>
<body class="wide comments example">

		<div class="fw-body">
			<div class="content">
				<h1 class="page_title">Individual column searching (text inputs)</h1>

				<table id="example" class="display" cellspacing="0" width="100%">
					<thead>
						<tr>
							<th>Name</th>
							<th>Position</th>
							<th>Office</th>
							<th>Age</th>
							<th>Start date</th>
							<th>Salary</th>
						</tr>
					</thead>
					<tfoot>
						<tr>
							<th>Name</th>
							<th>Position</th>
							<th>Office</th>
							<th>Age</th>
							<th>Start date</th>
							<th>Salary</th>
						</tr>
					</tfoot>
					<tbody>
						<tr>
							<td>Tiger Nixon</td>
							<td>System Architect</td>
							<td>Edinburgh</td>
							<td>61</td>
							<td>2011/04/25</td>
							<td>$320,800</td>
						</tr>
						<tr>
							<td>Garrett Winters</td>
							<td>Accountant</td>
							<td>Tokyo</td>
							<td>63</td>
							<td>2011/07/25</td>
							<td>$170,750</td>
						</tr>
						<tr>
							<td>Ashton Cox</td>
							<td>Junior Technical Author</td>
							<td>San Francisco</td>
							<td>66</td>
							<td>2009/01/12</td>
							<td>$86,000</td>
						</tr>
						<tr>
							<td>Colleen Hurst</td>
							<td>Javascript Developer</td>
							<td>San Francisco</td>
							<td>39</td>
							<td>2009/09/15</td>
							<td>$205,500</td>
						</tr>
						<tr>
							<td>Sonya Frost</td>
							<td>Software Engineer</td>
							<td>Edinburgh</td>
							<td>23</td>
							<td>2008/12/13</td>
							<td>$103,600</td>
						</tr>
						<tr>
							<td>Jena Gaines</td>
							<td>Office Manager</td>
							<td>London</td>
							<td>30</td>
							<td>2008/12/19</td>
							<td>$90,560</td>
						</tr>
						<tr>
							<td>Quinn Flynn</td>
							<td>Support Lead</td>
							<td>Edinburgh</td>
							<td>22</td>
							<td>2013/03/03</td>
							<td>$342,000</td>
						</tr>
						<tr>
							<td>Charde Marshall</td>
							<td>Regional Director</td>
							<td>San Francisco</td>
							<td>36</td>
							<td>2008/10/16</td>
							<td>$470,600</td>
						</tr>
						<tr>
							<td>Haley Kennedy</td>
							<td>Senior Marketing Designer</td>
							<td>London</td>
							<td>43</td>
							<td>2012/12/18</td>
							<td>$313,500</td>
						</tr>
						<tr>
							<td>Tatyana Fitzpatrick</td>
							<td>Regional Director</td>
							<td>London</td>
							<td>19</td>
							<td>2010/03/17</td>
							<td>$385,750</td>
						</tr>
						<tr>
							<td>Michael Silva</td>
							<td>Marketing Designer</td>
							<td>London</td>
							<td>66</td>
							<td>2012/11/27</td>
							<td>$198,500</td>
						</tr>
						<tr>
							<td>Paul Byrd</td>
							<td>Chief Financial Officer (CFO)</td>
							<td>New York</td>
							<td>64</td>
							<td>2010/06/09</td>
							<td>$725,000</td>
						</tr>
						<tr>
							<td>Gloria Little</td>
							<td>Systems Administrator</td>
							<td>New York</td>
							<td>59</td>
							<td>2009/04/10</td>
							<td>$237,500</td>
						</tr>
						<tr>
							<td>Bradley Greer</td>
							<td>Software Engineer</td>
							<td>London</td>
							<td>41</td>
							<td>2012/10/13</td>
							<td>$132,000</td>
						</tr>
						<tr>
							<td>Dai Rios</td>
							<td>Personnel Lead</td>
							<td>Edinburgh</td>
							<td>35</td>
							<td>2012/09/26</td>
							<td>$217,500</td>
						</tr>
						<tr>
							<td>Jenette Caldwell</td>
							<td>Development Lead</td>
							<td>New York</td>
							<td>30</td>
							<td>2011/09/03</td>
							<td>$345,000</td>
						</tr>
						<tr>
							<td>Yuri Berry</td>
							<td>Chief Marketing Officer (CMO)</td>
							<td>New York</td>
							<td>40</td>
							<td>2009/06/25</td>
							<td>$675,000</td>
						</tr>
						<tr>
							<td>Caesar Vance</td>
							<td>Pre-Sales Support</td>
							<td>New York</td>
							<td>21</td>
							<td>2011/12/12</td>
							<td>$106,450</td>
						</tr>
						<tr>
							<td>Doris Wilder</td>
							<td>Sales Assistant</td>
							<td>Sidney</td>
							<td>23</td>
							<td>2010/09/20</td>
							<td>$85,600</td>
						</tr>
						<tr>
							<td>Angelica Ramos</td>
							<td>Chief Executive Officer (CEO)</td>
							<td>London</td>
							<td>47</td>
							<td>2009/10/09</td>
							<td>$1,200,000</td>
						</tr>
						<tr>
							<td>Shou Itou</td>
							<td>Regional Marketing</td>
							<td>Tokyo</td>
							<td>20</td>
							<td>2011/08/14</td>
							<td>$163,000</td>
						</tr>
						<tr>
							<td>Michelle House</td>
							<td>Integration Specialist</td>
							<td>Sidney</td>
							<td>37</td>
							<td>2011/06/02</td>
							<td>$95,400</td>
						</tr>
						<tr>
							<td>Suki Burks</td>
							<td>Developer</td>
							<td>London</td>
							<td>53</td>
							<td>2009/10/22</td>
							<td>$114,500</td>
						</tr>
						<tr>
							<td>Prescott Bartlett</td>
							<td>Technical Author</td>
							<td>London</td>
							<td>27</td>
							<td>2011/05/07</td>
							<td>$145,000</td>
						</tr>
						<tr>
							<td>Gavin Cortez</td>
							<td>Team Leader</td>
							<td>San Francisco</td>
							<td>22</td>
							<td>2008/10/26</td>
							<td>$235,500</td>
						</tr>
						<tr>
							<td>Martena Mccray</td>
							<td>Post-Sales support</td>
							<td>Edinburgh</td>
							<td>46</td>
							<td>2011/03/09</td>
							<td>$324,050</td>
						</tr>
						<tr>
							<td>Unity Butler</td>
							<td>Marketing Designer</td>
							<td>San Francisco</td>
							<td>47</td>
							<td>2009/12/09</td>
							<td>$85,675</td>
						</tr>
						<tr>
							<td>Howard Hatfield</td>
							<td>Office Manager</td>
							<td>San Francisco</td>
							<td>51</td>
							<td>2008/12/16</td>
							<td>$164,500</td>
						</tr>
						<tr>
							<td>Hope Fuentes</td>
							<td>Secretary</td>
							<td>San Francisco</td>
							<td>41</td>
							<td>2010/02/12</td>
							<td>$109,850</td>
						</tr>
						<tr>
							<td>Bruno Nash</td>
							<td>Software Engineer</td>
							<td>London</td>
							<td>38</td>
							<td>2011/05/03</td>
							<td>$163,500</td>
						</tr>
						<tr>
							<td>Sakura Yamamoto</td>
							<td>Support Engineer</td>
							<td>Tokyo</td>
							<td>37</td>
							<td>2009/08/19</td>
							<td>$139,575</td>
						</tr>
						<tr>
							<td>Thor Walton</td>
							<td>Developer</td>
							<td>New York</td>
							<td>61</td>
							<td>2013/08/11</td>
							<td>$98,540</td>
						</tr>
						<tr>
							<td>Finn Camacho</td>
							<td>Support Engineer</td>
							<td>San Francisco</td>
							<td>47</td>
							<td>2009/07/07</td>
							<td>$87,500</td>
						</tr>
						<tr>
							<td>Serge Baldwin</td>
							<td>Data Coordinator</td>
							<td>Singapore</td>
							<td>64</td>
							<td>2012/04/09</td>
							<td>$138,575</td>
						</tr>

					</tbody>
				</table>

</body>
</html>

Open in new window

Now I have to adapt it in my view code.

Eduardo Fuerte

ASKER

Hi

The datatable code is managed by a Codeigniter helper:

View code:

 <?php
		echo datatables(
				// Cabeçalho <thead> dataTables
				array(
			'id' => 'ID',
			'_name' => $this->lang->language['label_name'],
			'_hostname' => 'Host',
			'_port' => $this->lang->language['label_port'],
			//EF Maio/2017 - 01 Considera prefixo no GRID
			'_prefix' => $this->lang->language['label_prefix'],
			'_active' => 'Status',
			'acoes' => $this->lang->language['Actions'],
				),
				// Options dataTables
				array(
			'id' => 'datatables',
			'width' => '100%',
			'controller' => 'server_routes',
			'action' => 'datatables_list',
				),
				// Pesquisar dataTables
				array(
			'like' => array(
				'_name',
				'_hostname',
				'_prefix', 
			),
			'where' => array(
				'_providerId',
			),
			'search_submit' => '#pesquisar_groups_form .search_submit',
				)
		);
?>
....

<! -- The textbox value used to be used as filter: -->

        <div class="row example">

            <div class="col-xs-12 mb15">
                <div class="input-group">
                    <span class="input-group-addon ">
                        <i class="fa fa-search c-gray"></i>
                    </span>
                    <input type="text" name="_prefix_search_din" id="_prefix_search_din" class="form-control gui-input br-light light" placeholder="<?php echo $this->lang->language['label_prefix'] ?>">
                </div>
            </div>
            
        </div>

Open in new window

Then in the helper datatables.php

Where I guess is where the dynamic filter code must to be implemented:

 function redrawDataTables(where) {

                if (typeof table != "undefined" && typeof table != undefined && table != "") {

                    $("#exemplo").empty();
                    table.destroy();
                }

                if (typeof where == "undefined" || typeof where == undefined) {
                    where = "";
                }

                table =  $("#' . $options['id'] . '").DataTable({
                    "paging":   true,
                    // "pagingType": "scrolling",
                    "pagingType": "full_numbers",
                    // "sPaginationType": "four_button",
                    "retrieve": true,
                    "searching": false,
                    "ordering": true,
                    "lengthChange": false,
                    "info": false,
                    "ajax": {
                        "url": "' . base_url() . $options['controller'] . '/' . $options['action'].'",
                        "type": "post",
                        "data": where,
                        "processing": true,
                    },
                    // "columnDefs": [
                    //     {
                    //         "className": "dt-center",
                    //         "targets": "_all"
                    //     }
                    // ],
                    "language": {
                        "sEmptyTable": "Nenhum registro encontrado",
                        "sInfo": "Mostrando de _START_ até _END_ de _TOTAL_ registros",
                        "sInfoEmpty": "Mostrando 0 até 0 de 0 registros",
                        "sInfoFiltered": "(Filtrados de _MAX_ registros)",
                        "sInfoPostFix": "",
                        "sInfoThousands": ".",
                        "sLengthMenu": "_MENU_ resultados por página",
                        "sLoadingRecords": "Carregando...",
                        "sProcessing": "Processando...",
                        "sZeroRecords": "Nenhum registro encontrado",
                        "sSearch": "Pesquisar",
                        "oPaginate": {
                            "sNext": "",
                            "sPrevious": "",
                            "sFirst": "",
                            "sLast": ""
                        },
                        "oAria": {
                            "sSortAscending": ": Ordenar colunas de forma ascendente",
                            "sSortDescending": ": Ordenar colunas de forma descendente"
                        }
                    }
                });
            }
        </script>';


         $datatables .= '

            <style>
                .pagination > li:last-child > a{
                    z-index: 0;
                }
                .input-group {
                    z-index: 0;
                }
            </style>


            <table id="' . $options['id'] . '" class="table admin-form theme-warning fs13" cellspacing="0" width="' . $options['width'] . '">
                <thead>
                    <tr>';

                // var_dump($fields);

                if(array_key_exists('active_ind',$fields)) {
                    $fields = array_merge($fields, array('acoes'=>'Ações'));
                // }
                // var_dump($fields);
                }
                // exit;


                foreach ($fields as $field => $title) {
                    $datatables .= '<th>' . $title . '</th>';
                }
                $datatables .=
                          '</tr>
                        </thead>
                    </table>';

        return $datatables;
    }

Open in new window

Could you better detail how the filter code could be ?

ASKER CERTIFIED SOLUTION

lenamtl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eduardo Fuerte

ASKER

Hi lenamtl

Unfortunatelly I couldn't change the technology in this case since Datatables plugin is used in all the project.

lenamtl

Yadcf is made for Datatables you don't need to change anything to your datatables you just need add Yadcf to your page and set it.
I'm using this with for all my Datatables projects.

This is very very easy to set... Let me know if you have any question or need help for the settings.

Eduardo Fuerte

ASKER

lenamtl

Yes, better understood.

I had to stop it for a while. I will return soon to this subject, if needed with another questions.

Thank you for the help!