0
Good evening! Well, I’ll explain what I’m trying to do. I have a table in my database that corresponds to the products and I have a page that shows these products. It happens that one of the parameters of this table relates to the category of a given product. What I need is to show the products by category using this parameter. Each category shall have one HTML Heading informing the name of the category before inserting the products relating to it below. The detail is that I already managed to do this, but in a kind of "dirty" way. I’m using a variable to set the name of a certain category and then I’m establishing a connection with the database that compares the value of the variable with the value of the category of each product, and then inserts them below the HTML Heading corresponding to that category. The bad thing is that I have to manually create multiple connections according to the number of categories present, and I think there’s a better way to do that.
I think everything will be clearer with the code.
$categoria_produto = "Comidas";
$sql = "SELECT id_produto, nome_produto, preco_produto, categoria_produto FROM produtos WHERE categoria_produto LIKE '{$categoria_produto}' ORDER BY nome_produto";
$stmt = $conexao->prepare($sql);
$stmt->execute();
$num = $stmt->rowCount();
if($num>0)
{
echo "<h1>{$categoria_produto}</h1>";
echo "<table>";
echo " <tr>";
echo " <th>NOME</th>";
echo " <th>PREÇO</th>";
echo " <th>QUANTIDADE</th>";
echo " </tr>";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
extract($row);
$preco_produto_reajustado = number_format($preco_produto, 2, ",", ".");
echo " <tr>";
echo " <td><div class='id-produto' style='display: none'>{$id_produto}</div>";
echo " <div class='nome-produto'>{$nome_produto}</div></td>";
echo " <td>R${$preco_produto_reajustado}</td>";
echo " <td>";
echo " <form class='adicionar'>";
echo " <input type='number' name='quantidade' value='1' min='1' max='20'/>";
echo " <button type='submit'>Adicionar</button>";
echo " </form>";
echo " </td>";
echo " </tr>";
}
echo "</table>";
}
else
{
echo "Sem produtos na categoria {$categoria_produto}.<br/>";
}
See why I am not liking this method. All this code above concerns only the category Comidas
! For example, if I want products referring to the category Eletrônicos
be shown also, I will have to use just below:
$categoria_produto = "Eletrônicos";
$sql = "SELECT id_produto, nome_produto, preco_produto, categoria_produto FROM produtos WHERE categoria_produto LIKE '{$categoria_produto}' ORDER BY nome_produto";
$stmt = $conexao->prepare($sql);
$stmt->execute();
$num = $stmt->rowCount();
if($num>0)
{
echo "<h1>{$categoria_produto}</h1>";
echo "<table>";
echo " <tr>";
echo " <th>NOME</th>";
echo " <th>PREÇO</th>";
echo " <th>QUANTIDADE</th>";
echo " </tr>";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
extract($row);
$preco_produto_reajustado = number_format($preco_produto, 2, ",", ".");
echo " <tr>";
echo " <td><div class='id-produto' style='display: none'>{$id_produto}</div>";
echo " <div class='nome-produto'>{$nome_produto}</div></td>";
echo " <td>R${$preco_produto_reajustado}</td>";
echo " <td>";
echo " <form class='adicionar'>";
echo " <input type='number' name='quantidade' value='1' min='1' max='20'/>";
echo " <button type='submit'>Adicionar</button>";
echo " </form>";
echo " </td>";
echo " </tr>";
}
echo "</table>";
}
else
{
echo "Sem produtos na categoria {$categoria_produto}.<br/>";
}
The output is being desired, but the code is not pleasing me.
As I said before, I do not think it is feasible to solve this problem in this way, I believe that there is a better way to do what I want. Thanks in advance for any help!
Thank you so much for the answer! I had never worked with
Foreign Key
but I saw that it is very practical and I adapted it to my code following its logic! Just a quick correction in your reply, a comma was missing after theFROM
amidprodutos p categoria c
to separate the tables, thusprodutos p, categoria c
. After this little fix everything worked perfectly, thank you very much for the assistance and for taking your time to help me optimize my code! Big hug!– M. Victor
Well noted @M.Victor, read more about relationship between tables in http://blog.thiagobelem.net/relations-tabelas-no-mysql/ Dispo Hugs
– Mastria