Expandable table how to do Double select?

Navigation
0

In this script one I have an expandable table Showing by GROUP by nome , that when we click on the line, will expand, showing a second table listing all the lines referring to GROUP by nome .

Closed Table:

OpenTable:HowshouldIshow

WhatIdonotknowhowtodoisforthischildtabletoshowallrecordsfortheparenttablethatshowinGROUPbynome.

<?phpinclude"config.php";
    ?>
    <script  
    src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"type="text/javascript"></script>
    <script type="text/javascript">  
    $(document).ready(function () {
    $("#report > tbody > tr").hide();
    $("#report > tbody > tr:even").addClass("odd").show();

    $("#report tr.odd").click(function () {
    $(this).next("tr").toggle();
    $(this).find(".arrow").toggleClass("up");
  });
  });
  </script>        
  </head>
  <body>
  <table id="report" border="1" style="width: 50%;">
    <thead>
        <tr>
            <th style="width: 20%;">Id</th>
            <th>Cliente</th>
            <th>Data</th>
        </tr>
    </thead>
    <tbody>
    <?php

    $sql = "SELECT * FROM a_finan GROUP by cliente ";

  $result = $PDO->query( $sql );
  $linha = $result->fetchAll();
  foreach ($linha as $row) {

  $id_empresa = $row['id_empresa'];
  $id_finan = $row['id_finan'];
  $start = $row['start'];
  $tipo = $row['tipo'];
  $valor = $row['valor'];
  $os = $row['os'];
  $cliente  = $row['cliente'];
  ?> 

  <tr>
  <td> <a href="#"><?php  echo $id_finan?> Mostrar Faturas</a></td>
       <td><?php  echo $nome_fan?></td>
       <td>30,00</td>
       </tr>
        <tr>
            <td colspan="5">
                <table>
                    <tr>
                        <td>
                        </td>
                        <td>OS</td>
                        <td>Cliente</td>
                        <td>Data</td>
                        <td>Centro Custo</td>
                        <td>Status Transacação</td>
                        <td>valor</td>
                        <td>Acao</td>
                    </tr>
                    <tr>
                        <td>
                        <input type="checkbox" />
                        </td>
                        <td><?php echo $id_finan?></td>
                        <td><?echo $cliente?></td>
                        <td>12/10/2015</td>
                        <td>xxx</td>
                        <td>xxx</td>
                        <td>30,00</td>
                        <td>Del</td>
                    </tr>
                </table>
            </td>
        </tr>
       <?}?>
    </tbody>
    </table> 
    </body>
    
asked by anonymous 13.10.2015 / 17:38

2 answers

1

Try using the following query:

SELECT distinct cliente, data, group_concat(CONCAT_WS(',',id,coluna1,coluna2,valor) SEPARATOR ';') as resultados FROM tabela GROUP BY cliente ORDER BY cliente;

Where CONCAT_WS(',',id,coluna1,coluna2,valor) will group the columns id, coluna1, coluna2 e valor and group_concat([expr] SEPARATOR ';') will group the rows and separate by ;

You will get the following result: 

+----------------+------+----------------------------------------------+
| cliente        | data | resultados                                   |
+----------------+------+----------------------------------------------+
|    CVC Turismo |   30 | 1,xxx,xxx,30;4,xxx,xxx,30;6,xxx,xxx,30;      | 
| Sterna Viagens |   30 | 2,xxx,xxx,30;3,xxx,xxx,30;5,xxx,xxx,30;      |
+----------------+------+----------------------------------------------+

The resultados column handling, you can do in PHP itself. Ex.: 

 $sql = "SELECT distinct cliente, data, group_concat(CONCAT_WS(',',id,coluna1,coluna2,valor) SEPARATOR ';') as resultados FROM tabela GROUP BY cliente ORDER BY cliente;";

 $result = $PDO->query( $sql );

 $linha = $result->fetchAll();
 foreach ($linha as $row) {
     $resultadoLinhas = explode(';',$row['resultados']);
     foreach ($resultadoLinhas as $resultadoLinha) {
         $resultadoColunas = explode(',', $resultadoLinha);
         $id      = $resultadoColunas[0];
         $coluna1 = $resultadoColunas[1];
         $coluna2 = $resultadoColunas[2];
         $valor   = $resultadoColunas[3];

         // No checkbox passe o id da linha no valor do input
         echo "<input type='checkbox' value='{$id}'>";
     }
 }

In this link you have some interesting information about the result grouping in MySQL, it's worth checking out: link

    
18.10.2015 / 12:23
0

Hello, do the following:

Do not use GROUP BY, get all the lines, let's work grouping in PHP.

You will have an array like this: 

[0] => id,cliente,data,tel,status,valor
[1] => id,cliente,data,tel,status,valor
[2] => id,cliente,data,tel,status,valor

Now use a foreach to go through all of them and group by id, client, value.

More or less like this: 

$result_novo = array();

foreach ($result as $key => $item) {
    $result_novo[$item['id']]['id']      = $item['id'];
    $result_novo[$item['id']]['cliente'] = $item['cliente'];
$result_novo[$item['id']]['detalhes'][$item['algumid']]['valor'] = $item['valor'];   

$result_novo[$item['id']]['detalhes'][$item['algumid']]['data'] = $item['data'];   


}

In the numeric key, use a valid array column.

And now to display in HTML, make two foreach, the first normal and the second inside the first, which in the example above is in the details key, so you assemble two tables, then you use a little JQuery to open and close . 

foreach $result_novo as $key => $item

echo $item['id'];
echo $item['cliente'];

if $item['detalhes'] == array {

foreach $item['detalhes'] as $key2 => $item2 {
   echo $item2['valor'];
   echo $item2['data'];
}

}

}

Note: Use only the logic of the codes.

Hugs !!

    
17.10.2015 / 23:41
Include additional files for the DLL with JNA Pass value to variable in PHP fopen