How do I display values from other tables in a database in php?

I am displaying all the items in my database table

There is a category table in my database as well that has these values

Table item has attributes(ItemID, Name, Price, categoryID(FK)) Table category has attributes(categoryID(PK), Name, Description)

CategoryID    Name        
1             Pizza
2             Burgers

Now when I display the items it gets displayed in this order

Item Name      Category Price(USD)
Cheese Burger  2        7
Chicken Pizza  1        10

instead of the category ID I want the Category name to be displayed like this

Item Name      Category Price(USD)
Cheese Burger  Burger   7
Chicken Pizza  Pizza    10

Here is my SQL query that I have written

if(isset($_POST['search1'])){
    $searchKey1 = $_POST['search1'];
    $sql1 = "SELECT * FROM item WHERE Name LIKE '%$searchKey1%'";

}
else{
    $sql1 = "SELECT item.*,category.Name FROM item, category where item.CategoryID=category.CategoryID";
    $searchKey1 ="";
}
$result1 = mysqli_query($conn, $sql1);

html part

<p><strong>Displaying Menu by Item</strong></p>
<p><a href="add_items.php">Add Items</a></p>
<table class="table table-bordered">
  <tr>
    <th>Item Name</th>
    <th>Category</th>
    <th>Price</th>
    <th colspan="2">Actions</th>
  </tr>
  <?php while($row = mysqli_fetch_object($result1)) {?>
  <tr>
    <td><?php echo $row->Name?></td>
    <td><?php echo $row->CategoryID?></td>
    <td><?php echo $row->Price?></td>
    <td><a href="delete_item.php?id=<?php echo $row->ItemID ?>"><i class="fa fa-trash"></i>Delete</a></td>
    <td><a href="edit_items.php?id=<?php echo $row->ItemID ?>"><i class="fas fa-pencil-alt"></i>Edit</a></td>
  </tr>
  <?php } ?>

Answer

You can add an alias in the second query:

$sql1 = "SELECT item.*,category.Name as categoryName FROM item, category where item.CategoryID=category.CategoryID";

And modify the Category column values:

<td><?php echo $row->categoryName?></td>

Advertisement

Answer