While returning duplicate data

Asked

Viewed 330 times

1

Good morning Guys, I have this query

$query = "SELECT p.*, c.nomeCategoria AS categoria FROM produto AS p INNER JOIN produto_categoria AS c"; 
$dados = mysqli_query($con, $query) or die(mysqli_error());

and a while that returns the value within a select. 

  <select name="categoriaProduto">
  <?php
   while($row = mysqli_fetch_array($dados))
   {
    ?>
  <option value="<?php echo $row["categoria"]; ?>"><?php echo $row["categoria"]; ?></option>
  <?php
 }
  ?>
</select>

only that on my page it is showing the duplicated select data, I have 3 items registered in the table categoria_product.

I was wondering if there was anything I did wrong with the code. Thank you!! Mostra de como os dados estao.

php mysql sql mysqli

  • This combo is only to display categories? if it is not necessary to Inner Join just a simple select.

  • Would then select nomeCategoria FROM categoria_produto ? is that in fact, this is the registration of products, and the combo really is only to show the registered categories, but the one that is chosen in the combo, will have the code saved in the product table.

  • you don’t have a table only for categories?

  • You have to have two queries. One for product data and one for categories. If you make a Join you need to specify the link columns with the ON. See in the documentation how to perform an Inner Join.

  • @Yeah, I got it. table products, where it has a column "categoriaProduct" of type int, and the table categoria_products where have the columns "idCategory" and "Category name".

  • A product has only one category?

  • @rray yes, it’s very simple

  • 1

    I get it, so do one select idCategoria, nomeCategoria FROM categoria_produto the idCategoria goes in combo value and nomeCategoria goes within the option.

  • @rray thanks man!!! gave right to what I needed!

Show 4 more comments

1 answer

4

SELECT p.*, c.nomeCategoria AS categoria FROM produto AS p
INNER JOIN produto_categoria AS c

The current consultation brings the products with associated categories, I imagine that there is more than one product with the same category in the table by appears repeated categories.

The correct is to change the query, make a simple select in the category table(categoria_produto) and display the result(id, name/description) in the combo.

SELECT idCategoria, nomeCategoria FROM categoria_produto

In php/html:

<option value="<?php echo $row["idCategoria"]; ?>">
   <?php echo $row["nomeCategoria"]; ?>
</option>
  • 1

    Note that this even returns categories without product. To avoid this would need to keep Join and ask distinct, without asking for product data

  • 1

    @bfavaretto and if you need to register a product with a newly created category(zero associated products), at least I thought in this case p give the answer.

  • True, it depends on the use. In the form it makes equal sense to show all.

Browser other questions tagged php mysql sql mysqli

You are not signed in. Login or sign up in order to post.