greenarrow greenarrow - 3 months ago 11
MySQL Question

Connecting two tables with codeigniter

First of all i am new here & sorry if this question sounds silly, here's my question.

I have two tables like this

1.jobseets

2.jobsheet_tasks

the table jobsheets stores some basic information like customer, date & the jobsheet_tasks only stores the tasks related to each job sheet.

on "jobsheets_tasks" table. the id of the "jobsheets" that belongs tasks belongs it is storing for reference purpose

here are my tables

tabels

there can be one more more tasks under a job sheet.

Relation

in order to get the information in both tables i have written the following query.

public function get_jobsheets() {
$this->db->select('jobsheets.id, jobsheets.vehicle_number, jobsheets_tasks.task');
$this->db->from('jobsheets');
$this->db->join('jobsheets_tasks', 'jobsheets_tasks.jobsheet_id = jobsheets.id');

$query = $this->db->get();


if($query->num_rows() > 0) {

foreach ($query->result()as $row){

$data[] = $row;

}
return $data ;
}


}


and i am showing the data in my view like this.

<?php
foreach($result as $jobsheet) {

?>
<tr>
<td><?php echo $customer->id;?></td>
<td><?php echo $customer->vehicle_number;?></td>
<td><?php echo $customer->task;?></td>
<?php } ?>


and the output is like this

[wrong output][3]

the query works, but the thing is as you can see in the above image the tasks named 1 & 2 are belongs to a one job sheetete which job sheet id is 61.

in the view the tasks belongs to same job sheets shown as separate rows, i want to show the data like this.

[correct output][3]

tasks of same job sheet one after another.

please assist me to achieve this. please provide your solutions in code level.

Thanks.

Answer

You can try this function in your controller.....

  public function get_jobsheets() {
      $this->db->select('jobsheets.id, jobsheets.vehicle_number, GROUP_CONCAT(jobsheets_tasks.task) as task');  
      $this->db->from('jobsheets');
      $this->db->join('jobsheets_tasks', 'jobsheets_tasks.jobsheet_id = jobsheets.id');
      $this->db->group_by('jobsheets.id, jobsheets.vehicle_number'); 
      $this->db->order_by('jobsheets.id', 'desc');
      $query = $this->db->get();
      if($query->num_rows() > 0) {
          foreach ($query->result()as $row){
              $data[] = $row;
          }
          return $data ;
      }
  }