Display mysql data using php and html

Question

Display mysql data using php and html

Navigation

#1 by (0 votes)

0

Taking this table as an example:

create table datos_prueba(
    id int not null auto_increment,
    persona int (11) not null,
    mes int (11) not null,
    anio int (11) not null
    dato int (11) not null,
    total int (11) not null,
    primary key (id)
)

By making the following query I get this data:

SELECT  dato, concat(mes,'/',anio) as mesAnio, 
sum(total) as total FROM datos_prueba
group by dato, concat(mes, '', anio)
order by dato asc, anio asc, mes asc

Taking into account that information as I can show in a table (using php for example) in this way:

mysql php

asked by Juan 30.10.2018 в 17:45

source

1 answer

Pug in angle 6 does not consider brackets or elements without value in the template Do not insert data. from the input between django and postgres

score 0 · Accepted Answer

I leave the data in an array in case someone needs it:

$mes = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12];

$mesNombre = [
            [1, "Ene"],
            [2, "Feb"],
            [3, "Mar"],
            [4, "Abr"],
            [5, "May"],
            [6, "Jun"],
            [7, "Jul"],
            [8, "Ago"],
            [9, "Sep"],
            [10, "Oct"],
            [11, "Nov"],
            [12, "Dic"]
        ];

$metricas = [
                [1, "Calidad EC"],
                [2, "Derivaciones"],
                [3, "Reingreso"],
                [4, "NPS"],
                [5, "TPA"],
                [6, "Productividad"],
                [7, "Calidad"],
                [8, "Efectividad"]
            ];

$arrayDatos = [
                [
                1, 
                    [ 
                        [1, 10],
                        [5, 20],
                        [12, 30] 
                    ]         
                ],
                [
                2, 
                    [
                        [4, 40],
                        [7, 50],
                        [9, 60]
                    ]         
                ],
                [
                3, 
                    [
                        [2, 70],
                        [6, 80],
                        [11, 90]
                    ]          
                ],
                [
                4, 
                    [
                        [3, 70],
                        [7, 87],
                        [9, 92]
                    ]          
                ],
                [
                5, 
                    [
                        [1, 70],
                        [4, 70],
                        [8, 80],
                        [11, 90],
                        [12, 100]
                    ]          
                ],
                [
                6, 
                    [
                        [1, 10],
                        [4, 30],
                        [8, 70],
                        [10, 90],
                        [12, 100]
                    ]          
                ],
                [
                7, 
                    [
                        [1, 70],
                        [3, 80],
                        [5, 90],
                        [7, 90],
                        [9, 90],
                        [11, 100]
                    ]          
                ],
                [
                8, 
                    [
                        [2, 70],
                        [4, 80],
                        [6, 90],
                        [8, 90],
                        [10, 90],
                        [12, 50]
                    ]          
                ]
              ];    

$datosMetricas = [];

?>
 <style>
    .contenedor{
        width: 90%;
        margin: auto;
   }

 </style>
<link rel="stylesheet"href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<div class="contenedor" style="margin-top: 2%;"">
<div class="row">
    <div class="col xl12 l12 m12 s12">
        <table class="table striped highlight centered" style="font-size: 11px; border: 1px solid #9e9e9e;">
            <tr style="border: 1px solid #9e9e9e;">
                <td style="background-color: #0d47a1; color: #ffffff; border: 1px solid #9e9e9e;"><b>METRICA</b></td>
                    <?php
                        for ($i=0; $i < count($mes); $i++) { 
                            echo "<td style='background-color: #0d47a1; color: #ffffff; border: 1px solid #9e9e9e;'>".$mesNombre[$i][1]."</td>";
                        }
                    ?>
            </tr>
            <?php
                for ($j=0; $j < count($metricas); $j++) { 
                    echo "<tr style='border: 1px solid #9e9e9e;'><td style='background-color: #0d47a1; color: #ffffff; border: 1px solid #9e9e9e;'>".$metricas[$j][1]."</td>";
                    for ($k=0; $k < count($mes); $k++) { 
                        array_push($datosMetricas, [$metricas[$j][0], $mes[$k]]);
                        echo "<td style='border: 1px solid #9e9e9e;'>";
                        for ($m=0; $m < count($arrayDatos[$j][1]); $m++) {
                            if (($arrayDatos[$j][0]." - ".$arrayDatos[$j][1][$m][0]) == ($metricas[$j][0]." - ".$mes[$k])) {

                                if($m == 0){
                                    echo "<div class='chip'>";
                                    echo $arrayDatos[$j][1][$m][1];
                                }
                                else{

                                    $dato = $arrayDatos[$j][1][$m][1] - $arrayDatos[$j][1][$m - 1][1];

                                    if ($arrayDatos[$j][1][$m][1] > $arrayDatos[$j][1][$m - 1][1]) {
                                        echo '
                                           <div class="chip green accent-2" style="font-size: 15px; position: relative; padding-right: 2.1rem; color: #000000;"><b>'.$arrayDatos[$j][1][$m][1].'</b>
                                                <div style="float: right; font-size: 11px !important; position: absolute; top: -3px; right: 4px">
                                                     <span>
                                                          <i class="tiny material-icons">arrow_drop_up</i>'.$dato.'
                                                     </span>
                                                </div>
                                           </div>';
                                    }
                                    else if($arrayDatos[$j][1][$m][1] == $arrayDatos[$j][1][$m - 1][1]){
                                        echo '
                                           <div class="chip green yellow" style="font-size: 15px;position: relative; padding-right: 2.1rem;"><b>'.$arrayDatos[$j][1][$m][1].'</b>
                                                <div style="float: right; font-size: 10px !important; position: absolute; top: -3px; right: 4px">
                                                     <span>
                                                          <i class="tiny material-icons">cached</i>'.$dato.'
                                                     </span>
                                                </div>
                                           </div>';
                                    }
                                    else{
                                        echo '
                                           <div class="chip red darken-1" style="font-size: 15px; position: relative; padding-right: 2.1rem; color: #ffffff;"><b>'.$arrayDatos[$j][1][$m][1].'</b>
                                                <div style="float: right; font-size: 11px !important; position: absolute; top: -3px; right: 4px">
                                                     <span>
                                                          <i class="tiny material-icons">arrow_drop_down</i>'.$dato.'
                                                     </span>
                                                </div>
                                           </div>';
                                    }

                                }
                                echo "</div>";
                            }   
                       }
                       echo "</td>";
                    }
                    echo "</tr>";
                }
            ?>
         </table>
    </div>
</div>