Datatables: row details dynamic data

dragongirl411

Hi there. I'm getting my data from a database via php and mysql. I'm loading the data into Datatables and all is working well. The problem is when I try to add row details with dynamic data also from the database.

Short description of my data: customers contact info is in the outer table. Each customer will have at least one, and up to ten, products associated with them. I'd like to list the products in the row details. But I can't figure out how to add this data in a way that it is ready for json encoding.

I'm pretty new to json/jquery/ajax/datatables and I feel like I've tried so many things and looked in so many places for answers that my head is spinning.

Here is my html/js:

<?php
$page_title = '';
require '../private/includes/initialize.inc.php';
include '../private/includes/form_header.inc.php';

<?php
>

function format(d){
    var output = ''+
    'Office: '+d.office+''+
    'Address: '+d.full_address+''+
    'Email: '+d.email+''+
    'Phone: '+d.phone+''+
    'Dealer: '+d.dealer_info+'';
?>


    // create products inner table as a row
    output += '<tr><td colspan="5"><table width="100%"><tr><thead><th>Product Name</th><th>Model</th><th>Serial</th><th>Turbine</th><th>Invoice</th><th>Invoice Link</th><th>Purchased</th></thead></tr>';
    
    for(i=0; i<d.products.length; i++){
        output += '<tr><td>' + d.products[i].name + '</td>'+
        '<td>' + d.products[i].model_number + '</td>'+
        '<td>' + d.products[i].serial_number + '</td>'+
        '<td>' + d.products[i].turbine + '</td>'+
        '<td>' + d.products[i].invoice_number + '</td>'+
        '<td>' + d.products[i].invoice_link + '</td>'+
        '<td>' + d.products[i].purchased + '</td></tr>';
    }

    output += '</table></td></tr>';
    output += '</table>';
    
    return output;
}

$(document).ready(function(){
    var table = $('#table_id').DataTable({
        "processing": true,
        "serverSide": true,
        "sAjaxSource": "../private/includes/process_tables.php",
        //"sAjaxSource": "data.txt",
        "columns": [
                                {
                                    "className": 'details-control',
                                    "orderable": false,
                                    "data": null,
                                    "defaultContent": ''
                                },
                                {"data": "id"},
                                {"data": "summary_link"},
                                {"data": "fullname"},
                                {"data": "created"}
        ]
    });
    
    /* Add event listener for opening and closing details */
    $('#table_id tbody').on('click', 'td.details-control', function(){
        var tr = $(this).closest('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');
        }
    });
});
</script>
    <section>
        <div class="rule"></div>
    <table class="display responsive" id="table_id" cellspacing="0">
        <thead>
        <tr>
            <th></th>
                <th>Registration ID</th>
          <th>Summary Link</th>
          <th>Contact</th>
          <th>Created</th>
        </tr>
       </thead>
    </table>
    </section>
</main>
<?php include '../private/includes/footer.inc.php'; ?>

and here is my php:

<?php
require 'initialize.inc.php';
//$iNumberProducts = 1;
$requests = new Registration();
$products = new Product();
$output = array("aaData" => array());

$results = $requests->getAllActiveRegistrations();
if($results) {
    foreach($results as $result) {
        $output['aaData'][] = $result;
        $prodResults = $products->getProductsForRegistrationID($result->id);
        if($prodResults){
            foreach($prodResults as $prodResult){
                $output['aaData']['products'][''] = $prodResult; 
            }
        }
    } // end foreach($results)
    
}

echo json_encode($output);

What I'm trying to accomplish in the php is get all active registrations, which includes the customer data. Then use the registration id to find all products associated with each customer.

If I do a test using json in a text document using the following, everything works fine.

{
"aaData":[
    {
        "id":"181",
        "summary_link":"181_summary.html",
        "fullname":"Cherie Skaggs",
        "created":"07\/29\/2015 10:26 AM",
        "office":"Smiles R Us",
        "full_address":"123 Main St.  Chicago, IL 55555",
        "email":"cherie@test.com",
        "phone":"555-555-5555",
        "dealer_info":"Patterson in Louisville, KY",
        "products":[
            {
                "name":"Midwest Stylus",
                "model_number":"Pro1000",
                "serial_number":"9999999",
                "turbine":"no",
                "invoice_number":"3013506M",
                "invoice_link":"181_3013506M_invoice.jpg",
                "purchased":"07\/29\/2015"
            }
        ]
    },
    {
        "id":"182",
        "summary_link":"182_summary.html",
        "fullname":"Kris Mahoney",
        "created":"07\/29\/2015 10:30 AM",
        "office":"",
        "full_address":"123 Main St.  Chicago, IL 55555",
        "email":"kim@taggrafx.com",
        "phone":"999-999-9999",
        "dealer_info":"Henry Schein in Wallingford, CT",
        "products":[
            {
                "name":"Stylus Forever",
                "model_number":"p2",
                "serial_number":"54gg454",
                "turbine":"no",
                "invoice_number":"t4t4454",
                "invoice_link":"181_t4t4454_invoice.jpg",
                "purchased":"05\/13\/2015"
            },
            {
                "name":"Super Stylus",
                "model_number":"Max999",
                "serial_number":"652626",
                "turbine":"yes",
                "invoice_number":"i8989899",
                "invoice_link":"181_i8989899_invoice.jpg",
                "purchased":"06\/21\/2015"
            }
        ]
    }
]
}

Any help is appreciated.

dragongirl411

Well after much trial and error and rolling my face across the keyboard I've come up with a solution. My html/js file remains the same. Here is my new php file:

<?php
require 'initialize.inc.php';

$requests = new Registration();
$products = new Product();
$output = array("aaData" => array());
$mainRows = array();

// get all active registrations from db
$results = $requests->getAllActiveRegistrations();

// assemble array for outer table
foreach($results as $key => $value){
    $mainRows['id'] = $value->id;
    $mainRows['summary_link'] = $value->summary_link;
    $mainRows['fullname'] = $value->fullname;
    $mainRows['created'] = $value->created;
    $mainRows['office'] = $value->office;
    $mainRows['full_address'] = $value->full_address;
    $mainRows['email'] = $value->email;
    $mainRows['phone'] = $value->phone;
    $mainRows['dealer_info'] = $value->dealer_info;
    
        // get all products for this specific registration id
    $prodResults = $products->getProductsForRegistrationID($value->id);

        // create the array for inner table
    $mainRows['products'] = array();
    $i=0;
        // assemble the inner table array
    foreach($prodResults as $prodKey => $prodValue){
        $mainRows['products'][$i]['name'] = $prodValue->name;
        $mainRows['products'][$i]['model_number'] = $prodValue->model_number;
        $mainRows['products'][$i]['serial_number'] = $prodValue->serial_number;
        $mainRows['products'][$i]['turbine'] = $prodValue->turbine;
        $mainRows['products'][$i]['invoice_number'] = $prodValue->invoice_number;
        $mainRows['products'][$i]['invoice_link'] = $prodValue->invoice_link;
        $mainRows['products'][$i]['purchased'] = $prodValue->purchased;
        $i++;
    }
    $output['aaData'][] = $mainRows;
}
echo json_encode($output);

I would accept any criticism if it would improve the logic or efficiency of the code.