jquery datatables requested from the server with parameters

Asked

Viewed 2,581 times

0

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

Using datatables to fill in the information that is returned by echo json .

have this code in javascript the data.id sends the id to 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 parameter
$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

  • Hello, to your return JSON must be something like this { "data": [ {"NOME" : "Primeiro Nome", "MORDA": "Primeira Morada", "LOCALIDADE": "Primeira Localidade"} ] }, I mean, you should put the prefix data in your set of values. So: echo "{ \"data\":".json_encode($rows). "}". Try this and say what has returned.

  • yes generated me this, the problem must be in the way I am calling the php code from the java script side. the variable is well?. is that if you do form action and call the php file after Submit it generates well json does the output well.

  • Look to be honest, I’m also not used to working with plugins, mainly this that until today was strange to me, but I did tests and I succeeded in all, only in a different way. It is possible to edit the question and put the whole script ? Maybe then I can better understand the idea of how you want to pass the value.

  • some help...

  • Look, it seems that the plugin does everything internally, the exemplo the most objective I could find in their directory was this here, but it is not working, in case it would have to rewrite the script all in a remote possibility of being able to put it into operation.

  • At the moment one of the easiest solutions that comes to mind, is to return all the values in script, because I tried to see it myself GET by ajax, and the dataTable does not accept to be reset. More details are always welcome, you can always try to explain better what you want if I’m wrong with your explanation and my suggestions.

  • because I wanted and returned the result in php and called the function after doing the sumbit and so the datatables already come...but that’s not how it belongs.I want to ask the server for ajax

  • That’s exactly what I just said :/, the plugin bar some features and some options, by PHP.

  • but I think I’m getting there...

  • did not try to pass the drawing id after Submit but sends me a different id than it was supposed to lol

  • Buddy, I already have the answer you’re looking for, but I’m rewriting for the Prepared Statments what’s been a bit of a hassle. It has some problem if I answer with the MySQLi normal ?

  • well I am using sql server and I am using PDO

  • but to open the datatables has to be so xD or it does not open... $(Document). ready(Function () {

  • I saw this example but tbm did not give https://www.youtube.com/watch?v=i41WoX-B5f4

  • There’s an error here in the script I rewrote, I’m trying to correct, then I answer the question, but I’ll use MySQLi then you can try to adapt.

Show 10 more comments

3 answers

1

This example here uses the caixa de busca of own DataTable to perform database searches.

HTML - Client

<!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>
        <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
        <link rel="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, follow the script PHP which returns the database values in the format JSON.

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 containing the code jQuery that creates the DataTables:

http://localhost/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][[[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[]=&search value[regex]=false&_=1441820166780 0

What happens is that the plugin DataTables send these parameters to the script/address in the field url of the call Ajax, 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 is modified according to the request made.

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

$params = $_GET;

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

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

WHERE nif LIKE '%$nif%'

What would return only columns with the NIF match 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 define 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 value of input with id="myinput", or myValue in the case of $_GET['mykey'].

These are the only ways to pass data using the DataTable.

Since you can reverse the process, prioritizing the reading of the fields input, and only then activate the DataTable through a CallBack. If this can’t help you, buddy, I don’t know what else.

Tip:

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

In the case of the custom parameter you set up there d.mykey something like this was presented to me:

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 separated the parameters for better 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 also helped me, I met this plugin in a way I didn’t expect, but you can tell it’s a good match.

  • HELLO but in your script you don’t pass parameters

  • I think I’ve solved the problem :) "aaData": data.data, this xD was missing

  • The script passes parameters on URL for input search, the table is formatted internally by the output JSON that the PHP returns. Now that you say that, I can’t understand what you really want.

  • hello I have a search form where I send by parameter the form data... I used the serialize tool to fetch the information...this params I’ve never heard of but in your DATA.php you do not have to send it by parameter?

  • now I have another problem, tomorrow I will test your solution.... I want to put a loading by clicking on the search button put a loading on a div and disappear when you have the data on the client side shows the datatables

  • Look, I’m starting to get more and more confused about what you really want, and I don’t think I can help you solve the problem, or even get close, and your explanations aren’t the clearest.

  • This was probably the last edição which I made in my reply, since I have nothing more to explain in it, because I think it is quite clear, and I have gathered as much information as possible, I hope you will make good use of this information, friend.

  • not this clear...the issue is that your example is not to fetch information the datatables

  • I’ll try to do it your way...

  • Let’s go continue this discussion in chat.

Show 5 more comments

0

I did so in mine

$('#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;

}

  • And when you want to give a load in the table after you include a record only put after the inclusion ajax the command: $('#example'). Datatable().ajax.Reload(null, false); Let it update the automatic datatable without refresh.

  • in the ajax datatable url you can also concatenate ex: "ajax": "Assets/control/json.php? op=cliente_data_json&parametro1=" + parametro1 + "&parametro2=" + parametro2 + "",

  • var data = "Assets/control/json.php? op=cliente_data_json" + "&parametro1=" + parametro1,

0

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

`

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>** `

solved

Browser other questions tagged jquery

You are not signed in. Login or sign up in order to post.