Mount dynamic table with php

Question

Mount dynamic table with php

Navigation

#1 by (0 votes)

0

Hello, I'm trying to build a report page in php using data from the database but I'm not able to lay the data horizontally correctly.

The logic is as follows:

The search fields are:

startingdate|enddate|stations(canbe1ormore)

Thissearchshouldreturntime,stationname,andstationvalue

Thenumberofrowsandcolumnswilldependonthesearchresult

Iwouldlikeittoshowasfollows:

SofarI'vedoneawhileshowingmethenamesofthestationshorizontally

$stm=$conexao->prepare("SELECT * FROM tb_estacoes where id_estacao in($estacoes)");
$stm->execute();
$count = $stm->rowCount();
while($re = $stm->fetchObject()){ 
echo '<td>'.$re->estacao.'</td>';
}

Now I need to complete the cells with the time and value data for each season. I tried doing using for:

$select = $conexao->prepare("SELECT a.*, b.id_estacao, b.estacao FROM tb_dados a, tb_estacoes b where a.id_estacao in($estacoes) AND a.dt_medicao BETWEEN '$dtinicio' AND '$dtfim' AND a.id_estacao = b.id_estacao");
$select->execute();
$i=0;
while($number = $select->fetchObject()){
echo "<td>" .$number->horario. "</td>";   
for ($i = 0; $i >= $count; ++$i) {     
  echo "</tr><tr>";
  $i = 0;
}
echo "<td>" .$number->valorEstacao. "</td>";
}

In this way it shows the data run (does not separate by station) then the values of 1 station, are distributed in all columns, what is wrong ..

I do not know if I have to do a select for each station, or work with arrays ..

If anyone has any idea how I can do this, I would appreciate it.

php mysql

asked by anonymous 04.05.2017 / 15:23

1 answer

contact form in HTML and PHP Grant all script error

score 0 · Answer 1

In MySQL you can use Prepared Statement to solve this problem as well ( replace the fields according to the structure of your table ):

set @sql = (
    select group_concat(distinct 
        concat(
            "sum(case when 'estacaoid'='", estacaoid, "' then 'valor' end) as '", 'estacoes'.'nome', "'"
        )
    ) 
    from horarios
    inner join estacoes on horarios.estacaoid = estacoes.id
);

set @sql = concat("select horario, ", @sql, " from horarios group by 'horario'");

prepare stmt from @sql;

execute stmt;

This code will dynamically create the columns according to the stations your table has.

Result:

IntheStackOverflowdocumentationthereisanotherexampleinEnglish: link