Want to output from 2 tables with same column name. Using codeigniter

Question

I have 2 different tables with same columnname name tbl_specialty and tbl_student. I made a JOIN statement to join the 2 tables, now i want to output column name for table 1 and column name for table 2, how can i achieve this ? i am only getting a replication from one column. Please help.

My View

 <?php
       foreach ($delegates->result() as $row )  
 {?>
       <tr>
          <td><a href=""> <?php echo $row->name; ?></a></td>
          <td class="center"><?php echo $row->job; ?></td>
          <td class="center"><?php echo $row->name; ?></td>
          <td class="center"><?php echo $row->workplace;?></td>
          <td class="center"><?php echo $row->country_name; ?></td>
      </tr>

My model

public function delegates_per_course()  {  
  $this>db>select('tbl_student.name,tbl_student.workplace,tbl_student.job,tbl_student.dob,tbl_student.email,tbl_student.mobile,tbl_country.country_name,tbl_specialty.name,tbl_country.country_id,tbl_country.country_name');
  $this->db->from('tbl_student');
  $this->db->join('tbl_country','tbl_student.country_id=tbl_country.country_id'); 
  $this->db->join('tbl_specialty','tbl_student.specialty_id=tbl_specialty.id'); 
  $this->db->order_by("tbl_country.country_name");
  $query = $this->db->get();
  return $query;

}

Mayank Pandeyz · Accepted Answer · 2017-01-04 11:31:58Z

2

Try this:

$this->db->select('tbl_student.name as stud_name,tbl_student.workplace,tbl_student.job,tbl_student.dob,tbl_student.email,tbl_student.mobile,tbl_country.country_name,tbl_specialty.name as spec_name,tbl_country.country_id,tbl_country.country_name');

I have changed the query and create aliases for the same name columns like:

tbl_student.name as stud_name
tbl_specialty.name as spec_name

Now you can refer them properly by using there aliases. In absence of aliases tbl_specialty.name overrides values of tbl_student.name

answered Jan 4, 2017 at 11:31

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mkhululi Cooper Ncube Kamzilan Over a year ago

Hey. thanks for the reply. How will i output it on my view. Instead of foreach ($delegates -> result () as $row ) what do i write ?

Mayank Pandeyz Over a year ago

In the same way, just use $row->stud_name; and $row->spec_name; to refer these two columns

gaukhar · Accepted Answer · 2017-01-06 09:36:31Z

0

From http://www.w3schools.com/sql/sql_union.asp

Blockquote The UNION operator is used to combine the result-set of two or more SELECT statements.Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

In your case it will look like so:

SELECT columnname FROM tbl_specialty
UNION ALL
SELECT columnname FROM tbl_student;

edited Jan 6, 2017 at 9:36

answered Jan 4, 2017 at 12:25

gaukhar

1923 silver badges14 bronze badges

Collectives™ on Stack Overflow

Want to output from 2 tables with same column name. Using codeigniter

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related