get result from join same table join

get result from join same table join - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: get result from join same table join (/showthread.php?tid=69118)

get result from join same table join - falcon812311 - 10-10-2017

Hi,
i want retrieve each h1_status, h2_status, ..h6_status from database and show it to view.

my status_payment table:

Code:
status_id | status_name

       1  | In Progress

       2  | Paid

my user_sub_id table

Code:
id | h1_status | h2_status | h3_status | h4_status | h5_status | h6_status

1  |      1    |      1    |      1    |      1    |        1  |       1

i want get each status name in table.

what i try:

PHP Code:
 function subsetIndex($user_id, $sub_id = false)
  {
    $this->db->select('*');
    $this->db->from('user_sub_set as sub');
    $this->db->join('status_taken', 'status_taken.status_taken_id = sub.status_taken');
    $this->db->join('status_payment as h1', 'h1.status_id = sub.h1_status');
    $this->db->join('status_payment as h2', 'h2.status_id = sub.h2_status');
    $this->db->join('status_payment as h3', 'h3.status_id = sub.h3_status');
    $this->db->join('status_payment as h4', 'h4.status_id = sub.h4_status');
    $this->db->join('status_payment as h5', 'h5.status_id = sub.h5_status');
    $this->db->join('status_payment as h6', 'h6.status_id = sub.h6_status');
    $this->db->where('user_id', $user_id);
    
    if ($sub_id != false) {
      $this->db->where('sub_set_id', $sub_id);
    }
    
    $query = $this->db->get();
    
    if ($query->num_rows() > 0) {
      return $query->result();
    } else {
      return false;
    }
    
  } 

i want to get each status_name after join.

Code:
<tr>

  <td>1</td><td>Hibah 1</td>

  <td><?php echo $sub_set_detail[0]->h1_amount?></td>

  <td><?php echo $sub_set_detail[0]->status_name?></td>

  <td><?php echo $sub_set_detail[0]->h1_date?></td>

</tr>

if i change my status h2_status = 2 which mean Paid. i expect In progress chnge to Paid. but the status not change at all.

Please help me.

RE: get result from join same table join - neuron - 10-10-2017

PHP Code:
$this->db->select('sub.*, h1.status_name as h1_status_name, h2.status_name as h2_status_name'); 

//in view:

PHP Code:
<tr>
 <td>1</td>
<td>Hibah 1</td>
 <td><?php echo $sub_set_detail[0]->h1_amount?></td>
<td><?php echo $sub_set_detail[0]->h1_status_name?></td>
<td><?php echo $sub_set_detail[0]->h1_date?></td>
</tr>

<tr>
 <td>1</td>
<td>Hibah 1</td>
 <td><?php echo $sub_set_detail[0]->h2_amount?></td>
<td><?php echo $sub_set_detail[0]->h2_status_name?></td>
<td><?php echo $sub_set_detail[0]->h2_date?></td>
</tr> 

RE: get result from join same table join - falcon812311 - 10-10-2017

(10-10-2017, 08:09 AM)neuron Wrote:
PHP Code:
$this->db->select('sub.*, h1.status_name as h1_status_name, h2.status_name as h2_status_name');

//in view:

PHP Code:
<tr> <td>1</td> <td>Hibah 1</td> <td><?php echo $sub_set_detail[0]->h1_amount?></td> <td><?php echo $sub_set_detail[0]->h1_status_name?></td> <td><?php echo $sub_set_detail[0]->h1_date?></td> </tr> <tr> <td>1</td> <td>Hibah 1</td> <td><?php echo $sub_set_detail[0]->h2_amount?></td> <td><?php echo $sub_set_detail[0]->h2_status_name?></td> <td><?php echo $sub_set_detail[0]->h2_date?></td> </tr>

Its work bro. Thank you so much!