How To Render Multiple Row In A Cell In Datatables From A Different Table?

I am creating a table using datatables and I am having some trouble rendering data in it. My Table structures are. TABLE_1 |------|------|-------| | ID | NAME | PHONE | |------|-

Solution 1:

You can transform your data in your application layer so that in resultant array you will have only rows for table a along with related category names.

First you need an ordered result set like

select a.id,
  a.phone,
  a.name,
  b.category 
from table_1 a
join table_2 b 
  on a.id = b.table_1_id
order by a.id asc

Then loop through all records and cook your data set

$result = [];
$currentParent = false;
$data = null;
foreach ($rows as $row) {
    /* 
     * if id is changed then its a different record
     * prepare data for new row and create a comma separated string of category names
     */
    if ($currentParent != $row['id']) {
        if($data != null){ // for first and intermediate rows
            $result[]= $data;
        }
        $data = ['name'=> $row['name'], 'category'=> ''];
        $currentParent = $row['id'];
    }
    $data['category'] = empty($data['category']) ? $row['category']: $data['category'] .", ". $row['category'];
}
$result[]= $data; // add data for last row
echo json_encode($result);

The resultant array would look like

Array
(
    [0] => Array
        (
            [name] => item 1
            [category] => Cat 1, Cat 2, Cat3
        )

    [1] => Array
        (
            [name] => item 2
            [category] => Cat 1, Cat 2, Cat3
        )

    [2] => Array
        (
            [name] => item 3
            [category] => Cat 1, Cat 2, Cat3
        )

)

Another shorthand way but not preferred is to apply aggregate methods on query level like if you are using MySQL you can use group_concat but it has a restriction of max character limit (which is adjustable).