CODEWITHSUNDEEP
mysqlcodeignitercodeigniter-2codeigniter-3
Mkhululi Cooper Ncube Kamzilan
Mkhululi Cooper Ncube Kamzilan

Reputation: 45

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

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;

}

Upvotes: 0

Views: 897

Answers (2)

gaukhar
gaukhar

Reputation: 182

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;

Upvotes: 0

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

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

Upvotes: 2

Related Questions