How do I get the id of a parent into datatables?

Alasdair_McK

Hi,

I want to add a datatable with editor funtionality into a pre-existing page. That page shows the data for a project and is passed the project_id from it's calling page.

On this page that is about a single project, I want a datatable that allows me to see and manipulate data from a table where the project_id is part of a compound key (along with status_id) on the project_status table.

I know how to declare the compound key in the ajax php file
Editor:inst( $db, 'project_status', array('project_id', 'status_id'))

The question is, how do I get the project_id into the datatables code so it knows to select rows Where project_id = $project_id ?

Also on Insert, I'd need to insert a row with the correct project_id value. Similarly for delete operations.

I know how to declare the project_id variable in javascript and instantiate it, just not how to use it within datatables so that it is used in all relevant database operations.

I think it ought to be relatively straightforward, but I'm stumbling around and haven't seen any examples or forum questions that address this.

Thanks,

Al.

rf1234

Hi Al,

as soon as you open the page and your "legacy" table is being built just do an ajax call to the back end and set a session variable with the project_id. it should be overwritten each time this table changes.
Then you can use this session variable in the where clause of your Editor instance in PHP. Here is an example
a) Javascript - send proposal id to back end:

$.ajax({
    type: "POST",
    url: 'actions.php?action=SetProposalId',
    async: true,
    data: {
        proposalId: YOURVARIABLE
    },
    dataType: "json"
});

b) Set PHP session variable:

switch (filter_input(INPUT_GET, 'action')) {
  case "SetProposalId":
        $_SESSION['SelectedProposalId'] = filter_input(INPUT_POST,'proposalId');
        break;    
}

c) Editor instance whith session variable in where clause:

function table(&$db, &$lang) {
    Editor::inst( $db, 'table' )
    ->field(
        Field::inst(...)
    )
    ->leftJoin( ... )
    ->where( 'table.proposal_id', $_SESSION['SelectedProposalId'] )
    ->process($_POST)
    ->json();
}

rf1234

to set the id upon create do this:

Field::inst( 'table.proposal_id' )->set(Field::SET_CREATE)
                   ->setValue( $_SESSION['SelectedProposalId'] ),

to delete the child table row when you delete the parent just set delete cascade in your database.