datatables child tables populated with PHP data

Question

I've been trying to sort out the best way to get DataTables' child rows, to work with my data. There are several posts here on it, but none of those mention my issue specifically.

My situation:

Prior to wanting to add expandable rows, all of my data was brought into the page via PHP, connecting directly to my MySQL database and populating a table that is wrapped in the DataTables $(document).ready function. Loading DataTables was simple, and it worked fine. However, I don't see any way for me, using the PHP data, to add expandable child rows, as the data must be (as far as I can tell) added after the initial generation of the table, as shown here in their demo file:

 $('#example tbody').on('click', 'td.details-control', function () {


        var tr = $(this).closest('tr');       
        var row = table.row( tr );
        console.log(row);

        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');
        }



    });

function format ( d ) {
    // `d` is the original data object for the row
    return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
        '<tr>'+
            '<td>Full name:</td>'+
            '<td>hi</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Extension number:</td>'+
            '<td>12345</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Extra info:</td>'+
            '<td>And any further details here (images etc)...</td>'+
        '</tr>'+
    '</table>';
}

even this code WORKS, technically. When I click on a cell, it expands, showing this newly-created table underneath - but I don't know of any way to populate that with the php data that I want to, (instead of the generic data here...), as the PHP code was generated from the server prior to the JavaScript code running, etc etc.

What I've tried:

I read up here on DataTables' server-side processing, and modified the script they have on the website, but when I ran the php file for the first time, I was missing ssp.class.php, so I downloaded the latest version of DataTables, and then called it with this:

require( '../../plugins/jqueryDataTables/DataTables-1.10.7/examples/server_side/scripts/ssp.class.php' );

However, now, I get this error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 57 bytes) in C:\xampp\htdocs\plugins\jqueryDataTables\scripts\ssp.class.php on line 433

line 433 reads: return $stmt->fetchAll();

I have no idea what to do or why I can't get the script to work correctly, but after hours and hours on DataTables' website, I'm frustrated. Any help on my situation would be great, or if I'm going about this all wrong in the first place, that would be nice to hear too!

Answer 1

So I hope someone else in the future will have this same question. The code below is culmination of about 25 different posts on SO and lots and lots of reading on the datatables website. I ended up being able to do everything I originally wanted to do - it just took me forever to research how to DO it in the first place! Here's how I solved it:

First, my query on databaseSearch.php:

$searchQuery  = "SELECT * FROM alldata where $searchBy like '%$searchValue%' LIMIT 400" ;  //limiting helps with that memory overflow error in my original question
mysqli_set_charset($con, 'utf8');
$searchResult = mysqli_query($con, $searchQuery);

Then, creating an array out of the data that is brought back:

while ($row = mysqli_fetch_row($searchResult)) {

$item = array();

    $item["id"] = $row[0];
    $item["dateReceived"] = $row[1];
    $item["comments"] = $row[2];


    $output[] = $item;

    }

$out = array('aaData' => $output);
echo json_encode($out);

and then, the entire datatables code:

function format ( d ) {

    // `d` is the original data object for the row
    return '<div class="slider">'+ 
    '<table id="expand" cellpadding="5" cellspacing="0" border="0" style="margin: 0 auto;">'+
        //creating a submit button inside the dropdown that uses HTML5 datasets, i can pass ALL the information from `d` to one button, so I can pass it all off to another page.
        '<td><input class="submitButton" type="submit" value="Submit" onclick="newFromTemplate(this)" data-number="'+d.number+'" data-partNumber="'+d.partNumber+'" data-projectName="'+d.projectName+'"data-comments="'+d.comments+'" /></td>'+
        '<tr>'+  //and I can make new <tr>s and <td>s using this syntax:
            '<td class="dropHeader">cost</td>'+
            '<td class="dropInfo">'+d.cost+'</td>'+
            '<td class="dropHeader">resale</td>'+
            '<td class="dropInfo">'+d.resale+'</td>'+
        '</tr>'+
        '<tr>'+            
            '<td class="dropHeader">Date Received</td>'+
            '<td class="dropInfo">'+d.dateReceived+'</td>'+
            '<td class="dropHeader">Name</td>'+
            '<td class="dropInfo">'+d.name+'</td>'+            
        '</tr>'+
    '</table>'+
   '</div>'; 
}


$.fn.dataTable.ext.errMode = 'throw';  //shows errors in console          
    var table = $('#table').DataTable( {    
      ajax : { 
       url : 'databaseSearch.php' ,       
       dataSrc : 'aaData' ,
       type : 'POST',
       data: {searchBy:searchBy, searchValue:searchValue, options:options},
      },          
      //the "createdRow" function allows me to do additional things with the rows I output.
      "createdRow" : function (row,data,index) {      
      $('td',row).eq(0).attr('id', 'customer-' + index);  //like create an id
      $('td',row).eq(1).attr('id', 'location-' + index);
      $('td',row).eq(2).attr('id', 'zip-' + index);
      $('td',row).eq(3).attr('id', 'projectName-' + index);
      $('td',row).eq(3).attr('id', 'zDate-' + index);            
      $('td',row).eq(7).attr('id', 'ID-' + index);

      //This sections handles the icons that are placed in the first cell

      //This adds either a valid or invalid flag to the first cell
      if ( data["zDate"]) {      
        SelectedDate = new Date(data["zDate"]);
        if (SelectedDate > CurrentDate) {
          zImage = $('<img />').attr('src', '../../img/valid.png'); 
          $('td',row).eq(0).append(zImage);
          //adding this class allows me to absolutely position the debit image for each line.
          $('td',row).eq(0).addClass( 'icons' );  //or add classes...
        } else {
          zImage = $('<img />').attr('src', '../../img/invalid.png'); 
          $('td',row).eq(0).append(zImage); //or apply images...
          $('td',row).eq(0).addClass( 'icons' );
        }
      }                           
    },
      // "columns" is the top level <td>s that will be created.
      columns : [ 
        //{ className : 'details-control'},
        { data : 'customer' },
        { data : 'location' },
        { data : 'zip' },
        { data : 'projectName' },        
        { data : 'ID' },
    ],
    "columnDefs": [
    { className: "details-control", "targets": [ 0 ] }
    ],
    "lengthMenu": [ 25, 50, 101 ],
    "oLanguage": {
    "sSearch": "Filter Results: "
  }
});