Retrieve variable sent in JSON in datatables

Question

Retrieve variable sent in JSON in datatables

Navigation

#1 by (1 votes)

0

Dear programmers, I come to you based on the following problem. I work on a system and I'm using Datatables. So far I have not had major problems, but now I'm with one that is pulling green hairs. I have in my mysql Database, a table called laboratories, where records with the id of related fields are kept. In this particular case, the region and city ids of a laboratory are saved. At the time of creating the table in datatables, obviously it generates the id of region and city on screen but I need to get these id in a php variable to consult in their respective tables (region and city) the names of them and thus reflect them on screen . I put part of my code where I make the calls.

Javascript code

$(document).ready(function() {
    $('#listado').dataTable({

        "ajax": "buscalab.php",

        "columns" : [
        {
            "sClass": "alignRight", 
            "data" : "region" 
        }, {
            "sClass": "alignRight", 
            "data" :  "ciudad" 

        }

Php code in search.php

$sql = "SELECT * FROM clientes WHERE tipo = 'lab'";
$resultado = mysqli_query($conexion, $sql);
if (!$resultado){
    die("Error");
}else{
    while ($data = mysqli_fetch_assoc($resultado)){
        $arreglo["data"][]= $data;
    }
    echo json_encode($arreglo);
}

mysqli_free_result($resultado);
mysqli_close($conexion);

I enclose an image of how it looks now

I appreciate any help or guidance in this regard. Greetings to all.

mysql php json ajax datatables

asked by maha1982 11.06.2017 в 03:32

source

1 answer

Submit a form by clicking on a link Error ClassNotFoundException in the return

score 1 · Accepted Answer

At the moment of creating the table in datatables, it obviously generates the region and city ids on the screen, but I need to get these ids in a php variable in order to consult their respective tables (region and city) and the names of them. reflect them on the screen. I put part of my code where I make the calls.

It is not necessary to request the ids in the query and then pass them as a condition ( WHERE ) in other queries. What you need is to combine ( JOIN ) the records of the tables using the field they have in common, that is, the primary and the foreign key. (Note: You can also join records using non-key fields).

SELECT clientes.nombre, regiones.nombre AS region, ciudades.nombre AS ciudad, telefono, email
FROM clientes
INNER JOIN regiones ON regiones.id = candidato.id_region
INNER JOIN ciudades ON ciudades.id = ciudades.id_ciudad
WHERE tipo = 'lab'

Do not forget to adapt the query according to the names of the tables and the fields (columns) of your database.