CODEWITHSUNDEEP
phpmysqljsoncodeigniter
Usama Iftikhar
Usama Iftikhar

Reputation: 183

Get json encode data from two tables on the basis of foreign key in codeigniter

I have a question for you and I have not found a solution to this, So I have 2 tables:

Categories:

id  category

1   BBA
2   BSCS
3   BEE

Sub_category:

id  category_id sub_category_name
1       1           Accountant
2       1           Manager
3       1           Sales
4       2           Data Adminsitrator
5       2           Web Developer
6       3           Airline Pilot
7       3           Airport Security Screener
8       3           Aircraft Mechanic

I want to join two table from first table I want to get category and under category I want all sub_category

my controller look like this:

public function getVehicles()
    {
        $result = $this->crud_m->getUserVehicles($data,'vehicle');
        return $this->output->set_output(json_encode(array(     
                'status' => 'OK',
                'statusCode' => '402',
                'response' => array(   
                        'description' => 'Details',
                        'category'=> $result
                )
            )));

        }

And my model is some what like this:

    public function getUserVehicles($data)
        {   
            $this->db->select('*');
            $this->db->from('categories');                      
            $this->db->join('sub_category','sub_category.category_id=categories.id');
            $this->db->group_by('categories.id');
            $query = $this->db->get();
            $result = $query->result();
            if(empty($result)){     
                return '401';
                }
            else{

                return $result; 
            }
        }

my output is now like this:

{
  "status": "OK",
  "statusCode": "402",
  "response": {
    "description": "Details",
    "category": [
      {
        "id": "1",
        "category": "BBA",
        "category_id": "1",
        "sub_category_name": "Accountant"
      },
      {
        "id": "4",
        "category": "BSCS",
        "category_id": "2",
        "sub_category_name": "Data Adminsitrator"
      },
      {
        "id": "6",
        "category": "BEE",
        "category_id": "3",
        "sub_category_name": "Airline Pilot"
      }
    ]
  }
}

And what I need is like this:

[
  {
    "categoryId": "1",
    "category": "BBA",
    "subCategory": [
      {
        "subCategoryId": "1",
        "subCategoryName": "Accountant"

      },
      {
        "subCategoryId": "2",
        "subCategoryName": "Manager"

      },
      {
        "subCategoryId": "3",
        "subCategoryName": "Sales"

      }
    ]
  },
  {
    "categoryId": "2",
    "category": "BSCS",
    "subCategory": [
      {
        "subCategoryId": "4",
        "subCategoryName": "Data Adminsitrator"

      },
      {
        "subCategoryId": "5",
        "subCategoryName": "Web developer"

      }
    ]
  }
]

Upvotes: 2

Views: 1340

Answers (1)

Jihad Mahfouz
Jihad Mahfouz

Reputation: 108

you can use the following code to get data as you like.

    public function getUserVehicles($data)
    {   
        $categories = array();
        $this->db->select('*');
        $this->db->from('categories');                      
        $query = $this->db->get();
        $result = $query->result();
        foreach($result as $r){
            $r['subCategory'] = $this->getSubCategory($r['id']);
            array_push($categories, $r);
        }
        if(empty($result)){     
            return '401';
        }else{
            return $categories; 
        }
    }


        public function getSubCategory($cat)
    {   
        $this->db->select('*');
        $this->db->from('sub_category');      
        $this->db->where('id', $cat);
        $query = $this->db->get();
        $result = $query->result();
        return $result; 
    }

Upvotes: 1

Related Questions