INNER JOIN and FOREACH

Question

I need to show the color name of a table as a result of another. Code:

<?php 
    $sqlPint = mysql_query("SELECT * FROM cor_fundo AS c 
                                    INNER JOIN produto AS p
                                    ON c.cor_fundo_id = p.produto_cor_fundo                                     
                                    WHERE c.ativo = 1 AND p.produto_id = '".$cod."' 
                                    ORDER BY c.cor_fundo_nome ASC");
    if (mysql_num_rows($sqlPint)== true):
        while ($lnPint = mysql_fetch_assoc($sqlPint)):                                          

    $checked = explode(',', $lnPint['produto_cor_fundo']);  

    foreach ($checked as $row){
            $nome = $row['cor_fundo_nome'];
            $id = $row['cor_fundo_id'];
?>                                      

<div class="col-md-4">
    <div class="product-chooser-item">              
        <div class="">
            <span class="title tt_uppercase"><?=$row['cor_fundo_nome']?></span>
            <input type="radio" name="produto_pintura_fundo"  value="<?=$id?>">
        </div>
        <div class="clear"></div>
    </div>
</div>

<? } endwhile; endif; ?>

In the product table is saved only the id’s coming from the table cor_background as in the image:

I need to show the name of the colors that are in the background table instead of the id’s that are in the products table.

On the page is showing like this:

Names would have to appear, not numbers.

Answer 1

The relationship between your tables product and bottom is of 1:N.

A product can contain many colors.

At least that’s what I understood from your image. However, despite having constructed a separate table for colors, it did not create a relationship table between the tables. Where would be stored the color id and product id instead concatenou inside a field produto_cor_fundo table product as ids of the colours separated by comma.

It is completely inadvisable to follow in this way!

In your darling you did:

SELECT * FROM cor_fundo AS c INNER JOIN produto AS p
ON c.cor_fundo_id = p.produto_cor_fundo                                     
WHERE c.ativo = 1 AND p.produto_id = '".$cod."' 
ORDER BY c.cor_fundo_nome ASC

Describing her darling, she does the following:

Select all table fields bottom relating to the table product where the field cor_fundo_id is equal to the field produto_cor_fundo. While the field ativo shall be 1 and the field produto_id must be equal to the variable $cod, Ascending by color name.

Note that it will not work because instead of having only one color id per product line, there is a concatenation of ids in the field produto_cor_fundo table product

There are two ways to fix this problem:

first way

Persist in modeling error also known as POG (gambiarra-oriented programming).

 $checked = explode(',', $lnPint['produto_cor_fundo']);  

 foreach ($checked as $row){
   $id = $row['cor_fundo_id'];       
   $sqlPint = mysql_query("SELECT cor_fundo_nome FROM cor_fundo WHERE 
   cor_fundo_id =".$id);

Create a new querie within your foreach and fetch the names of all colors for each product line searched in the previous querie.

This is completely inadvisable because if you have a product with 50 colors will completely influence the performance of your application. And even if you think they are few colors, if there are a few accesses will also generate queue for the query on your server.

Queries in the database requires a lot of memory, and generates bottleneck for reading on hard disk, remembering that the time it takes to filter the results of querie, your server is in response waiting and also consuming resources.

2nd way, and the right way:

Create a new table called product_cores, create a field produto_cores_id which will be the primary key of this table, and create composite keys for that table: produto_id and cor_fundo_id, and make a relationship of 1:N using these two key tables product and bottom. This type of modeling is given the composite primary key name.

You also need to change your product insertion and update routines in the database. To insert or update in this new table the product ids and their color ids.

Then change your query query to this:

 SELECT * FROM produto_cores pc 
 INNER JOIN produto p ON p.produto_id = pc.produto_id 
 INNER JOIN cores_fundo cf ON cf.cor_fundo_id = pc.cor_fundo_id 
 WHERE cf.ativo = 1 AND p.produto_id = ".$cod."
 ORDER BY cf.cor_fundo_nome ASC;

Ready you now have access to the colors of your products. Note, that will give a job to fix, but however execute only a querie to seek the intended results, and on high demand of access performance does not decay, does not generate bottlenecks in the server.

It is important to consult also about: (RSP) RELATIONSHIP ENTITY DIAGRAM for the next systems, which you want to assemble.