You need to JOIN your products and categories table, you can do something like this:

SELECT * from products LEFT JOIN categories ON products.category = categories.category_id;

Here is a working example: https://paiza.io/projects/d0wyiVd6JYPk7Acfc7svag?language=mysql

I have two tables in my database (products, category) which category is related to products by a foreign key. I would like to output all records in a table in html. The issue is that I would like to output category_name in the table instead of its id. How can I achieve that?

Here are my tables:

table products

table category

Below is the code I used to display the records:

<?php
                    $sql = "SELECT * FROM products";
                    $result = mysqli_query($mysqli, $sql);
                ?>

            <table>
                <tr>
                    <th>Product ID</th>
                    <th>Brand</th>
                    <th>Model</th>
                    <th>Product Name</th>
                    <th>Category</th>
                    <th>Quantity</th>
                    <th>Unit Price</th>
                    <th>Supplier</th>

                </tr>

                <?php
                if (mysqli_num_rows($result) > 0)
                    while($row = mysqli_fetch_array($result)){
                ?>      

                <tr>
                    <td><?php echo $row["product_id"]; ?></td>
                    <td><?php echo $row["brand"]; ?></td>
                    <td><?php echo $row["model"]; ?></td>
                    <td><?php echo $row["product_name"]; ?></td>
                    <td><?php echo $row["category"]; ?></td>
                    <td><?php echo $row["quantity"]; ?></td>
                    <td><?php echo $row["unit_price"]; ?></td>
                    <td><?php echo $row["supplier"]; ?></td>
</td>
                </tr>

                <?php           
                    }
                ?>