Answers

• colin Posts: 15,240Questions: 1Answers: 2,599

  November 2020

  We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

  Cheers,

  Colin
• jelder227 Posts: 5Questions: 1Answers: 0

  November 2020

  Ok - I think I did this as best I can. Here is a test case. Obviously, in this case I am drawing from the same file for both. In reality, I have two different tables and two different php pages per the code above. However, this does replicate the issue in that everyone shows up in all offices.

  live.datatables.net/muyuguji/1/edit
• kthorngren Posts: 21,341Questions: 26Answers: 4,954

  November 2020

  I only want the adjlinesTable to contain the records where MAINtable(id) = adjlinesTable(adjustmentid)

  Looks like you are doing something similar as this blog but maybe without the Editor. Whether using the Editor with this or not doesn't matter. Take a look at this section of the child Datatable configuration for how to use the ajax.data option to send the id to the server.

  Your server script will need to get the id parameter to limit the results to the desired data.

  Kevin
• jelder227 Posts: 5Questions: 1Answers: 0

  November 2020

  OK, for the subtable, I need to make sure the only records pulled have the adjustmentid = rowData.id (assuming this is the id from the main row). So I revised my main script to the following:

  $(document).ready(function() {
    
  function createChild ( row ) {
      // This is the table we'll convert into a DataTable
      var table = $('<table class="display" width="100%"/>');
   
      // Display it the child row
      row.child( table ).show();
   
      // Initialise as a DataTable
      var adjlinesTable = table.DataTable( {
      ajax: {
          url: 'php_pages/fetch_adjlines.php',
          type: 'post',
          data: function ( d ) {
              d.adjustmentid = rowData.id;
          }
      },
      pageLength: 5,
      columns: [
        { data: "id" },
        { data: "adjustmentid" },
        { data: "product" },
        { data: "quantity" }
      ],
      order: [[1, 'asc']],
      
      } );
  }
    
    // Main table
    var table = $('#MAINtable').DataTable( {
      ajax: "php_pages/fetch_adjustments.php",
      pageLength: 5,
      columns: [
        {
           className: 'details-control',
           orderable: false,
           data: null,
           defaultContent: ''
        },
        { data: "id" },
        { data: "date" },
        { data: "reason" }
      ],
      order: [[1, 'asc']],
    } );
    
    
    // Add event listener for opening and closing first level childdetails
    $('#MAINtable tbody').on('click', 'td.details-control', function () {
       var tr = $(this).closest('tr');
       var row = table.row( tr );
       var rowData = row.data();
   
       if ( row.child.isShown() ) {
         // This row is already open - close it
         row.child.hide();
         tr.removeClass('shown');
          
         // Destroy the Child Datatable
         $('#' + rowData.id.replace(' ', '-')).DataTable().destroy();
       }
       else {
         // Open this row
          createChild(row);
         tr.addClass('shown');
        }
    } );
               
  
            
  } );
  

  This totally broke it ... presumably I need to do something in fetch_adjustments.php?

  This is my code there:

  // DB table to use
  $table = 'adj_lines';
  
  // Table's primary key
  $primaryKey = 'id';
  
  // Array of database columns which should be read and sent back to DataTables.
  // The `db` parameter represents the column name in the database, while the `dt`
  // parameter represents the DataTables column identifier - in this case object
  // parameter names
  $columns = array(
      array(
          'db' => 'id',
          'dt' => 'DT_RowId',
          'formatter' => function( $d, $row ) {
              // Technically a DOM id cannot start with an integer, so we prefix
              // a string. This can also be useful if you have multiple tables
              // to ensure that the id is unique with a different prefix
              return 'row_'.$d;
          }
      ),
      array( 'db' => 'id', 'dt' => 'id' ),    
      array( 'db' => 'adjustmentid', 'dt' => 'adjustmentid' ),        
      array( 'db' => 'product', 'dt' => 'product' ),  
      array( 'db' => 'quantity',  'dt' => 'quantity' )
  
  );
  
  /*has server connection with value $sql_details */
   include('../include/basic_conn.php');
  
  
  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
   * If you just want to use the basic configuration for DataTables with PHP
   * server-side, there is no need to edit below this line.
   */
  
  require( '../include/ssp.class.php' );
  
  echo json_encode(
      SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
  );
  
• kthorngren Posts: 21,341Questions: 26Answers: 4,954

  November 2020

  This totally broke it ... presumably I need to do something in fetch_adjustments.php?

  What happens?

  Yes, you will need to do something in your fetch_adjustments.php script to get the parameters sent and use it for the query. I don't use PHP so won't be much help there.

  Kevin
• kthorngren Posts: 21,341Questions: 26Answers: 4,954

  November 2020

  Maybe you are getting an error with this d.adjustmentid = rowData.id;. I don't think you have a variable rowData in your createChild function. Yu probably just want to use row for the row parameter in the function.

  Kevin
• jelder227 Posts: 5Questions: 1Answers: 0

  November 2020

  Slowly, slowly, step by step... You are correct.

  d.adjustmentid = rowData.id I get an error, rowData is not defined.

  I replaced it with d.adjustmentid = row.id
  What I am getting over to my php file now is row_1 - which is the DT_RowId, not my id from the column. Any clue on how to send the column value?
• kthorngren Posts: 21,341Questions: 26Answers: 4,954

  November 2020

  Use console.log( row ); inside the createChild() function to see the structure of row. The id property should be { data: "id" }, from your main table. Then use the browser's network inspector tool to verify the parameters sent by looking at the bottom of the header tab (at least when using Chrome).

  Do both show the correct id?

  Then in your fetch_adjlines.php script you will need some code to get the parameters. Have you done that? Sorry I'm not much help with PHP.

  Kevin
• jelder227 Posts: 5Questions: 1Answers: 0

  November 2020

  Kevin, you are wonderful! While you didn't answer it, you definitively pointed me in the right direction to make it work. I had to throw out the base php file from Datatables and do a bunch of rewriting to make my code generate the right formats to be imported correctly, but doing this I was able to define the DT_RowId as my table id. The tables still work, as this is a unique field. And this field then gets passed to my child table and draws the correct records.

  After over a week beating my head against a wall on this, it works!!! I just need to pretty it up, and then move on to my next set of problems ...