phpmysqlarrayscodeigniterunion

How to combine two different tables into one in codeigniter?


In this code, I have two different tables i.e. skill_master and jobs_category. Now, I want to get these two different table data into one and also convert its data into JSON format using json_encode.

$this->db->select('category');
$this->db->from('jobs_category');
$this->db->order_by('category');
$query1 = $this->db->get();
$result1 = $query1->result_array();

$this->db->select('key_skills');
$this->db->from('skill_master');
$this->db->order_by('key_skills');
$query2 = $this->db->get();
$result2 =$query2->result_array();

$arr = array();

foreach($result1 as $row)
{
    foreach($result2 as $rows)
    {
        $arr[] = $row['category'].','.$rows['skill_master'];
    }
}

$json = json_encode($arr); 
echo $json;

For example:

table1: skill_master

key_skills
==========
java
php
dot net

table2: jobs_category

category
========
IT Jobs
Air line Jobs
Hardware Jobs

Now, Here I have two tables here. Now, I want to combine these two tables and want data in JSON format like ["java", "PHP", "dot net", "IT Jobs", "Air Line Jobs", "Hardware Jobs"]. So, How can I do this? Please help me.

Thank You


Solution

  • $this->db->select('category');
    $this->db->from('jobs_category');
    $this->db->order_by('category');
    $query_category= $this->db->get();
    $result_category = $query_category->result_array();
    
    $this->db->select('key_skills');
    $this->db->from('skill_master');
    $this->db->order_by('key_skills');
    $query_skills = $this->db->get();
    $result_skills =$query_skills->result_array();
    

    If You get records from table jobs_category and skill_master like this

     $result_category = 
          [
            '0' => ['category' => 'IT Jobs'], 
            '1' => ['category' => 'Air line Jobs'],
            '2' => ['category' => 'Hardware Jobs']
          ];
       $result_skills = 
          [
           '0' => ['skill_master' => 'java'], 
           '1' => ['skill_master' => 'php'],
           '2' => ['skill_master' => 'dot net']
          ];
    
    
      $final_arr = $final_category_arr = $final_skill_arr = [];
    
      foreach($result_category as $category_row)
      {
        $final_category_arr[] = $category_row['category'];
      }
    
      foreach($result_skills as $skill_row)
      {
       $final_skill_arr[] = $skill_row['skill_master'];
      }
    
      $final_arr = array_merge($final_category_arr, $final_skill_arr);
    
     $json = json_encode($final_arr); 
     echo $json;
    

    Result will be like this

    ["IT Jobs","Air line Jobs","Hardware Jobs","java","php","dot net"]