Popular table with PHP and JSON

1

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

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

I have 2 files in common, index.php (It has the Table and the Script that receives the data via Json) and getClients (It has the query selecting the records in the database and sending in the 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>

getClients.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 getClient.php file is generating this result.

I can not manipulate scrtip within index.php so that it receives the information in JSON format and inserts the rows into the table with the information.

Could you help me?

    
asked by anonymous 15.10.2014 / 21:54

1 answer

1

I could not use the API as the documentation indicates but from the documentation I see that the JSON object should 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 "rows" property:

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 got 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: link

    
15.10.2014 / 23:54