Master Detail 2 tables on the same page? (Php/Ajax/Datatables)

SalmanS

Hello Everyone,

I got two tables on my home page.

Master Table - column names - id, location
Slave/Detail Table - column - sid, mid,update_details,

I would like to display two table

    <table id=master onclick(rowid)>

    <thead>
    <th>id</th>
    <th>location</th>
    </thead>
    </table>

    <table id=details>
    <thead>
    <th>sid</th>
    <th>mid</th>
    <th>update</th>
    <th>details</th>
    </thead>
    </table>

    function onclick(rowid)
    {
    display details table record in table id=details.
    }

Can this be achieved using datatables and ajax? if so can someone direct me to some fiddle please.

Apologize for such stupid question.

Thanking you in advance

kthorngren

Are you looking for the Slave/Detail table to display rows based on what's selected in the Master table? If so this blog should help:
https://datatables.net/blog/2016-03-25

If you are not using Editor then you can ignore those parts of the config.

Kevin

SalmanS

Sorry I am not using editor, iand its confusing in the above example....is there a way this can be achieved using datatables not editor. Please???

kthorngren

Yes, you can use this without the editor. For the Parent and Child tables you would implement the DataTables Javascript sections for each. You will also need the Selecting rows code under the Wiring it together section.

The key part is this in the child table:

    ajax: {
        url: '../php/users.php',
        type: 'post',
        data: function ( d ) {
            var selected = siteTable.row( { selected: true } );
 
            if ( selected.any() ) {
                d.site = selected.data().id;
            }
        }
    },

Basically your Parent table will need an ID that can be used in the Child table to send an ajax request with just the ID to the server script. You will need a server script that can accept a parameter with the ID and return the data corresponding to that ID.

Please post any specific questions you have about this process.

Kevin

SalmanS

Many thanks Kevin,and please accept my apologies for my query, I got click even captured. Do you see any jsfiddle or live.datatable ?! On this forum?

SalmanS

Here are my tables with different IDs.

    <table id=master onclick(rowid)>

    <thead>
    <th>id</th>
    <th>location</th>
    </thead>
    </table>

    <table id=details>
    <thead>
    <th>sid</th>
    <th>mid</th>
    <th>update</th>
    <th>details</th>
    </thead>
    </table>

function onclick(rowid)
{
display details table record in table id=details.
}

SalmanS

I am little bit confused on this, do you have any jsfilddle or datatable please?

    ajax: {
        url: '../php/users.php',
        type: 'post',
        data: function ( d ) {
            var selected = siteTable.row( { selected: true } );

            if ( selected.any() ) {
                d.site = selected.data().id;
            }
        }
    },

kthorngren

do you have any jsfilddle or datatable please

I don't at the moment. I suppose one could be created but the blog page has a working example. It includes Editor functionality but all you would be interested in is what happens when you select and deselect rows.

Not sure exactly what you are confused by but the ajax data function will pass parameters in the request to the server. The Select Extension is used to select the rows. The child table will get the data for the selected row in the parent then pass the id to the server script in the parameter site. How you pass the parameter will be dictated by how you structure the server script.

The child table is updated by this code:

siteTable.on( 'select', function () {
    usersTable.ajax.reload();
} );
 
siteTable.on( 'deselect', function () {
    usersTable.ajax.reload();
} );

The child table is reloaded each time a row is selected or deselected in the parent. The child table will send an ajax request with the site id if the row is selected or a request with no parameters if there are no selected rows. The server script will return the data associated with the id if that parameter is sent or it will send a blank ([])response if the parameter is blank.

Kevin

SalmanS

Sorry for me being a daft.
Still struggling- - here is my code..

    <table id=masterTable>

    <thead>
    <th>id</th>
    <th>location</th>
    </thead>
    </table>

    <table id=detailsTable>
    <thead>
    <th>sid</th>
    <th>mid</th>
    <th>update</th>
    <th>details</th>
    </thead>
    </table>


    master.Table.on( 'select', function () {
        detailsTable.ajax.reload();
    } );

    details.on( 'deselect', function () {
        detailsTable.ajax.reload();
    } );

