jquery datatables requests to the server with parameters

Question

jquery datatables requests to the server with parameters

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a search form where I put the NIF to search.

I'm using datatables to populate the information that echo json returns.

I have this code in javascript the data.id sends the id to the php.

function getfichauser() {
    $('#ficha').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            url: "../Logica/user/getficha.php",
            "type": "GET",
            "data": function (data) {
                 data.id = $('#nif').val();
            },
        },
            "columns": [{
            "data": "NOME"
        }, {
            "data": "MORADA"
        }, {
            "data": "LOCALIDADE"
        }]
    });
};

Now I have this code in php I get the NIF by parametero
$ nif = $ _GET ["nif"];

        $query="SELECT * from USER where NIF=:NIF";
   $db = new ligacao();
    $conn = $db->open();
    $stmt= $conn->prepare($query); 
     $stmt->bindParam(':NIF', $nif, PDO::PARAM_STR);
       $stmt->execute();
    $result = $stmt->fetchAll();

    $table = array();
    $rows = array();


        foreach ($result as $row) {
        $rows[]=$row;           
  }


   echo "{ \"data\":".json_encode($rows). "}";

jquery

asked by anonymous 07.09.2015 / 19:06

3 answers

A 'tremble' effect with while? Function Points are sufficient to consolidate a software measurement program?

score 1 · Answer 1

This example uses caixa de busca of DataTable itself to search the database.

HTML - Customer

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>DataTables - Exemplo com MySQLi &plus; PHP</title>
        <script type="text/javascript" src="https://code.jquery.com/jquery-1.11.3.min.js"></script><scripttype="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script><linkrel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" />
    </head>
    <body>
        <div id="">
            <h1 style="text-align:center; font-family: Arial; font-weight:300;">DataTables - Exemplo com MySQLi &plus; PHP </h1>
            <section>
                <table id="tabela" class="display" cellspacing="0" with="90%">
                    <thead>
                        <tr>
                            <td>Jogo</td>
                            <td>Console</td>
                            <td>Preço</td>
                        </tr>
                    </thead>

                    <tfoot>
                        <tr>
                            <td>Jogo</td>
                            <td>Console</td>
                            <td>Preço</td>
                        </tr>
                    </tfoot>
                </table>    
                <script type="text/javascript">

                    $(document).ready(function() {
                        $('#tabela').DataTable({
                            "processing": true,
                            "serverSide": true,
                            "ajax": "Data.php"
                        });
                    } );

                </script>
            </section>
        </div>
    </body>
</html>

Now, it follows the PHP script that returns the database values in JSON format.

PHP - Server

<?php
// Criado por Parvez; Ou seja qual for o nome real dele
// Exemplo tirado orignalmente de: http://phpflow.com/php/datatable-pagination-sorting-and-search-server-side-phpmysql-using-ajax/
// Modificado por mim

//Conexao
// - example é o nome do banco de dados que usei
$db = mysqli_connect("localhost", "root", "", "example");
if(mysqli_connect_errno()){
    die("Erro:".mysqli_connect_error()."(.". mysqli_connect_errno() .".)"); 
}
//var_dump($db);
//------------------------

//Variaveis Compostas
$params = $colunas = $totalConsultas = $dados = array();
//Variaveis Simples
$where = $sql_total = $sql = "";
//Variaveis HTTP
$params = $_GET;
//Indexes
$colunas = array(0 => 'nome', 1 => 'console', 2 => 'preco');

//Valor de  Busca existe ?
if(!empty($params['search']) && $params['search']['value'] !== ""){
    $where = "WHERE ";
$where .= "( nome LIKE '%{$params['search']['value']}%' ";
    $where .= "OR console LIKE '%{$params['search']['value']}%' )"; 
}

//Total de Resultados Sem Valor de Busca
$SQL = "SELECT nome, console, preco FROM jogos ";
$sql_total .= $SQL;
$sql .= $SQL;

//Concatenar termo de busca se o valor existir
if(isset($where) && $where !== ""){
    $sql_total .= $where;
    $sql .= $where; 
}

//Ordenar e definir os LIMITES/OFFSETS
$sql .= "ORDER BY {$colunas[$params['order'][0]['column']]} {$params['order'][0]['dir']} LIMIT {$params['start']}, {$params['length']}";

//Obter o numero geral de consulta existentes para calcular o numero de páginas
$query_total = mysqli_query($db, $sql_total) or die("Erro: Não foi possivel contar os resultados - ". mysqli_connect_error());
$totalConsultas = mysqli_num_rows($query_total);

//Obter o conjunto de consultas existentes
$query_sql = mysqli_query($db, $sql) or die("Erro: Nenhum resultado encontrado - ". mysqli_connect_error());
while($row = mysqli_fetch_row($query_sql)){
    $dados[] = $row;    
}

$formato_json = array(
        "draw"           => intval($params["draw"]), 
        "recordsTotal"   => intval($totalConsultas), 
        "recordsFiltered"=> intval($totalConsultas), 
        "data"           => $dados);

echo json_encode($formato_json);

?>

SQL table

--
-- Estrutura da tabela 'jogos'
--

CREATE TABLE IF NOT EXISTS 'jogos' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'nome' varchar(36) NOT NULL,
  'console' varchar(16) NOT NULL,
  'preco' decimal(6,2) NOT NULL,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'nome' ('nome')
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Extraindo dados da tabela 'jogos'
--

