How do I display item title from 1 category (tbl_category) in a table that represents another catego-CodePudding

I have 2 tables tbl_category and tbl_food.

I sorted the items from tbl_food to tbl_category by adding category_id to tbl_food, where category_id in tbl_food is identical to id in tbl_category.

But how do I take title from tbl_category and display it in a table that represents items from tbl_food?

<?php
    //query to get all admin from dtb 
    $sql = "SELECT * FROM tbl_food";
    //Exectue the query
    $res = mysqli_query($conn, $sql);
    //check if executed
    if($res==TRUE)
    {
    //count rows to check wether we have data
        $count = mysqli_num_rows($res); //get all rows in dtb

        $sn=1; //create a variable and assign the value
        //check numb of rows
        if($count>0)
        {
            //there is data
            while($rows=mysqli_fetch_assoc($res))
            {
                //using while loop to get data from dtb

                //get individual data
                $id=$rows['id'];
                $title=$rows['title'];
                $description=$rows['description'];
                $price=$rows['price'];
                $active=$rows['active'];
                $category_id=$rows['category_id'];

                
                //display values in table
                ?>
                <tr>
                        <td><?php echo $sn  ; ?></td>
                        <td><?php echo $title; ?></td>
                        <td><?php echo $description; ?></td>
                        <td><?php echo $price; ?> kn</td>
                        <td><?php echo $active; ?></td>
                        <td><?php echo $category_id; ?></td> 
</tr>
                <?php
            
    }}
    else{
        ?>
        <tr colspan="4">
            <td class="error">No categories added</td>
    </tr>
    <?php  
    }
}
  ?>

CodePudding user response：

I think you need to edit your query by adding JOIN to join tbl_category to tbl_food via category_id, at first, like this:

$sql = "SELECT food.*, category.title FROM tbl_food AS food INNER JOIN tbl_category AS category ON category.id=food.category_id";

Secondly it seems you have mistake in iterating query results. mysqli_fetch_assoc($res) returns an associated array, so it must be like this:

$rows = mysqli_fetch_assoc($res)
foreach($rows as $row){
 $id=$row['id'];
 $title=$row['title'];
 $description=$row['description'];
 $price=$row['price'];
 $active=$row['active'];
 $category_id=$row['category_id'];
 //Doing stuff
}

CodePudding user response：

You have to method for get title from tbl_category 1- JOIN Method: it is already mentioned above 2- Condition Method:

$sql = "SELECT tbl_food.*, tbl_category.title FROM tbl_food,tbl_category WHERE tbl_category.id=tbl_food.category_id";

foreach($sql as $row){

$id=$row['id'];
 $title=$row['title'];
 $description=$row['description'];
 $price=$row['price'];
 $active=$row['active'];
 $category_id=$row['category_id'];
}