Select inside of Select

Question

I am developing a product page that lists within each category all related products (they are various categories and various products). Like the code below, it 'rotates' all the categories and what I need to do, and where I need help, is to list the products.

    <?php do { ?> //Listagem das Categorias 
        <div class="row" id="<?php echo $row_ListaCategoria['descricao'];?>">
            <div class="header-content">

                <!--CATEGORIA-->
                <div class="separador"><!--Nome da Categoria-->
                    <h3><?php echo $row_ListaCategoria['descricao']; ?></h3>
                    <hr/>
                </div>

                <!--PRODUTOS--><!--Rodar todos os produtos da categoria $row_ListaCategoria['id']-->
                <div class="col-lg-3 col-md-3 col-xs-12 col-sm-6 col-lg-3-edit thumb">
                    <div class="box clearfix">

                        <!--IMAGEM PRODUTO-->
                        <a href="img/produtos/id/101.1032.png" class="thumbnail text-center thumbnail-edit thumbnail_wrapper no-border-radius pop" data-toggle="lightbox" data-title="CHICOTE PARA REPARO ALTERNADOR VW/CHICOTE P/REPARO SENSOR PRESS" data-footer="TC Chicotes">
                            <img class="img-responsive img-form-edit" src="img/produtos/id/101.1032.png" alt="#" />
                        </a>

                        <!--INFORMAÇÕES PRODUTO-->
                        <div class="product-text">
                            <h4>CHICOTE PARA REPARO ALTERNADOR VW/CHICOTE P/REPARO SENSOR PRESS</h4>
                            <p><strong>Código:</strong> 101.1152<br>
                            <strong>Aplicação:</strong> 400<br>
                            <strong>Obs:</strong> N/F</p>
                        </div>

                    </div>
                </div>  

            </div>

        </div>
    <?php } while ($row_ListaCategoria = mysql_fetch_assoc($ListaCategoria)); ?>

To find the categories I use:

mysql_select_db($database_Conect, $Conect);
$query_ListaCategoria = sprintf("SELECT * FROM tbl_categoria WHERE id_empresa = %s ORDER BY descricao", GetSQLValueString($colname_listaFabrica, "int"));
$ListaCategoria = mysql_query($query_ListaCategoria, $Conect) or die(mysql_error());
$row_ListaCategoria = mysql_fetch_assoc($ListaCategoria);
$totalRows_ListaCategoria = mysql_num_rows($ListaCategoria);

Bench:

(Category)

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `descricao` varchar(32) NOT NULL,
  `data_cadastro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id_empresa` int(11) NOT NULL,
  PRIMARY KEY (`id`)

(Enterprise)

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(100) NOT NULL,
  `data_cadastro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ativo` int(1) NOT NULL,
  PRIMARY KEY (`id`)

(Product)

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) NOT NULL,
  `tbl_categoria_id` int(11) NOT NULL,
  `tbl_empresa_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`tbl_categoria_id`)
    REFERENCES tbl_categoria(id)
    ON DELETE CASCADE,
  FOREIGN KEY (`tbl_empresa_id`)
    REFERENCES tbl_empresa(id)
    ON DELETE CASCADE

An example of how it would be shown:

I couldn’t solve this problem yet, I tried to develop a Function to select the products but it hasn’t worked yet. I’m open if you have any suggestions, ideas or solutions.

I thank you all in advance!

Answer 1

You can do it in the same Query yes.

You can make the comparison directly by adding the two tables and giving them nicknames with "AS", and when calling the fields just put the nickname in front to reference where the field comes from.

Example:

select C.campo_tabela_categoria, P.C.campo_tabela_produto 
from tbl_categoria AS C, tbl_produto AS P 
where id_empresa = %s AND C.id_categoria = P.id_categoria 
order by C.descricao

Or you can use (which would be even more correct) INNER JOIN, for example:

select C.campo_tabela_categoria, P.C.campo_tabela_produto 
from tbl_categoria AS C INNER JOIN tbl_produto AS P on C.id_categoria = P.id_categoria;

If you want to know more about joins see this post: What is the difference between INNER JOIN and OUTER JOIN?

I hope this helps;

Att;