Codeigniter: Join not working with columns same name and id

Question

I wanting to be able to join two tables togeather.

How ever because I my forum table has column "name" and my forum_categories column "name"

I am not able to display both names.

On my select() if I use like $this->db->select('f.name, fc.name', false); it only displays name from forum_categories

array(1) { [0]=> array(1) { ["name"]=> string(17) "News & Discussion" } }

Question how can I get both names to show from both columns and tables.

Note: I only want to be able to use $result['name'] in my foreach loop.

So the out put I would like it to be

General

News & Discussion

Lounge

I have looked at

CodeIgniter ActiveRecord field names in JOIN statement

codeigniter - select from 2 tables with same column name

Model

public function get_forums() {
    $this->db->select('f.name, fc.name', false);
    $this->db->from('forum as f');

    // tried $this->db->join('forum_categories as fc', 'fc.forum_id = f.forum_id');

    $this->db->join('forum_categories as fc', 'fc.forum_categories_id = f.forum_id');
    $query = $this->db->get();

    if ($query->num_rows() > 0) {
        return $query->result_array();
    } else {
        return false;
    }
}

Controller

<?php

class Forums extends MY_Controller {

    public function __construct() {
        parent::__construct();
    }

    public function index() {

        $data['label'] = '';

        $data['forums'] = array();

        $results = $this->get_forums();

        var_dump($results);

        if (isset($results)) {
            foreach ($results as $result) {

                $data['forums'][] = array(

                    'name' => $result['name'], // Only want to use single variable.

                );
            }
        }

        $data['header'] = Modules::run('admin/common/header/index');
        $data['footer'] = Modules::run('admin/common/footer/index');

        $this->load->view('template/forum/list_forum_view', $data);
    }

    public function get_forums() {
        $this->db->select('f.name, fc.name', false);
        $this->db->from('forum as f');
        $this->db->join('forum_categories as fc', 'fc.forum_categories_id = f.forum_id');
        $query = $this->db->get();

        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return false;
        }
    }
}

update

works fine with code below but would rather just use one lot of join()

public function get_forums() {
    $this->db->select("*");
    $this->db->from('forum');
    $query = $this->db->get();

    foreach ($query->result_array() as $f) {
        $data[] = array(
            'name' => $f['name']
        );

        $this->db->select("*");
        $this->db->from('forum_categories');
        $query = $this->db->get();

        foreach ($query->result_array() as $fc) {
            $data[] = array(
                'name' => $fc['name']
            );
        }

    }

    return $data;
}

Answer 1

Simply use

AS

keyword, because when you try to print it, that AS keyword give you optional name for your column.

Here i have two table which have same column name "name" so i have changed those with AS keyword.

Ex:

$this->db->select('tbl_category.id_category,tbl_category.name AS cat_name,
                     tbl_subject.id_subject,tbl_subject.name AS sub_name');

In result

Array ( [0] => Array ( [id_category] => 9 [cat_name] => OL [id_subject] => 13 [sub_name] => Science )

So it is simple solution from the SQL language

Answer 2

From what i see in your updated question. What you need is UNION and not JOIN. You can use get_compiled_select() to build both query before concat with UNION.

public function get_forums() {
    $forum = $this->db->select('name')->get_compiled_select('forum');
    $forum_categories = $this->db->select('name')->get_compiled_select('forum_categories');

    $query = $this->db->query($forum.' UNION '.$forum_categories);

    if ($query->num_rows() > 0) {
        return $query->result_array();
    } else {
        return false;
    }
}

Answer 3

Try This , It Will Work .

    public function get_forums() {
    $this->db->select('f.name as forum_name, fc.name as forum_categories_name', false);
    $this->db->from('forum f');

    // tried $this->db->join('forum_categories fc', 'fc.forum_id = f.forum_id');

    $this->db->join('forum_categories fc', 'fc.forum_categories_id = f.forum_id');
    $query = $this->db->get();

    if ($query->num_rows() > 0) {
        return $query->result_array();
    } else {
        return false;
    }
}