CODEWITHSUNDEEP
phpmysqlsql
dipak dutta
dipak dutta

Reputation: 274

Show mysql database data from other table

I have two tables. one advt and second category. I need to show data from table category where cat_id comes from advt. I am unable to show categories.

My problem is how will I show category, subcategory and subcat names instead of cat_id. I am able to show cat_id but failed to show category names from category table where name is as product

table- advt:

enter image description here

table - category:

enter image description here

My php code is as below:

<?php 
include('include/config.php');


if($stmt = $connection->prepare("SELECT 
                                 a.ad_id, 
                                 a.cus_id,
                                 a.ad_name,
                                 a.category,
                                 a.subcategory,
                                 a.subcat
                                 FROM `advt` a 
                                 INNER JOIN `category` b
                                 ON a.category = b.cat_id AND a.subcategory = b.cat_id AND a.subcat = b.cat_id GROUP BY a.ad_id
                                 ")){ 


       $stmt->execute(); 
       $stmt->store_result(); 
       $stmt->bind_result($ad_id, $cus_id, $ad_name, $category, $subcategory, $subcat);   

       while($stmt->fetch()){ 
       ?>

        <tr class="example">
            <td><a href="details1/<?php echo $cus_id;?>/<?php echo $ad_id;?>"><?php echo  "<img src=ad/data/img/$cus_id/$ad_id/$img_name  width='180' height='120'><br>";?></a></td>
            <td><?php echo $ad_id; ?></td>
            <td><?php echo $cus_id; ?></td>
            <td><?php echo $ad_name; ?></td>
            <td><?php echo $category; ?></td>
            <td><?php echo $subcategory; ?></td>
            <td><?php echo $subcat; ?></td>
         </tr>

        <?php
       } 
       $stmt->close();
       } 

?>

Upvotes: 0

Views: 119

Answers (2)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

If you just want the category name then use the below query.

JOIN both the tables on cat_id then add product to the SELECT part and remove the GROUP BY(we assume that category table won't have duplicate cat_id).

SELECT a.ad_id, 
       a.cus_id,
       a.ad_name,
       a.category,
       a.subcategory,
       a.subcat,
       b.product
FROM `advt` a 
INNER JOIN `category` b ON a.category = b.cat_id

Upvotes: 0

Mittal Patel
Mittal Patel

Reputation: 2762

If your category, subcategory and subcat all are references to the category table then to get the name of the category can be get by using below query:

SELECT 
    a.ad_id, 
    a.cus_id,
    a.ad_name,
    b.product as category,
    c.product as subcategory,
    d.product as subcat
FROM `advt` a 
INNER JOIN `category` b
ON a.category = b.cat_id 
INNER JOIN `category` c
ON a.subcategory = c.cat_id 
INNER JOIN `category` d
ON a.subcat = d.cat_id

Upvotes: 1

Related Questions