How to search all the records in the database?

Question

Help me out, guys!!!

$BuscaProdutosAtivos = $pdo->prepare("SELECT * FROM tbl_produtos p
                                      INNER JOIN tbl_categorias c ON c.cat_id = p.prod_categoria
                                      INNER JOIN tbl_variacoes v ON v.cod_prod = p.id
                                      WHERE p.prod_ativo = 1");

////////////////////////////////                                      
$BuscaProdutosAtivos->execute(); 
$BuscaProdutosAtivos->rowCount();

I’m searching for 3 types of information within the database: first I search for a product that is active to show in the store, so far so good.

Then I look for the category to which this product belongs and also everything goes well.

My product can be green, yellow or blue and that’s what I look for in the table tbl_variacoes and try to put inside a dropdown and that’s where I have trouble because it returns only the first variation, ie only the first color.

while($PA = $BuscaProdutosAtivos->fetch()){ ?>
    <select name="variacoes" class="form-control chosen-select" data-placeholder="Escolha a variação">
        <option value=""></option>
        <option value="1"><?=$PA['cor']?></option>
    </select>
?>

How to return all variations within the dropdown?

This code returns to me all base products that meet the requirements. If you can help me understand !!!!

Answer 1

With this query, the query will return all the variations registered in tbl_variacoes.

$BuscaProdutosAtivos = $pdo->prepare("SELECT * FROM tbl_produtos p
                                      INNER JOIN tbl_categorias c ON c.cat_id = p.prod_categoria
                                      INNER JOIN tbl_variacoes v ON v.cod_prod = p.id
                                      WHERE p.prod_ativo = 1");

Let’s assume you have 3 colors registered as variations, at the time you run the command while, in the generated result, would perform 3 repetitions. In your current code, will return you 3 boxes of the same product. To prevent this from happening, a small adjustment in the query:

$BuscaProdutosAtivos = $pdo->prepare("SELECT *, GROUP_CONCAT(v.cor) AS cores FROM tbl_produtos p
                                      INNER JOIN tbl_categorias c ON c.cat_id = p.prod_categoria
                                      INNER JOIN tbl_variacoes v ON v.cod_prod = p.id
                                      WHERE p.prod_ativo = 1 GROUP BY p.id");

Note that at the end of the query included the GROUP BY p.id, which will group the result by the product id in tbl_produtos and after the SELECT * included the GROUP_CONCAT(v.cor) AS cores which will concatenate the grouped result of the column v.cor, separating by ,, and defined the nickname as cores.

And in the code you shared in this link, a minor amendment will be necessary:

<select name="variacoes" class="form-control chosen-select" data-placeholder="Escolha a variação">
<option value=""></option>
<?php foreach(explode(',',$PA['cores']) as $cor) : ?>
    <option value="<?=$cor?>"><?=$cor?></option>
<?php endforeach; ?>
</select>