Tables of child data tables populated with PHP data

Question

I am trying to find a better way to get child rows of DataTables to work with my data. There are several posts on it, but none of them mention my problem.

My situation:

Before I wanted to add expandable rows, all my data was transferred to the page via PHP, connecting directly to my MySQL database and filling out a table that is wrapped in the DataTables function $(document).ready. Downloading DataTables was easy and it worked fine. However , I don’t see the possibility for me to use PHP data to add extensible child rows, since the data should be (as far as I can tell) added after the initial generation of the table, as shown here in my 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>';
}

, . , , , , php, ( ...), PHP JavaScript ..

:

DataTables script -, php , ssp.class.php, DataTables, :

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

:

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

433 : return $stmt->fetchAll();

, script , - DataTables . , , !

Answer 1

, - . 25 SO - datatables. , , - , ! :

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

, :

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 all the 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: "
  }
});