Ruchika Ruchika - 4 months ago 12
MySQL Question

Fetching value from other database table based on similar field in other table

I have 2 tables


  1. new_categories
    [![enter image description here][1]][1]

  2. new_products
    [![enter image description here][2]][2]



While displaying in new_products in front end - i.e prod_cat_id - i can display in

<?php echo $item->prod_cat_id; ?>


But its resulting in numeric information like 1,2,3,4 etc

However, The prod_cat_id field in table new_products is exactly similar to id field in table new_categories

How is it possible through mysql that column prod_cat_id in table newcar_products be matched to id field in table newcar_categories and when matched the display value will be echo will be of cat_name

<?php echo $item->cat_name; ?>


Edited Revised code

Code Tried
Executed this Query in Mysql

SELECT #__new_categories.*, #__new_products.* from #__new_categories inner join #__new_products on #__new_products.prod_cat_id = #__new_categories.id


It was able to perfectly join in both table with Output as below
[![enter image description here][3]][3]

However, it seems a condition is missing in which when this code executed,
cat_name displaying all categores

It should match show only one single cat_name matching against prod_cat_id

$db = JFactory::getDBO();
$query = $db->getQuery(true);

$query = "SELECT #__new_categories.*, #__new_products.* from #__new_categories inner join #__new_products on #__new_products.prod_cat_id = #__new_categories.id";

$db->setQuery($query);
$results = $db->loadObjectList();
foreach($results as $row){
echo $row->cat_name;
}
?>


Tried this but All values in database under cat_name are displayed in instead of one cat_name against matching prod_cat_id

see the Output[![enter image description here][4]][4]

Answer

using select and inner join

 select 
     new_categories.cat_name as cat_name
   , new_categories.cat_parent as cat_parent
   , new_products.prod_name  as prod_name
 from new_categories 
 inner join new_products on new_products.prod_cat_id = new_categories.id

The query should return all the object form database that meet the join condition so for echo the result you should use a foreach

$results = $db->loadObjectList();  
foreach($results as $row){
   echo $row->cat_name . ' - ' .  $row->prod_name . '<br />' ;
}

If you need a single category you can eg (cat id = 2)

 select 
     new_categories.cat_name as cat_name
   , new_categories.cat_parent as cat_parent
   , new_products.prod_name  as prod_name
 from new_categories 
 inner join new_products on new_products.prod_cat_id = new_categories.id
 where new_categories.id = 2

or if you need only a product you can eg:product id 3

 select 
     new_categories.cat_name as cat_name
   , new_categories.cat_parent as cat_parent
   , new_products.prod_name  as prod_name
 from new_categories 
 inner join new_products on new_products.prod_cat_id = new_categories.id
 where new_products.id = 3