INNER JOIN and FOREACH

Question

INNER JOIN and FOREACH

Navigation

#1 by (0 votes)

1

I need to show the color name of one 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, only the ids that come from the table color_found as in the image are saved:

Ineedtoshowthenameofthecolorsthatareinthetablecolor_correctinsteadoftheid'sthatareintheproductstable.

Onthepageitlookslikethis:

Names would have to appear, not numbers.

php

asked by anonymous 19.07.2017 / 07:00

1 answer

How to model system actions / processes Sharing routes on express

score 0 · Answer 1

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

A product can contain many colors.

At least that's what I understood from your image. However despite having built a separate table for colors, it did not create a relationship table between the tables. Where would be stored the color id and the product id instead concatenated within a% field of the product table the% color of% of the comma-separated colors.

It's completely inadvisable to follow this way!

In your love 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 your querie, it does the following:

Select all fields in the color_fundle table relating to the product table where the produto_cor_fundo field is equal to the ids field. While the cor_fundo_id field must be equal to 1 and the produto_cor_fundo field must be equal to ativo , sorting ascending by color name.

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

There are two ways to fix this:

1st 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 query within your $cod and search for the names of all colors for each product line you searched for in the previous query.

Which 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 that there are a few colors, if there are a few hits it will also generate queue for the query on your server.

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

2nd Way, and the most correct:

Create a new table called product_cores , create a produto_cor_fundo field that will be the primary key for this table, and also create composite keys for this table: foreach and produto_cores_id , and do a relationship of produto_id using these two keys of the product and cor_fundo tables. This type is called the composite primary key.

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 the ids of their colors.

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 it will give a job to fix, but nevertheless, it will only execute a query to fetch the desired results, and on high demand of accesses the performance does not decay, it does not generate bottlenecks on the server.

It is important that you also refer to: (DER) DIAGRAM OF RELATIONSHIP ENTITIES for the next systems you wish to mount.