Popular table with PHP and JSON

Question

I am developing a system that has a client table, this table was not developed by me, so I am having some problems to popular.

Visually the table works perfectly, but when I try to popular with database records, they do not appear.

I have 2 files in common, index.php (Has the Table and oo Script that receives the data via Json) and getClientes(Has the query selecting the records in the database and sending in Json format).

index php.

<table id="grid" class="table table-condensed table-hover table-striped" data-selection="true" data-multi-select="false" data-row-select="true" data-keep-selection="true">
    <thead>
        <tr>
            <th data-column-id="codigo" data-order="asc" data-align="left" data-header-align="left">Código</th>
            <th data-column-id="razao" data-order="asc" data-align="left" data-header-align="left">Razão Social</th>
            <th data-column-id="bairro" data-order="asc" data-align="left" data-header-align="left">Bairro</th>
            <th data-column-id="cidade" data-order="asc" data-align="left" data-header-align="left">Cidade</th>
            <th data-column-id="status" data-formatter="link" data-sortable="false">Status</th>
            <th data-column-id="acao" data-formatter="link" data-sortable="false"></th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
    </tbody>
</table>
<script src="js/grid/jquery.bootgrid.js"></script>
<script>
    $(function() {

        $("#grid-data").bootgrid({
            ajax: true,
            url: "class/getClientes.php",
            dataType: "json",
            formatters: {
                "link": function(column, row) {
                    return "<a href=\"#\">" + column.id + ": " + row.id + "</a>";
                }
            }
        });

    });
</script>

getClientes.php

$query = mysql_query('SELECT codigo, nome, bairro, cidade  FROM clientes');
$result = mysql_fetch_array($query);
$arr = array();
if(mysql_num_rows($query) > 0) {
    while($row = mysql_fetch_assoc($query)) {
        $arr[] = $row;  
    } 
}
$json_response = json_encode($arr);
echo $json_response;

My getClientes.php file is generating this result.

I am unable to handle scrtip inside index.php so that it receives the information in JSON format and insert the rows into the table with the information.

Could you help me?

Answer 1

I could not use the API as the documentation indicates but from the documentation I see that the JSON object must have the following structure:

{
  "current": 1,
  "rowCount": 10,
  "rows": [ // ========================= aqui sim entra o seu JSON que está na imagem
    {
      "id": 19,
      "sender": "[email protected]",
      "received": "2014-05-30T22:15:00"
    },
    {
      "id": 14,
      "sender": "[email protected]",
      "received": "2014-05-30T20:15:00"
    },
    ...
  ],
  "total": 1123
}

And then it seems to me that your object needs some work. At least adding the property "Rows":

if(mysql_num_rows($query) > 0) {
    while($row = mysql_fetch_assoc($query)) {
        $arr[] = $row;  
    } 

    $resp  = array('rows' => $arr);
}
$json_response = json_encode($resp);

The way I managed to get it to work for an example in jsFiddle was like this:

var data = {
    json: JSON.stringify({
        "current": 1,
            "rowCount": 1,
            "rows": [{
            "codigo": "111",
                "razao": "nome razao",
                "bairro": "nome bairro",
                "status": "status",
                "cidade": "capital"
        }],
            "total": 1
    })
}

$.ajax({
    url: "/echo/json/",
    type: "POST",
    data: data,
    success: function (result) {
        $("#grid").bootgrid({
            formatters: {
                "link": function (column, row) {
                    console.log(column, row);
                    return "<a href=\"#\">" + column.id + ": " + row.id + "</a>";
                }
            }
        }).bootgrid("append", result.rows);
    }
});

Example: http://jsfiddle.net/fxsgakhp/