CODEWITHSUNDEEP
phpmysqlsqldatabasephpmyadmin
user4108200
user4108200

Reputation:

join two tables to display data

i have tried to join two tables. one is category and other is subcategory. id in category table becomes the cat id in subcategory table

category

id  name  catimage  
2   cat1  image1

subcategory

id  name     subcatimage  catid
1   subcat1   image2       2

the code is

<?php
ob_start();
require_once('config.php');

    $selectsubcategory = mysql_query("SELECT category.name, subcategory.name, category.catimage, subcategory.id, subcategory.catid FROM category INNER JOIN subcategory ON category.id=subcategory.catid ");    
    $posts = array();
    if(mysql_num_rows($selectsubcategory)) 
        {
            while($post = mysql_fetch_assoc($selectsubcategory)) 
                {
                    $posts[] = $post;
                }
            header('Content-type: application/json');
            echo stripslashes(json_encode(array('subcategorylist'=>$posts)));
        } 
    else 
        {
            header('Content-type: application/json');
            echo stripslashes(json_encode(array('subcategorylist'=>'No subcategory')));
        }
?>

I am getting all the details but the problem is that i am not getting category.name in result. can anyone help

P.S i have used sql, will change it later right now my concern is the functionality part

Upvotes: 0

Views: 368

Answers (3)

Bokka Soni
Bokka Soni

Reputation: 26

SELECT
      Category.name, category.catimage,
      subcategory.name, subcategory.id,
      subcategory.catid
FROM
     category INNER JOIN subcategory
WHERE
      category.id = subcategory.catid

Upvotes: 0

Raghu Goriya
Raghu Goriya

Reputation: 88

may this will help you...

here MainCatName will display category name field data... and SubCatName will display as a subcategory name field data...

SELECT
    `category`.`name`  AS MainCatName
    , `subcategory`.`name` AS SubCatName
FROM
    `category`
    INNER JOIN `subcategory` 
        ON (`category`.`id` = `subcategory`.`catid`);

Upvotes: 1

Alban Pommeret
Alban Pommeret

Reputation: 327

The subcategory.name overrides your category name. You have to use an alias for your subcategory name like that:

 $selectsubcategory = mysql_query("SELECT category.name, subcategory.name AS sub_name, category.catimage, subcategory.id, subcategory.catid FROM category INNER JOIN subcategory ON category.id=subcategory.catid ");

Upvotes: 2

Related Questions