Difficulty to rescue information from AFFILIATE given a PRODUCT, many relationship to many

Question

I recently started my studies in the area and I have a difficulty in the code.

I need to display the data as follows:

• Product code
• Product class
• Product description
• Name of the subsidiary holding the product
• Availability (which if it exists in the stock table will be green, otherwise red)

Below is the table and column data I need to use

db_product

CODE, CLASS, DESCRIPTION

db_stock

COD_FILIAL, CODIGO_PRODUTO

db_filial

COD_FILIAL, NOME_FILIAL

That is mine code COMPLETE

<?php

 //tabela

 echo "<table border=1>";
 echo "<tr>";
 echo "<th>Codigo</th>";
 echo "<th>Descrição</th>";
 echo "<th>Classe</th>";
 echo "<th>Disponibilidade</th>";
 echo "</tr>";

//conexão

 $strcon =  mysqli_connect('localhost','s.o','b.7','s.a') or die ('Erro');
$sql = "SELECT * FROM db_produto";

$resultado = mysqli_query($strcon,$sql) or die('erro bd');

//resultados loop

while ($registro = mysqli_fetch_array($resultado))
{
$codigo = $registro['CODIGO'];
$descricao = $registro['DESCRICAO'];
$classe = $registro['CLASSE'];
echo "<tr>";
echo "<td>".$codigo;"</td>";
echo "<td>".$descricao;"</td>";
echo "<td>".$classe;"</td>";
echo "</tr>";

}

// fim while 

mysqli_close($strcon);
echo "</table>";
?>

Answer 1

Good you will need to utilize the JOIN I imagine it will clear your doubt, and it can also put everything inside the while thus filling the table with the records that will be displayed from the database, n column Disponibilidade I put the field nome_filial to fill but, you can change, I put only for you to analyze this code.

Follow the connection file and enter its name from Conn.php

Conn.php

 <?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "nomedoseubanco";

    // Criando a conexão com o banco de dados
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Checando a conexão com o banco de dados
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
?>

Also has change in the code below

<table class="display example" cellspacing="0" width="100%">
    <thead>    
        <tr>
            <th>Código do Produto</th>
            <th>Classe</th>
            <th>Descrição</th>
            <th>Filial</th>
            <th>Disponibilidade</th>
        </tr>
    </thead>
    <tbody>
        <?php
              include ("conn.php");

                $result = "SELECT A.codigo, A.classe, A.descricao, B.cod_filial, C.nome_filial FROM DB_PRODUTO A"
                        . " LEFT OUTER JOIN DB_ESTOQUE B ON (A.codigo = B.codigo_produto)"
                        . " LEFT OUTER JOIN DB_FILIAL C ON (B.cod_filial = C.cod_filial) ORDER BY A.codigo";
                $resultado = mysqli_query($conn, $result);

                $row = array();

                while ($row = mysqli_fetch_assoc($resultado)){

                    echo "<tr class='btn-default'>";
                        echo "<td>". $row['codigo'] ."</td>";
                        echo "<td>". $row['classe'] ."</td>";
                        echo "<td>". $row['descricao'] ."</td>";
                        echo "<td>". $row['cod_filial'] ."</td>";
                        echo "<td>". $row['nome_filial'] ."</td>";
                    echo "</tr>";

                }
        ?>
    </tbody>
</table>

Answer 2

Product binds with stock that binds with branch. At least that’s what the columns name indicates, mnemonically.

To accept products that are not in stock, always remember the sql Join chart, see that answer. In this case, we want what the author of the image identified as TWO LEFT OUTTER JOINS.

Applying the same image format to your case:

SELECT p.*, f.nome_filial
FROM
  db_produto p
    LEFT JOIN db_estoque e ON p.codigo = e.codigo_produto
    LEFT JOIN db_filial f ON e.cod_filial = f.cod_filial

After retrieving this information would treat for display. In this case, null branch names should not be displayed in the table and should also be red in the availability column. If not zero, only display the value in the appropriate column and green in the availability column.

Above, the treatment of which color to display would be in PHP. Can you play in the bank? Yes, it is possible. In this case, we will return in the query a new column (let’s call it generically cor_disponibilidade?), where color will be returned in RGB and then only apply in CSS (more about this here: It is possible to change the background color of an element for printing?).

In case, to play at the bank, we will check if the value of the affiliate code is null and return #ff0000 for absence, or #00ff00 for availability. The structure is basically the same:

SELECT p.*, isnull(f.nome_filial, '') as nome_filial,
    CASE
      WHEN e.cod_filial IS NULL THEN '#ff0000'
      ELSE '#00ff00'
    END as cor_disponibilidade
FROM
  db_produto p
    LEFT JOIN db_estoque e ON p.codigo = e.codigo_produto
    LEFT JOIN db_filial f ON e.cod_filial = f.cod_filial