Popular table with PHP and JSON

Asked

Viewed 3,970 times

1

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.

inserir a descrição da imagem aqui

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?

php jquery mysql json

  • Can you make a jsFiddle with this JSON in a variable and with your HTML and plugin loaded? So we can test. Have a look at the http://www.jquery-bootgrid.com/Documentation#formatters documentation ?

  • I already checked the documentation Sergio, but still I did not get the desired result.

  • It would be possible to make a jsFiddle?

  • Sergio, here it is: http://jsfiddle.net/jan8g6eg/ I think it’s the right way.

1 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/

  • Thank you for your attention Sergio! Helped to understand a lot how to write the code. But how would I get my getClientes.php to generate a JSON in this format with all my records var data = { json: JSON.stringify({ "Current": 1, "rowCount": 1, "Rows": [{ "code": "111", "reason": "reason name", "neighborhood": "neighborhood name", "status": "status", "city": "capital" }], "total": 1 }) } , i.e., how to generate in this format, stating "Current", "Rows", "total"....

  • @crishenrique1986 has tried the php I put in. I think that’s the essentials, the rest is information for pagination. You can add more elements to the array, type: $resp = array('rows' => $arr, 'total' => $total_de_linhas);

  • Sergio, I’ve already entered the php code you gave me, but at first, I’m using the example you gave me to see if it works. It turns out that when I use the same script that you did, the table formatting goes away, the search field does not appear, paging...column only, but when I’m removing some things from the script, trying to get back to the nearest of the original, the table back appear correctly but without the records.

  • I used the documentation SCRIPT itself, changing only the URL $("#grid-data"). bootgrid({ ajax: true, post: Function () { /* To accumulate custom Parameter with the request Object */ Return { id: "b0df282a-0d67-40e5-8558-c9e93b7befed" }; }, url: "class/Clientes.php", formatters: { "link": Function(column, Row) { Return "<a href="#">" + column.id + ": " + Row.id + "</a>"; } } }); However, it does not respect the amount of record it should have in a pagination. What can it be?

  • @crishenrique1986 I don’t know the plugin well and I don’t have much time to investigate more than I put in the reply. If you find the solution it would be interesting to put an answer here for others to learn. Ideas: your HTML has #grid in table id, this code you have placed #grid-data. From what I understand the Qtd of lines per page is closed in php, but I may be wrong.

  • OK Sergio! Thank you for your attention, thank you for helping!

Show 1 more comment

Browser other questions tagged php jquery mysql json

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