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

Question

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.

Try to use a **JOIN** instead of making two queries. That is what relational databases are made for — nacho, Apr 04 '22 at 19:52

score 1 · Answer 1 · answered Apr 04 '22 at 20:31

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

score 0 · Accepted Answer · answered Apr 04 '22 at 21:27

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

If the main focus of this Database is **to request information from the database (OLAP)** you should keep the table the **denormalized as possible**. -- "Denormalization is a technique in which we add the duplicate data to one or more table. With the help of this, we can avoid costly joins in a relational database. Denormalization is a technique to speed up read oriented data retrieval performance in a relational database" -- Documentation: [https://www.ibm.com/docs/en/db2-for-zos/11?topic=design-denormalization-tables] — David Coimbra, Apr 04 '22 at 21:39

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

2 Answers2