Help to display bank data on screen

0

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

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

Where PRODUCT_TYPE is the service table and CATEGORIES is the category table, and the FIELD category is the Foreign Key (Yes, this is strange, but it was not me who set up 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

Remembering that the information in the SERVICE field is in the service table (PRODUCT_TYPE), 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 it 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 do not know how to resolve this: /

Could anyone help me?

Thank you in advance!

    
asked by anonymous 30.05.2017 / 21:03

1 answer

0

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

Something like this 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 have helped.

    
30.05.2017 / 21:29