populating datatable child rows with PHP generated JSON data.

indymx

I've worked out the childrows, but now I'm wanting to populate rows with data from a "nested" array. I've tried a few things, but so far everything returns "undefined"

The JSON data I am building looks like this:

[code]

{
data: [
{
id: 31,
customer: "Customer 4",
shipper: "Company 1",
consignee: "Company 2",
tracking: "L353109",
carrier: "Carrier 3",
carrierPro: "123456",
pickupTime: "02-27 22:02",
deliveryTime: "02-27 22:02",
nextUpdate: "02-28 13:02",
status: "En Route",
sendto: "terryg@homeoffice.com",
contact: "555-123-4321",
update: "",
comments: [
{
0: 60,
1: 31,
2: "This is a test",
3: "2014-03-03 00:23:09",
4: "terryg",
5: "1235",
6: "2014-02-26 14:30:55",
7: "Test Location",
id: 60,
load_id: 31,
comment: "This is a test",
updated_time: "2014-03-03 00:23:09",
updated_by: "terryg",
miles: "1235",
localeta: "2014-02-26 14:30:55",
location: "Test Location"
},
{
0: 59,
1: 31,
2: "This is a test",
3: "2014-03-03 00:22:37",
4: "terryg",
5: "254",
6: "2014-02-25 04:00:37",
7: "Somewhere",
id: 59,
load_id: 31,
comment: "This is a test",
updated_time: "2014-03-03 00:22:37",
updated_by: "terryg",
miles: "254",
localeta: "2014-02-25 04:00:37",
location: "Somewhere"
}
]
},
{
id: 30,
customer: "Customer 3",
shipper: "Company 2",
consignee: "Company 3",
tracking: "L353110",
carrier: "Carrier 2",
carrierPro: "327229",
pickupTime: "02-28 23:02",
deliveryTime: "02-21 07:02",
nextUpdate: "03-02 06:03",
status: "Scheduled",
sendto: "terryg@homeoffice.com",
contact: "dispatch@carrier2.com / 555-555-1234",
update: "",
comments: [
{
0: 58,
1: 30,
2: "test comment",
3: "2014-03-03 00:21:01",
4: "terryg",
5: "653",
6: "2014-02-26 19:00:20",
7: "Test Location",
id: 58,
load_id: 30,
comment: "test comment",
updated_time: "2014-03-03 00:21:01",
updated_by: "terryg",
miles: "653",
localeta: "2014-02-26 19:00:20",
location: "Test Location"
},
{
0: 57,
1: 30,
2: "test comment",
3: "2014-03-03 00:20:37",
4: "terryg",
5: "123",
6: "2014-03-28 22:30:17",
7: "Test Location",
id: 57,
load_id: 30,
comment: "test comment",
updated_time: "2014-03-03 00:20:37",
updated_by: "terryg",
miles: "123",
localeta: "2014-03-28 22:30:17",
location: "Test Location"
}
]
}
]
}

[/code]


I am building that data with this bit of PHP :

[code]
try {
$stmt = $db->prepare("SELECT * FROM hotboard WHERE status != 2 AND status !=6 ORDER BY `next_update` ASC");
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

$stmt2 = $db->prepare("SELECT * FROM comments WHERE load_id=:id ORDER BY id DESC");
$stmt2->execute(array(':id'=>$row['id']));
$comments = $stmt2->fetchAll();

extract($row);
$load['data'][] = array(
'id' => $id,
'customer' => $f->getCustomerNameDisplay($db, $customer),
'shipper' => $f->getShipConDisplay($db, $shipper),
'consignee' => $f->getShipConDisplay($db, $consignee),
'tracking' => $tracking,
'carrier' => $f->getCarrierNameDisplay($db, $carrier),
'carrierPro' => $carrier_pro,
'pickupTime' => date_format(date_create($pickup_time), 'm-d H:m' ),
'deliveryTime' => date_format(date_create($delivery_time), 'm-d H:m' ),
'nextUpdate' => date_format(date_create($next_update), 'm-d H:m' ),
'status' => $f->getStatusDisplay($db, $status),
'sendto' => $sendto,
'contact' => $f->emailize($db, $carrier, $id),
'update' => '',
'comments' => $comments

);
}
$json = json_encode($load);
echo $json;

}

catch(PDOException $ex) {
echo "An Error occured!
\n";
//user friendly message
$ex->getMessage();
echo $ex;
}
[/code]

and my datatable init code:

[code]

$(document).ready(function() {
var table = $('#table').DataTable( {
"lengthChange": true,
"sort": false,
"info": false,
"length": 15,
"paging": true,
"pagingType": "simple",
"lengthMenu": [ [15, 30, 50, -1], [15, 30, 50, "All"] ],
"serverSide": true,
"ajax": "Scripts/loadData.php",
"columns": [
{
"class": 'details-control',
"orderable": false,
"data": null,
"defaultContent": ''
},
{ "data": "customer" },
{ "data": "shipper" },
{ "data": "consignee" },
{ "data": "tracking" },
{ "data": "carrier" },
{ "data": "pickupTime" },
{ "data": "deliveryTime" },
{ "data": "nextUpdate" },
{ "data": "status" }
],

} )


// Add event listener for opening and closing details
$('#table tbody').on('click', 'td.details-control', function () {
var tr = $(this).parents('tr');
var row = table.row( tr );

if ( row.child.isShown() ) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
}
else {
// Open this row
row.child( format(row.data()) ).show();
tr.addClass('shown');
}
} );
} );
[/code]

the output row :

[code]


'Updated Time : '+d.updated_time+
'Updated By : '+d.updated_by+
'Location : '+d.location+
'Miles Out : '+d.miles+
'Comment : '+d.comment+
''+


[/code]

Any suggestions how I can get this going? I feel I'm very close.. Just missing that one little piece.