Select within Select

Question

Select within Select

Navigation

#1 by (0 votes)

0

I'm developing a product page that lists within each category all related products (there are several categories and several products). As the code below, it 'runs' all 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 search 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);

Bank:

(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')

(Company)

  '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 could not solve this problem yet, I tried to develop a function to select the products but it did not work yet. I'm open if you have any suggestions, ideas or solutions.

Thank you in advance!

php mysql html

asked by anonymous 11.04.2016 / 16:44

1 answer

How to install the rgeos package in R? Error in PHP Paging

score 0 · Answer 1

You can do the same Query as yes.

You can make the comparison directly by adding the two tables and giving them nicknames with "AS", and when you call 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;