0

I am fetching data from 3 tables with the help of join. My output is in JSON format.

Output in JSON format:-

{"order_id":"2", 
 "location_address":"Testing location Delhi",
 "emp_first_name":"**Ranjeet**",
 "order_status":"0",
 "from_date":"0000-00-00 00:00:00",
 "actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

{"order_id":"2",
"location_address":
"Testing location Delhi",
"emp_first_name":"**abhijeet**",
"order_status":"0",
"from_date":"0000-00-00 00:00:00",
"actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

The problem is, In above output, All the data is same but emp_first_name are different because both are having seperate id's. Now i want to combine the data of both the emp_first_name and display it as single output like

The output which i want :-

{"order_id":"2",
 "location_address":"Testing location Delhi",
 "emp_first_name":"**Ranjeet,abhijeet**",
 "order_status":"0",
 "from_date":"0000-00-00 00:00:00",
 "actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

QUery I Used :-

$this->datatables
   ->select('o.order_id, 
        concat_ws(" " , `l.location_street`, `l.location_city` ) as location_address, 
        e.emp_first_name, o.order_status, o.from_date')
   ->from('orders o')
   ->join('location l', 'o.location_id = l.location_id')
   ->join('order_details od', 'od.order_id = o.order_id')
   ->join('employee e', 'e.emp_id = od.emp_id');

Any help would be appreciated. Thank you in advance.

3

You should use GROUP_CONCAT on emp_first_name. Maybe something like:

$this->datatables
   ->select('o.order_id, 
        concat_ws(" " , `l.location_street`, `l.location_city` ) as location_address, 
        group_concat(e.emp_first_name SEPARATOR ','), o.order_status, o.from_date')
   ->from('orders o')
   ->join('location l', 'o.location_id = l.location_id')
   ->join('order_details od', 'od.order_id = o.order_id')
   ->join('employee e', 'e.emp_id = od.emp_id')
   ->group_by('o.order_id');
  • The group_by gets rid of the dup rows of output. – Rick James Mar 28 '15 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.