How can I connect to a table and display its contents? (MySQL and PHP)

Question

I have two mysql tables (product and category). I have some mock data in both tables. Now I need to attach the category to the product somehow. For example - Product with ID 1 should return the following:

| product name | category   |
| Monitor      | Technology |

I know I've done this before, but today I can't seem to find a solution.

edit This is what I have so far. The connection works fine and I can display the data in the table.

<?php
// Include database connection
include("connection.php");

// Create variables for later use
$db = $conn;
$tableName = "Produkte";
$columns= ['id_product', 'name_product'];

// Create variable to use in index.php
$fetchData = fetch_data($db, $tableName, $columns);

// The function below feteches data from the tables specified and checks if the colums are emtpy by any chance.
function fetch_data($db, $tableName, $columns) {
    // Check db connection
    if (empty($db)) {
        $message= "Database connection error";
    }
    // Check if the columns variable is empty and not an array by any chance
    elseif (empty($columns) || !is_array($columns)) {
        $message="Product Name must be defined in an indexed array";
    }
    // Check if table name is empty
    elseif (empty($tableName)) {
        $message= "Table Name is empty";
    }
    // Else proceed as usual.
    else {
        $columnName = implode(", ", $columns);
        // The query needs to be repalced. Today my SQL stuff is leaving me a bit.
        $query = "SELECT p.".$columnName." AS product, c.name_category FROM $tableName p JOIN Kategorie c ON c.id_";
        $result = $db->query($query);
        if ($result== true) {
            if ($result->num_rows > 0) {
                $row= mysqli_fetch_all($result, MYSQLI_ASSOC);
                $message= $row;
            }
            else {
                $message= "No Data Found";
            }
        }
        // Throw error if error occures
        else{
            $message= mysqli_error($db);
        }
    }
    return $message;
}

Table products has only 2 columns. id column and product_name column.

Answer 1

The basic technology is different:

// create DBquery using JOIN statement 
$query = "
SELECT 
    p.name AS product, c.name AS category 
FROM products p
JOIN categories c ON c.id = p.category_id;";

// get DB data using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();

// show table header
printf('| product name | category   |' . PHP_EOL);

// loop for output result as table rows
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    printf('| %-12s | %10s |' . PHP_EOL, $row['product'], $row['category']);
}

Try online