Show JSON in table with js from php


I want to show the data I receive from a query made in a php file, in an html with a table, sounds simple, but I need to group by one of the fields that I have in the json.

The thing would be, I have this json:

[{"id_order":"1","id_order_state":"1","date_add":"2013-02-27 16:24:30"},
{"id_order":"2","id_order_state":"3","date_add":"2013-04-08 12:47:34"},...

That is infinite let's say.

What I want to do is to group the orders (id_order) in a table according to the different states they have passed through (id_order_state). As for the same id_order, there are different states I eat a little coconut as grouping them correctly.

I'm looking for a table like this:


I share the code:

$mysql = new mysql("", "root", "1234", "prueba");
if ($mysql->connect_errno) {
    $arrayData=array("error"=>"Conexión fallida: ".$mysql->connect_error);
     $consulta = "SELECT id_order, id_order_status, data_add FROM ps_order_history limit 100";
     if ($resultado = $mysql->query($consulta)) { 
        //creo que esto me podria sobrar. 
        while ($fila = $resultado->fetch_assoc()) {
$json = json_encode($arrayData);
header('Content-Type: application/json; charset=utf8');
echo $json;

And my HTMl code: (This table I did a test to see if I could paint it but it does not work for me).

 <script src=""></script>
<div class="box-body">
  <div class="table table-responsive">
    <table class="table table-hover">
      <tbody id="tableBody">

  <!-- /.box-body-->

<script type="text/javascript">
$(function() {
    function update() 
        var request = $.ajax
                url: "consulta.php", 
                method: "POST",
                dataType: "json"

                success: function(data) {
                   var valor = ''
                   data.forEach(product => {
                    valor += "<tr>"+
                    '<td>' + data.id_order_history + '</td>' +
                    '<td>' + data.id_employee + '</td>' +
                    '<td>' + data.id_order + '</td>' +
                    '<td>' + data.id_order_state + '</td>' +
                    '<td>' + data.date_add + '</td>' +
                    '<td><button class="btn btn-danger fa fa-trash"></button>&nbsp;&nbsp;<button class="btn btn-warning fa fa-pencil"></button></td>' +


I have the data inside the json, but I would like to know how to paint them in a table, making the grouping that I comment.

I hope you have explained me well and that you can help me out. After getting to organize this json, I want to make a graph with SigmaJS.

If you could explain to me a bit about how sigma goes, I have managed to make some graph but not passing data to it.

Greetings and thanks for your attention.


I add the sql query with which I am trying to make the query:

SELECT id_order,id_order_state,date_add 

FROM ps_order_history
GROUP BY id_order
asked by Peisou 28.11.2018 в 13:13

1 answer


Thank you very much A.Cedano, I have managed to solve it in the following way:

For the query I used this sentence:

$query = "SELECT id_order, id_order_state, date_add FROM ps_order_history order by date_add asc";

Afterwards to get the desired array, I made a series of loops:

    $array_nodos = array();
$array_relaciones = array();
foreach ($array_ordenado as $pedido){
    $i = 0;
    foreach ($pedido as $estado){
        if (isset($array_nodos[$estado['id_order_state']])){
        else $array_nodos[$estado['id_order_state']] = 1;

        if (isset($pedido[$i+1])){
            if (isset($array_relaciones[$pedido[$i]['id_order_state']][$pedido[$i+1]['id_order_state']])){
            else $array_relaciones[$pedido[$i]['id_order_state']][$pedido[$i+1]['id_order_state']] = 1;


A bit messy, but that's how I get the array as I need it to handle it in sigma. Thank you very much for your contributions, I would not have achieved it without them.


answered by 29.11.2018 / 11:23