Show JSON in table with js from php

Question

Show JSON in table with js from php

Navigation

#1 by (0 votes)

0

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:

ESTADO INICIAL | ESTADO FINAL| NUMERO DE ORDENES(suma de id_order)

I share the code:

    <?php
$mysql = new mysql("192.168.0.20", "root", "1234", "prueba");
if ($mysql->connect_errno) {
    $arrayData=array("error"=>"Conexión fallida: ".$mysql->connect_error);
}else{
     $mysqli->set_charset("utf8");
     $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. 
        $arayData=array();
        while ($fila = $resultado->fetch_assoc()) {
            $arrayData[]=$fila;
        }
        $resultado->free();
     }else{
        $arrayData=array("error"=>"Error");
     }
    $mysql->close();
}
$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="https://ajax.googleapis.com/ajax/libs/jquery/1.2.3/jquery.min.js"></script>
<div class="box-body">
  <div class="table table-responsive">
    <table class="table table-hover">
      <thead>
        <tr>
          <th>#</th>
          <th>IdOrderHistory</th>
          <th>IdEmployee</th>
          <th>IdOrder</th>
          <th>IdOrderStatus</th>
          <th>DateAdd</th>
        </tr>
      </thead>
      <tbody id="tableBody">

      </tbody>
    </table>
  </div>
  <!-- /.box-body-->
</div>

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

                success: function(data) {
                  //console.log(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>' +
                    '</tr>';
                  $("#tableBody").html(valor);
                }
            });
    }

});
</script>

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.

Edit:

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

javascript php

asked by Peisou 28.11.2018 в 13:13

source

1 answer

know when the button is pressed day, week or month in full calendar Can a PreferenceFragment class be called before executing a method?

score 0 · Accepted 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']])){
            $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']])){
                $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;
        }

        $i++;
    }
}

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.

Greetings.