INSERT INTO 'jogos' ('id', 'nome', 'console', 'preco') VALUES
(1, 'Resident Evil - Retaliacao', 'XBOX 360', '3000.00'),
(2, 'The Elders Scrolls - Skyrim', 'XBOX 360', '5000.00');

Details:

The DataTables handles the request on the server side, sending parameters by url ($_GET, $_REQUEST) .

This is an example of when you open the index.html file that contains the code jQuery that creates the DataTables :

link

What happens is that the DataTables plugin immediately sends these parameters to the script / address in the url field of the Ajax call, and whenever a value is entered in caixa de busca , or when there are more than 10 resultados and the user clicks to go to the next page, a new request is made and the values returned by script selected are modified consonant the request made.

In the case of NIF , it would be the nif field in the database. To find out what the value of NIF was typed in DataTables search, we would do something like this:

$ params = $ _GET;

$ nif = $ params ['search'] ['value'];

And then in the WHERE clause of SQL you would do something like this:

WHERE nif LIKE '% $ nif%'

What would return only columns with NIF matching the search.

Example 2:

Rewriting the function for something like this:

$(document).ready(function() {
                        $('#tabela').DataTable({
                            "processing": true,
                            "serverSide": true,
                            "ajax": {
                                "url": "Data.php",
                                "data": function ( d ) {
                                    d.myKey = "myValue";
                                    // d.custom = $('#myInput').val();
                                    // etc
                                }
                            }

                        });
                    } );

In the file Data.php , if you try to return the value of $_GET['mykey'] , or if you set the value of the commented part d.custom = $('#myInput').val(); , and try to return the value in PHP using $_GET['custom'] , you will have the input with id="myinput" , or myValue in the case of $_GET['mykey'] .

These are the only ways to pass data using DataTable .

Since you can reverse the process, prioritizing the input field first, and then activating DataTable with CallBack . If this can not help you, my friend, I do not know what else.

Tip:

You can read which parameters were sent by DataTables by enabling Console Web > Rede and make refresh on the page.

In the case of the custom parameter that I defined above d.mykey I was presented with something like this:

http://
        127.0.0.1/
        projects/
        Data.php?draw=1
        &columns[0][data]=0
        &columns[0][name]=
        &columns[0][searchable]=true
        &columns[0][orderable]=true
        &columns[0][search][value]=
        &columns[0][search][regex]=false
        &columns[1][data]=1
        &columns[1][name]=
        &columns[1][searchable]=true
        &columns[1][orderable]=true
        &columns[1][search][value]=
        &columns[1][search][regex]=false
        &columns[2][data]=2
        &columns[2][name]=
        &columns[2][searchable]=true
        &columns[2][orderable]=true
        &columns[2][search][value]=
        &columns[2][search][regex]=false
        &order[0][column]=0
        &order[0][dir]=asc
        &start=0
        &length=10
        &search[value]=
        &search[regex]=false
        &myKey=myValue // Aqui, este campo dantes não existia.
        &_=1441908981287

I've set the parameters for best reading.

There is an original example of DataTables , but the request is much more complex, hence I have looked for this example. I hope this helps you, because it helped me too, I met this plugin in a way I did not even expect, but you see that it's a good match.

score 0 · Answer 2

php **'**        $rows = array();
    $table =array();
  foreach ($result as $row) {
        $rows[] = $row;
  }
   $table['data'] = $rows;
  echo json_encode($table);

'

I have this code js' **

    $( "#pesquisa" ).on( "submit", function( event ) {
          event.preventDefault();
            var pesquisa = $("#pesquisa").serialize();
     //                var nif= document.getElementById('nif').value;

          $.ajax({
                type:"GET",
                url:"../Logica/user/user.php",
                dataType:'json',
                data:pesquisa,

                success: function(data){

                     $('#tomadores').dataTable({
                       "aaData": data.data,
                        "aoColumns": [
                     { "mData": "NUMERO" },
                     { "mData": "MORADA" },
                     { "mData": "LOCALIDADE" },
                      { "mData": "TELEFONE" }
                 ]


                            });

                }
          });

    });
    </script>** '

resolved

score 0 · Answer 3

I did so in my

$('#example').DataTable({
    "ajax": "assets/controle/json.php?op=cliente_data_json",
    "columns": [
        { "data": "id" },
        { "data": "nome" },
        { "data": "razaosocial" },
        { "data": "opcoes" }
    ]
}).ajax.reload(null, false);

In my php I did so

if (isset($_REQUEST['op'])) {
    $op = $_REQUEST['op'];
}

switch ($op) {

    case 'cliente_data_json': {
        $stmt = $pdo->prepare("SELECT * FROM clientes");
        if ($stmt->execute()) {
            if ($stmt->rowCount() > 0) {
                while ($dados = $stmt->fetch(PDO::FETCH_OBJ)) {
                    $data['data'][] = array(
                        "id" => "$dados->id",
                        "nome" => "$dados->nome",
                        "razaosocial" => "$dados->razao_social",
                        "opcoes" => "<div class='btn-group btn-group-sm'><button type='button' class='btn btn-sm btn-info' id='edit_" . $dados->id . "'><i class='glyphicon glyphicon-pencil'></i>&nbsp;&nbsp;Editar</button>&nbsp;<button type='button' class='btn btn-sm btn-danger' id='del_" . $dados->id . "'><i class='glyphicon glyphicon-trash'></i>&nbsp;&nbsp;Deletar</button></div>"
                    );
                }
            }
        }
        echo json_encode($data);
    }break;

}