Help to display database data on the screen

Question

I am doing an INNER JOIN between two tables of my bank. But I’m having trouble showing the organized result on the screen, could someone help me? The tables are of category and service (related to category). In the category table I have only the id and the name of the category. Already in the service table I have several information, but I need to bring only the service code and the service name. The query I built was this:

SELECT t1.*, t2.* FROM produto_tipo AS t1 INNER JOIN categorias AS t2 ON t2.id = t1.categoria

Where PRODUTO_TIPO is the table of services and CATEGORIES is the table of category, and the FIELD category is the Foreign Key (Yes, this is strange, but I did not assemble this BD). When I run this query in phpmyadmin it returns the information I need, but without organization. It returns like this:

id     cod     categoria    id   nome_categoria
118    ho         4         4      Hospedagem
117    dev        3         3      Desenvolvimento
116    444        3         3      Desenvolvimento
119    ho2        4         4      Hospedagem
120    emmkt      5         5      Marketing
121    rs         5         5      Marketing

Well, first I’ll tell you how I want the information on the screen...

Categoria: Hospedagem (Que tem id=4)
código       Serviço
  ho         Servidor
  ho2         SMTP


Categoria: Desenvolvimento(Que tem id=3)
código       Serviço
  ho         Servidor
  ho2         SMTP


Categoria: Marketing (Que tem id=5)
código       Serviço
 emmkt       E-mail Marketing
  rs         Rede social

Remember that the information of the SERVICE field is in the services table (PRODUTO_TIPO), with the field called title.

In PHP code I’m calling this information like this:

<div class="row" align="center">
  <div class="col-lg-6">
    <h2>Categoria: <span style="color: #169F85;"><i><b>'. $row['nome_categoria'].'</b></i></span></h2>
  </div>
  <div class="col-lg-6 pull-right">
    <h2>Ações: 
      <a href="categoria_form.php?n='.$row['id'].'" class="btn btn-primary btn-xs"><i class="fa fa-edit" data-tip="tooltip" data-original-title="Editar"></i></a>
      <a href="#" onClick="javascript:Apagar(\''.$row['id'].'\');" class="btn btn-danger btn-xs"><i class="fa fa-times" data-tip="tooltip" data-original-title="Apagar"></i></a>
    </h2>
  </div>
</div>
  <tr>
    <td class="text-center"><strong>'.$row['cod'].'</strong></td>
    <td class="text-center">'.$row['titulo'].'</td>
    <td class="text-center"><font class="btn btn-xs btn-success">R$ '.Decimal($row['valorCusto']).'</font></td>
    <td class="text-center"><font class="btn btn-xs btn-success">R$ '.Decimal($row['valorFinal']).'</font></td>
    <td class="text-center">
      <a href="produtotipo_form.php?n='.$row['id'].'" class="btn btn-primary btn-xs"><i class="fa fa-edit" data-tip="tooltip" data-original-title="Editar"></i></a>
      <a href="#" onClick="javascript:Apagar(\''.$row['id'].'\');" class="btn btn-danger btn-xs"><i class="fa fa-times" data-tip="tooltip" data-original-title="Apagar"></i></a>
    </td>
  </tr>';

And this way he appears like this on the screen:

Categoria: Hospedagem
Categoria: Desenvolvimento
Categoria: Desenvolvimento
Categoria: Hospedagem
Categoria: Marketing
Categoria: Marketing

código       Serviço
emmkt       E-mail Marketing
 rs         Rede social
 dev         Servidor
 444          SMTP
 ho          Servidor
 ho2          SMTP

I don’t know how to fix this :/

Could someone help me?

From now on, thank you!

Answer 1

Have you tried using the ORDER BY in your query? Maybe using a ORDER BY nome_categoria ASC and making a little adjustments in your PHP you can show the data the way you want.

Something like that in your PHP code:

$categoria_atual = 0;
foreach($rows as $row) {
  if($categoria_atual != $row['id']) {
    $categoria_atual = $row['id'];
    echo '<tr><td colspan="5"><div class="row" align="center">
            <div class="col-lg-6">
              <h2>Categoria: <span style="color: #169F85;"><i><b>'. $row['nome_categoria'].'</b></i></span></h2>
            </div>
            <div class="col-lg-6 pull-right">
              <h2>Ações: 
                <a href="categoria_form.php?n='.$row['id'].'" class="btn btn-primary btn-xs"><i class="fa fa-edit" data-tip="tooltip" data-original-title="Editar"></i></a>
                <a href="#" onClick="javascript:Apagar(\''.$row['id'].'\');" class="btn btn-danger btn-xs"><i class="fa fa-times" data-tip="tooltip" data-original-title="Apagar"></i></a>
              </h2>
            </div>
          </div></td></tr>';
  }
  echo '<tr>
    <td class="text-center"><strong>'.$row['cod'].'</strong></td>
    <td class="text-center">'.$row['titulo'].'</td>
    <td class="text-center"><font class="btn btn-xs btn-success">R$ '.Decimal($row['valorCusto']).'</font></td>
    <td class="text-center"><font class="btn btn-xs btn-success">R$ '.Decimal($row['valorFinal']).'</font></td>
    <td class="text-center">
      <a href="produtotipo_form.php?n='.$row['id'].'" class="btn btn-primary btn-xs"><i class="fa fa-edit" data-tip="tooltip" data-original-title="Editar"></i></a>
      <a href="#" onClick="javascript:Apagar(\''.$row['id'].'\');" class="btn btn-danger btn-xs"><i class="fa fa-times" data-tip="tooltip" data-original-title="Apagar"></i></a>
    </td>
  </tr>';
}

I hope I’ve helped.