Limit the number of columns in an HTML table by PHP

Question

I have the following table:

 <table class="table">
  <thead class="thead-dark">
    <tr>
      <th scope="col">Nome</th>
      <th scope="col">Descrição</th>
      <th scope="col">Valor</th>
      <th scope="col">Valor Desconto</th>
    </tr>
  </thead>
  <tbody>
<?php 
    $tabela = 'produtos';
    $colunas = 4;
    $limite = 0;
    echo $metodos->listarDados($tabela,$colunas,$limite);
?>
  </tbody>
  </table>

I want to create a generic method where through the settings below, define the amount of columns of the table above:

  $tabela = 'produtos';
  $colunas = 4;
  $limite = 0;

Notice that you have the variable $columns = 4, it corresponds to the number of columns that the HTML table should have, that is, referring to Name, Description, Value and Discounted Value.

With this, I call the method as below:

echo $metodos->listarDados($tabela,$colunas,$limite);

And the method lies that way:

 public function listarDados($tabela,$colunas,$limite)
    {
       $limite = ($limite == 0)?null:"LIMIT".$limite;
       $sql = mysqli_query($this->conexao,"SELECT * FROM ".$tabela." ".$limite.";");
       $visualizar = '';
       for($c = 0; $c < $colunas; $c++)
       {
         while($pe = mysqli_fetch_object($sql))
         {
             $visualizar .= '<tr>';
             // aqui incluir os valores referentes as colunas da tabela
             $visualizar .= '</tr>';
         }
       }      
   return $visualizar; 
    }

I thought I’d put the for(), but I saw that this would limit the rows and not the columns. Anyway, I would like to make it as it was defined the amount of columns in the $colunas, appear the columns of the tables corresponding to the value included in the variable.

Answer 1

Getting columns by position

If the order is the same coming from the database query just need to make a for specific within the reading of each table row.

Adjusting to your code is something like:

public function listarDados($tabela, $colunas, $limite){
    $limite = ($limite == 0)?null:"LIMIT".$limite;
    $sql = mysqli_query($this->conexao,"SELECT * FROM $tabela $limite;");

    $HTMLTabela = '';

    //ler cada linha    
    while ($linha = mysqli_fetch_array($sql)){
        $HTMLTabela .= '<tr>';

        //percorrer cada coluna por posição
        for ($i = 0; $i < $colunas; ++$i){
            $HTMLTabela .= '<td>' . $linha[$i] . '</td>';
        }

        $HTMLTabela .= '</tr>';
    }
    return $HTMLTabela; 
}

I changed the name of some variables to more suggestive names. Note that when building the string for SQL does not need to concatenate the variables and can interpolate them directly into string, as I have. I used fetch_array instead of the fetch_object that I had and I get each column with:

$linha[$i]

This is because in each line the fetch_array returns an array with defined keys for column numbers and names. If you give a print_r in a $linha see what I mean:

Array
(
    [0] => Teste
    [nome] => Teste
    [1] => uma descrição 
    [descricao] => uma descrição
    [2] => 110
    [valor] => 110
    [3] => 20
    [valor_desconto] => 20
)

So you can access by name and position.

This idea makes it easy to change the amount of columns you want to get, but on the other hand makes the code rigid, and difficult to maintain because it fixes the order of the columns. This usually becomes a problem in the future if you want to add or remove columns because it breaks this function you did (unfortunately I speak from my own experience, which I have had problems with in the past). In that case you can only add columns at the end and never remove columns under penalty of breaking the code.

Getting columns based on an array

A more flexible solution for the same scenario (though not so automatic) is to pass an array of column names to get:

$tabela = 'produtos';
$colunas = Array('nome', 'descricao', 'valor', 'valor_desconto');
$limite = 0;
echo $metodos->listarDados($tabela, $colunas, $limite);

In this case the function is considerably similar to the previous one:

public function listarDados($tabela, $colunas, $limite){
    $limite = ($limite == 0)?null:"LIMIT".$limite;
    $sql = mysqli_query($this->conexao,"SELECT * FROM $tabela $limite;");
    $HTMLTabela = '';
        
    while ($linha = mysqli_fetch_array($sql)){
        $HTMLTabela .= '<tr>';
        foreach ($colunas as $coluna){
            $HTMLTabela .= '<td>' . $linha[$coluna] . '</td>';
        }
            
        $HTMLTabela .= '</tr>';
    }
    return $HTMLTabela; 
}

The only change was really the for of the columns which has now passed to a foreach:

foreach ($colunas as $coluna){
    $HTMLTabela .= '<td>' . $linha[$coluna] . '</td>';
}