Reference data from one MySQL table by using a field from another-CodePudding

I'm trying to create an HTML table using PHP and MySQL. I have 2 MySQL tables, named coffees, and suppliers, seen here:

Coffees:

Suppliers:

In my HTML table, I wish to display the coffee name, supplier name, and total. My problem however, is that in order to display the supplier name, I must use the SUP_ID field from the coffees table to reference SUP_NAME from the suppliers table.

My final output aims to be something along the lines of this:

My code is the following:

<?php

$connection_var = new mysqli("server", "user", "pw", "db");

if (mysqli_connect_errno()) {
    printf("Connection failed: s\n", mysqli_connect_errno());
    exit();
}

$coffees = "SELECT * from coffees";
$suppliers = "SELECT * from suppliers";

$coffeesArr = mysqli_fetch_array($coffees);
$supplierArr = mysqli_fetch_array($suppliers)
    
echo("<br>");
echo("<table border='1'>");
echo("<tr><td>COF_NAME</td><td>SUPPLIER NAME</td><td>TOTAL</td></tr>");

foreach($resultArr as $row) {
    echo("<tr>");
    echo("<td>" . $row['COF_NAME'] . "</td><td>" . /* Supplier name (from 2nd table) */ . "</td><td>" . $row['TOTAL'] . "</td><td>");
    echo"</tr>");
}

mysqli_close($connection_var);
?>

As seen above, I can easily reference the COF_NAME and TOTAL fields from the first table, however I am at a loss at how to reference the SUP_NAME field from my 2nd MySQL table, as it would require me to use the SUP_ID field from the first table. I would greatly appreciate if someone could guide me on how to achieve this.

CodePudding user response：

you can make an INNER JOIN so it joins both tables

<?php

$connection_var = new mysqli("server", "user", "pass", "db");

if (mysqli_connect_errno()) {
    printf("Connection failed: s\n", mysqli_connect_errno());
    exit();
}

$sql = "SELECT c.cof_name, s.sup_name, c.total FROM COFFEES c INNER JOIN suppliers s ON c.cof_id=s.sup_id";

$result  = mysqli_query($connection_var, $sql) or die(mysqli_error($connection_var));

echo("<br>");
echo("<table border='1'>");
echo("<tr><th>COF_NAME</th><th>SUPPLIER NAME</th><th>TOTAL</th></tr>");;
    
while($row = mysqli_fetch_array($result ,MYSQLI_ASSOC)) {
    echo("<tr>");
    echo("<td>" . $row['cof_name'] . "</td><td>" . $row['sup_name'] . "</td><td>" . $row['total'] . "</td>");
    echo("</tr>");
}

mysqli_close($connection_var);

?>

Image of the table

For more MySQL documentation about the joins: https://dev.mysql.com/doc/refman/8.0/en/join.html

CodePudding user response：

A simple INNER JOIN is needed:

select c.cof_name,
       s.sup_name,
       c.total
from coffees c 
inner join suppliers s on c.sup_id=c.sup_id;

Lear more about MySQL alias

Check MySQL different types of joins:

What is the difference between "INNER JOIN" and "OUTER JOIN"? https://dev.mysql.com/doc/refman/8.0/en/join.html