Answers

• tangerine Posts: 3,365Questions: 39Answers: 395

  April 2017

  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 Posts: 11Questions: 6Answers: 0

  April 2017

  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 Posts: 63,208Questions: 1Answers: 10,415 Site admin

  May 2017

  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 Posts: 11Questions: 6Answers: 0

  May 2017 edited May 2017

  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 Posts: 63,208Questions: 1Answers: 10,415 Site admin

  May 2017

  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 Posts: 11Questions: 6Answers: 0

  May 2017 edited May 2017

  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 Posts: 63,208Questions: 1Answers: 10,415 Site admin

  May 2017

  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