How to fetch data from two join tables with same database column names in codeigniter?

22
February 12, 2019, at 2:30 PM

There are two tables 'student' and 'parent'. both tables have 'f_name' and 'l_name' columns. I used left join for those two tables.

I want to display data from those two tables. However, when I use the following code I get 'parent name' in the column where student name is supposed to be shown. I get that it happens because both tables have 'f_name' and 'l_name' columns. but How do you fix this?

Controller

function index()
{
    $this->load->model('Tableview_model');

    $student_data= $this->Tableview_model->fetch_data();
    $data["student_data"]  =  $student_data;

    $this->load->view('register_students', $data);

}

model

function fetch_data()
{
        $this->db->select('s.student_code, s.f_name, s.l_name, s.tel, p.f_name, p.l_name');
        $this->db->from('student as s');
        $this->db->join('parent as p','s.p_id=p.p_id','Left');
        $query=$this->db->get();
    if($query->num_rows() > 0) 
    {
        return $query->result();

    }else{
        return false;
    }

}

view

        <?php
        foreach ($student_data AS $row) {
            ?>
            <tr>
                <td><?php echo $row->student_code; ?></td>
                <td><?php echo $row->f_name; ?> <?php echo $row->l_name; ?></td> //I'm supposed to get student first name and last name here
                <td><?php echo $row->tel; ?></td>
                <td><?php echo $row->tel; ?></td>
                <?php
            if(isset($row->f_name) && isset($row->l_name)){ // using isset() because of LEFT JOIN
                ?>
                <td><?php echo $row->f_name; ?> <?php echo $row->l_name; ?></td> //I'm supposed to get parent first name and last name here

                <?php
                    }
                }
            ?>

output

Answer 1

You can make alias of field name as below:

    $this->db->select('s.student_code, s.f_name, s.l_name, s.tel, p.f_name as pf_name, p.l_name as pl_name');
    $this->db->from('student as s');
    $this->db->join('parent as p','s.p_id=p.p_id','Left');
    $query=$this->db->get();

And can use pf_name and pl_name in your view. Hope it helps you.

READ ALSO
PHP empty session files generated

PHP empty session files generated

I have a site where I use session authorization for my users Details are:

39
Change PDO login script to MySQLi

Change PDO login script to MySQLi

I have a problem because I don't know how I should change the ready-made login script with PDO to MySQLi ? Rest of my website is coded in MySQLiBelow its my config file

17
Yii2&#39;s mongodb collection - get all content of nested arrays

Yii2's mongodb collection - get all content of nested arrays

I have a Yii2 mongodb's collection that looks like this:

44
Mysql subquery or something better

Mysql subquery or something better

I am somewhat new to mysql and I am having an issue on how I should best write the following querySay I have a table that has a datetime column as well as a few others I want to search on

36