how to group result mysql while inside table html php [closed]

0

Good afternoon someone can give me a light I'm trying to organize the data within the while, now I can get the data and put it in the normal table so my problem is when there are many items inside a volume of it gets repeated a lot of time the name of the volume, weight and dimension. The idea would be to group the name of the volume weight and dimension and the repeater results and make one with the amount of information grouped as in the attached photo.

    
asked by anonymous 09.07.2017 / 22:32

1 answer

3

Sample table

PHPCode

$td1="";
$td2="";
$td3="";
$Linha="";
$linhaFim="";
$result="";
$pesoOld="";
$dimensaoOld="";
$dimensaoOld2="";
$z="1";

$link = new mysqli ("localhost", "USUARIO", "SENHA", "NOME_DB");

$sqlm = ("SELECT * FROM dados order by id asc");

$resultado = mysqli_query($link,$sqlm);

echo "<table border=\"1\"><tr><th>Cod.:</th><th>Produto</th><th>Qtd.:</th><th>Un.:</th><th>Volume:</th><th>Peso:</th><th>Dimensao: A.L.C.</th></tr>\n";

while($row = mysqli_fetch_assoc($resultado)) {
 $cod=$row["cod"];
  $produto=$row["produto"];
   $qtd=$row["qtd"];
    $un=$row["un"];
     $volume=$row["volume"];
      $peso=$row["peso"];
       $dimensao=$row["dimensao"];

        $Linha = "<tr>\n<td>".$cod."</td>\n<td>".$produto."</td>\n<td>".$qtd."</td>\n<td>".$un."</td>\n";

        $td1 = "<td>".$volume."</td>\n";

        $td2 = "<td class=".$volume.">".$peso."</td>\n";

        $td3 = "<td class=".$volume.">".$dimensao."</td>\n";    

        $linhaFim = "\n</tr>\n";

        $result .= $Linha.$td1.$td2.$td3.$linhaFim;   
}

$ocorrencias = substr_count($result,"<td>Caixa");

for ($k = 1; $k < $ocorrencias; $k++) {
    $num = substr_count($result,"<td>Caixa ".$k."</td>");
    if ($num > 1){

        $result =  preg_replace("#<td>Caixa ".$k."</td>#", "<td rowspan=\"".$num."\">Caixa ".$k."</td>", $result,1);
        $result =  preg_replace("#<td>Caixa ".$k."</td>#", "", $result,$num-1);
        $volume="Caixa ".$k;

        $consultar = "SELECT volume,dimensao, peso, SUM(peso) AS subt FROM (SELECT volume,dimensao,peso FROM dados Where volume='$volume' ORDER BY id ASC LIMIT $num) AS tabelaSubt";
        $resulta = mysqli_query($link, $consultar);

            while($item = mysqli_fetch_assoc($resulta)){
                $peso = $item["peso"];
                $subt = $item['subt'];
                $dimensao = $item['dimensao'];
                $volume = $item['volume'];

                $dimensaoTot="23 x 54 x 82 cm";

                //volume para 2 itens de 23 x 54 x 82 cm
                $dimensaoTot2="?? x ?? x ?? cm";

                $result =  preg_replace("#<td class=".$volume.">".$peso."</td>#", "<td rowspan=\"".$num."\">".$subt." Kg</td>", $result,1);
                $result =  preg_replace("#<td class=".$volume.">".$peso."</td>#", "", $result,$num-1);
                $result =  preg_replace("#<td class=".$volume.">".$pesoOld."</td>#", "", $result,$num-1);

                $result = preg_replace("#<td class=".$volume.">50 x 43 x 55 cm</td>#", "<td rowspan=\"".$num."\">".$dimensaoTot."</td>", $result,1);
                $result = preg_replace("#<td class=".$volume.">50 x 43 x 55 cm</td>#", "", $result,$num-1);
                $result = preg_replace("#<td class=".$volume.">".$dimensaoOld."</td>#", "", $result,1);

                $result = preg_replace("#<td class=".$volume.">23 x 54 x 82 cm</td>#", "<td rowspan=\"".$num."\">".$dimensaoTot2."</td>", $result,1);
                $result = preg_replace("#<td class=".$volume.">23 x 54 x 82 cm</td>#", "", $result,$num-1);
                $result = preg_replace("#<td class=".$volume.">".$dimensaoOld2."</td>#", "", $result,1);

                $pesoOld=$peso;
                $dimensaoOld=$dimensao;
                $dimensaoOld2=$dimensao;
            }
    }
}

echo $result;
echo "</table>";    
  

This is a basic routine and can be incremented according to the needs since the question did not give all the possible variations

    
10.07.2017 / 17:17