Solution 1 :

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:

Problem :

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 products

table category

table category

Below is the code I used to display the records:

                    $sql = "SELECT * FROM products";
                    $result = mysqli_query($mysqli, $sql);

                    <th>Product ID</th>
                    <th>Product Name</th>
                    <th>Unit Price</th>


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

                    <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>



Comment posted by JOIN

Sounds like you need a

Comment posted by CD001

Aside: you’ve got a one-to-many relationship at the moment; if you want a product to appear in more than one category you’d have to duplicate the entire product… which is


Leave a Reply

Your email address will not be published. Required fields are marked *