Because datatables do not filter the data when searching

fernarey18

Because datatables do not filter the data when searching.
Please download the code and detect the error
Use WAMP SERVER

https://drive.google.com/file/d/0B6Io7u_4OHBTUm9JcHFBemtWX1k/view

tangerine

Please explain your problem in more detail and explain what debugging you have done.
Preferably provide a link to test case showing the problem.

fernarey18

Can you download the code, mount the database and get datatables to filter when searching for a record?

puede descargar el codigo, montar la base de datos y lograr que datatables filtre al buscar un registro?

allan

I'm afraid I won't be download and executing arbitrary code. Happy to take a look at a test case if you provide a link to one though.

Allan

fernarey18

Hello Allan, excuse me, I do not speak English, I'm from Argentina.
I have to get the data from more than one table. Please download the SQL file

https://drive.google.com/file/d/0B6Io7u_4OHBTSjFGSDdidEIwZjQ/view?usp=sharing

I can not make datatables filter the data with the finder

HTML

<table id="pagos">
                    <thead>
                        <tr>
                            <th>Cliente</th>
                            <th>Producto</th>
                            <th>Kilo</th>
                            <th>Precio</th>
                            <th>Entrega</th>
                            <th>Fecha ingreso</th>
                            <th>Acción</th>
                        </tr>
                    </thead>
                 </table>

JS

$('#pagos').DataTable({
         "bProcessing": true,
         "serverSide": true,        
         "ajax":{
            url :"server_processing.php",
            type: "post"           
          }
        });

SERVER_PROCESSING.PHP
```
<?php
$hostname = 'localhost';
$dbname = 'ap_formato';
$dbusername = 'root';
$dbpassword = '';

$connection = mysql_connect($hostname, $dbusername, $dbpassword);
if(!$connection) {
die("database connecction failed." . mysql_error());
}

$db_select = mysql_select_db($dbname, $connection);
if(!$db_select) {
die("database selection failed." . mysql_error());
}

$params = $columns = $totalRecords = $data = array();
$params = $_REQUEST;
$where = $sqlTot = $sqlRec = "";

$sql = "SELECT * FROM compras ";

$sqlTot .= $sql;
$sqlRec .= $sql;

$sqlRec .= " ORDER BY fecha_ingreso DESC LIMIT ".$params['start']." ,".$params['length'];

$queryTot = mysql_query($sqlTot, $connection) or die("database error:". mysql_error($conn));
$totalRecords = mysql_num_rows($queryTot);
$queryRecords = mysql_query($sqlRec, $connection);

while( $row = mysql_fetch_array($queryRecords) ) {
$tmp = array();
$sqlcli = "select * from clientes where idcliente = '$row[idcliente]'";
$rescli = mysql_query($sqlcli);
$tempc = mysql_fetch_array($rescli);
$nombre = ucwords($tempc['nombre'].' '.$tempc['apellido']);
$tmp[0] = $nombre;
$tn = ''; $tnc = ''; $tnp = ''; $t = ''; $tne = ''; $o = 1;

   $sqlped = "select * from pedidos_ventas where idcompra = '$row[idcompra]'";
   $resped = mysql_query($sqlped);   
   while ($rowped = mysql_fetch_assoc($resped)) {
          $sqlmer = "select * from mercaderias where idmerc = '$rowped[idmercaderia]' limit 1";
          $resmer = mysql_query($sqlmer);
          $tempmer = mysql_fetch_array($resmer); 

          if ($rowped[idmercaderia] == -1)
              $tn .= 'Vianda saludable<br>';
          elseif ($rowped[idmercaderia] == -2)
              $tn .= 'Comida sana<br>';
          else 
              $tn .= ucwords($tempmer['producto']).'<br>';

          $tnc .= number_format($rowped['kilo'], 2, ',', '.').'<br>';
          $tnp .= '$'.number_format($rowped['precio'], 2,",",".").'<br>';

          if ($rowped[idmercaderia] == -1) {
              $t += round($rowped['precio'], 2);  
          }else
              $t += round($rowped['kilo'] * $rowped['precio'], 2); 

          if ($o == 1){
              $sqlent = "select * from entregas_ventas where idcompra = '$row[idcompra]'";
              $resent = mysql_query($sqlent);

              while ($rowent = mysql_fetch_assoc($resent)) {
                  $tne += $rowent['entrega'];
              }                                 
              $o++;
          }
          $tfe = explode(" ", $row['fecha_ingreso']);
          $tfe = explode("-", $tfe[0]);
     }
     $tmp[1] = $tn; 
     $tmp[2] = $tnc;
     $tmp[3] = $tnp;
     $tmp[4] = '$'.number_format($t, 2,",",".").' - $'.number_format($tne, 2,",",".");
     $tmp[5] = $tfe[2].'-'.$tfe[1].'-'.$tfe[0];
     if (round($t,2) == round($tne,2)) { 
        $tmp[6] = '<img src="img/OK_Verde.png" width="22" height="22" />';
     }else{                                     
        $tmp[6] = '<a style="cursor: pointer; display: none;"></a>&nbsp;';
        $tmp[6] .= "<a style='cursor: pointer;' onClick='toolEntrega(\"tool_entrega_venta.php?id_rec=".$row[idcompra]."\", \"Nueva entrega\", \"440\", \"180\");'><img width='18' height='18' src='img/classy/26.png' /></a>"; 
     }
     if ($_POST['deu'] && round($t,2) != round($tne,2))
         $data[] = $tmp;
     elseif (!$_POST['deu']) 
        $data[] = $tmp;      

}

$json_data = array(
"draw" => intval( $params['draw'] ),
"recordsTotal" => intval( $totalRecords ),
"recordsFiltered" => intval($totalRecords),
"data" => $data
);

echo json_encode($json_data);

<?php > ``` ?>

allan

I don't see any code in your server-side script that will search based on the value submitted by the client-side.

Do you actually need server-side processing? Are you working with 50k or more records?

Allan

fernarey18

more than 15000. And the data that I should get are in multiple tables not in a single.

I know I have to use $ params ['search'] ['value'] but I do not know how to do it when the data in multiple tables

In this example all the data is in a single table
http://phpflow.com/php/datatable-pagination-sorting-and-search-server-side-phpmysql-using-ajax/

if( !empty($params['search']['value']) ) {   
        $where .=" WHERE ( nombre LIKE '%".$params['search']['value']."%' )";    
        $where .=" OR employee_salary LIKE '".$params['search']['value']."%' ";
        $where .=" OR employee_age LIKE '".$params['search']['value']."%' )";
}

allan

With 15k rows you might get away with client-side processing. It depends how long it takes the server to read all of those rows.

You are correct that you need to handle the search parameters submitted by DataTables. They would likely need to be inserted into your join query somewhere. I'm afraid I can't write that server-side script for you though.

Allan