codeigniter getting data array inside a datatable column

Question

I want to view multiple arrays in my data table row. I'm not sure how to do this. Data table only queries what is inside the _get_datatables_query. I cannot query for other referenced data.

public function list_projects() {
  $list = $this->foo_pro->get_datatables();
  $data = array();
  $ProjectID = $_POST['start'];
  foreach ($list as $foo_pro) {
    $ProjectID++;
    $row = array();
    // $row[] = $no;
    $row[] = $foo_pro->P_Year.$foo_pro->Code.$foo_pro->ProjectID;
    $row[] = $foo_pro->Contractor_Name;
    $row[] = $foo_pro->User_Title.'. '.$foo_pro->Dp_LastName;
    $row[] = '<div style="">&#8369;'.number_format($foo_pro->Contract_Amount, 2).'</div>';
    $row[] = $foo_pro->FirstName.' '.$foo_pro->Initial.'. '.$foo_pro->LastName.' #'.$foo_pro->Control_No;
    $row[] = '';
    $row[] = '';
    $row[] = '';
    $data[] = $row;
  }

  $output = array(
    "draw" => $_POST['draw'],
    "recordsTotal" => $this->foo_pro->count_all(),
    "recordsFiltered" => $this->foo_pro->count_filtered(),
    "data" => $data,
  );
  //output to json format
  echo json_encode($output);
}

What I want to achieve are array in 1 row and 1 column.

my join query.

$this->db->from($this->table);
$this->db->group_by('project.ProjectID');
$this->db->join('contractor', 'contractor.Contractor_No = project.Contractor_No');
$this->db->join('dpwh_user', 'dpwh_user.DPWH_User_No = project.DPWH_User_No');
$this->db->join('contractor_user', 'contractor_user.Contractor_User_No = project.Contractor_User_No');
$this->db->join('qcp', 'qcp.ProjectID = project.ProjectID');
$this->db->join('designmix', 'designmix.ProjectID = project.ProjectID');
$this->db->join('monthlyreport', 'monthlyreport.ProjectID = project.ProjectID');

If this came from two tables then why you don't try using join query to get the data from the database. @HanthonyTagam — always-a-learner
– always-a-learner, Commented Aug 4, 2017 at 4:34
i actually posted the joined statement but it was edited then. — Hanthony Tagam
– Hanthony Tagam, Commented Aug 4, 2017 at 11:18

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

In question if you have mentioned two tables than please find below mentioned query to get it done.

SELECT 
    p.ProjectId,
    p.Year,
    p.code,
    p.contract_amount,
    GROUP_CONCAT(CONCAT_WS('|',CONCAT_WS('|',mr.month,mr.year),CONCAT(mr.completion,'% <br/>'))) as monthly_report
FROM project p
INNER JOIN monthly_report mr
GROUP BY p.ProjectId;

Also I have added <br/> in group_concate() function so when you render this output in browser it will add break as per your requirement.

Let me know if it not works.

Working SQL fiddle

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 4, 2017 at 4:38

Alex Mac

2,9931 gold badge24 silver badges39 bronze badges

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

9 Comments

Alex Mac Over a year ago

@ankitsuthar: Glad and Thank you for SQL Fiddle.

Alex Mac Over a year ago

@HanthonyTagam : Let me know if it not works or other requirement

always-a-learner Over a year ago

@ShyamShingadiya looks like that <br> is not working properly in the query.

Alex Mac Over a year ago

There is again another option, in query add \n in concat_ws and while printing in html use nl2br() function, I hope this will help

always-a-learner Over a year ago

I get it little late that the <br> will work at the view. not in the SQL.

|

always-a-learner · Accepted Answer · 2017-08-05 03:37:25Z

Tho the response to this comment here is the query in active record technique.

Actually codeigniter is not very good when it comes to handle the subquerys. (as per my knowledge) I have added two options to work with subquerys. if both the querys will not get you what you wnat then i will suggest to use $this->db->query();.

I have not tested it yet.

option 1

$query = $this->db
    ->select('GROUP_CONCAT(CONCAT_WS('|',CONCAT_WS('|',mr.month,mr.year),CONCAT(mr.completion,'% <br/>'))) as monthly_report, p.ProjectId,p.Year,p.code,p.contract_amount,')    
    ->from('project p')
    ->join('monthly_report mr', 'mr.ProjectId = p.ProjectId', 'left')                               
    ->group_by('p.ProjectId')                   
    ->get();

option 2

$subquery = "(select GROUP_CONCAT(CONCAT_WS('|',CONCAT_WS('|',mr.month,mr.year),CONCAT(mr.completion,'% <br/>'))) as monthly_report 
            from monthly_report as mr
            where mr.ProjectId = p.ProjectId)";

$query = $this->db
    ->select('p.ProjectId,p.Year,p.code,p.contract_amount') 
    ->select($subquery) 
    ->from('project p')
    ->join('monthly_report mr', 'mr.ProjectId = p.ProjectId', 'left')                               
    ->group_by('p.ProjectId')                   
    ->get();

Collectives™ on Stack Overflow

codeigniter getting data array inside a datatable column

my join query.

2 Answers 2

Working SQL fiddle

9 Comments

option 1

option 2

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

my join query.

2 Answers 2

9 Comments

option 1

option 2

Comments

Your Answer

Sign up or log in

Post as a guest

Related