How to fetch all records in the database?

Question

How to fetch all records in the database?

Navigation

#1 by (2 votes)

-1

Help me out!

$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 looking for 3 types of information within the database: first I look for a product that is active to show in the store, so far so good.

Then I look for the category that this product belongs to and everything is fine too.

My product can be green, yellow or blue and this is what I look for in the tbl_variacoes table and I try to put it inside a dropdown and this is where I have problem 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 do I return all variations within the dropdown?

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

php mysql pdo

asked by anonymous 26.11.2014 / 01:27

1 answer

Combo or Dropdownlist display a Text according to its value Sybase database connection with C #

score 2 · Accepted Answer

With this query , the query will return all the changes 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");

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

$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 query I include GROUP BY p.id , which will group the result by product id in tbl_produtos and after SELECT * includes GROUP_CONCAT(v.cor) AS cores that will concatenate the collated result of column v.cor , separating by , , and set the nickname as cores .

And in the code you shared in this link , you will need to make a small change:

<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>