kthorngren

Since you are selecting and deselecting rows in the parent both events need to reference the parent table, for example:

master.Table.on( 'select', function () {
    detailsTable.ajax.reload();
} );
 
master.on( 'deselect', function () {
    detailsTable.ajax.reload();
} );

If you still have issues please post your Datatables init code for both tables.

Kevin

SalmanS

Hi Kevin,

Here is my jsfiddle which got two tables on the same page but i have no luck so far.

Master table got three records (list of Manager) and with unique id column (1,2,3)

Detail table got 9 employees who are working under the list of managers, the detail table got MID column which is manager's id from above.

On click of master table row it should list the corresponding rows of employees in details table underneath

http://jsfiddle.net/salmansohail/g3v4pj02/15/

kthorngren

The code you posted doesn't implement any of the code to select a row in the master and display the corresponding data in the details table. The other problem is loading the detail table into the DOM won't work for the solution provided in the blog. The blog solution needs an ajax data source. Is your data loaded using the ajax option in Datatables?

If you data is sourced directly from the DOM then you probably can make it work using the search() or better column().search() API to search the detail table in the MID column for the select ID. With this solution you could start with a default search that doesn't match and displays 0 rows using searchCols.

Kevin

SalmanS

I know it was not possible, i am reading a file from url using ajax and i have some success so far.

here is my updated jsfiddle

http://jsfiddle.net/salmansohail/8wc4tq60/1/

kthorngren

Maybe I'm misunderstanding what you want to do but with the blog example the detail table is empty until a row in the master table is selected. Then the detail table is populated with the matching records. But your examples are populating the detail table at the start.

Maybe we need to take a step back to get a better understanding of what you have, how to access the data and what you want.

1. For the detail table is this from an ajax data source or are you needing to populate all the records in the DOM at the start?

2. Are you wanting the detail table to start by showing no data then when a row in the master table is selected show the matching records in the detail table?

3. When the selected row in the master table is unselected then display no records in the detail row?

Kevin

SalmanS

Please ignore previous link ..Sorry

Correct one is here:
https://jsfiddle.net/zaibtabs/8wc4tq60/12/

SalmanS

Hi Kevin,

Many thanks for getting back to me.

What I am trying to achieve is the detail table to start by showing no data then when a row in the master table is selected show the matching records in the detail table

and

When the selected row in the master table is unselected then display no records in the detail row.

Yes this is what I am trying to achieve.

here is the jsfiddle, which i am working since last night with my little knowledge and so for very little success,

https://jsfiddle.net/zaibtabs/8wc4tq60/

kthorngren

You didn't answer the first question. What is the data source for the detail table (database, the text file)? Does it need to be in the DOM at the start?

In your server script do you have a way to filter the search of the data source to retrieve only the records you want, ie, MID == ID?

Kevin

SalmanS

What is the data source for the detail table? Text file
Does it need to be in the DOM at the start? No

In your server script do you have a way to filter the search of the data source to retrieve only the records you want, ie, MID == ID?No but I am trying..

kthorngren

One option could be to load the detail table with all the records but not display any by performing a search that removes them. Then when the master row is selected perform another search to display the records where MID == ID. If you have many thousands of records for the detail table this may or may not work well.

This would save you from have to parse a text file to filter the records. Would you like to try this approach?

Kevin

SalmanS

I have to comeup with some jquery function to parse a text file to filter the records and display only matching records in detail table.

I thought to use text file for testing purpose instead of mysql database but its getting bit complicated.

Its getting off the topic, towards jquery file parse a text file first.

anyway this is so far i reached.

https://jsfiddle.net/zaibtabs/9dxncL01/2/

Thanks for your help. I will try and let you know.
Once again many thanks.