group and populate

refaelb

Hello Guys,

I would be thankful if anyone can help!

At the end of a mysql_query i use GROUP BY "company_id" in order to get a unique row (one per company).

For every company there are several "owners"... e.g. owner name1, owner name 2. Currently in the "owner" column it displays the first owner only. (due to the group by).

I still want to get a row per company (unique) BUT i need to populate the rest of the owners.

So basically the table will show the first owner name and when a user clicks the DataTables "+" sign it should show all the owners.

I hope someone can guide me? Thanks!

allan

Hi refaelb,

One possible option is to do something like this: http://datatables.net/release-datatables/examples/api/row_details.html . Have the table render only the information that you want shown by default, and then when the user wants to display more information by clicking on the + button, you could do an Ajax call to the server to get the information that should be put into the 'extra' information element (see the fnOpen documentation for how to do that), and populate the table with that. Be aware though that this 'extra' information will not be included in sorting or filtering. Collapsable tables is not something that DataTables supports yet.

Regards,
Allan

refaelb

Hi Allan,

Thank you for your reply.

The data i populate is from several tables in a database using JOIN...
So you say that i still can use GROUP BY in the mysql query to populate a unique row (only one row per COMPANY) and then populate the list of the company owners e.g. name 1, name 2?

Please try to explain as i am a beginner... :-)

allan

You can use whatever SQL you want as long as it returns the information you want to display in the master table (i.e. yes - use GROUP BY). Then when you use fnOpen one option would be to get the extra data to be shown in XHR from another script on the server.

Allan

refaelb

Hi Allan,

Its getting clearer ... is there any example i can see how to do that? how to pass to the fnOpen the extra data?

allan

The example I linked to shows how fnOpen can have information passed to it: http://datatables.net/release-datatables/examples/api/row_details.html . You would probably just need to use $.ajax() to get the data. There a few discussions in the forum on that topic - for example: http://datatables.net/forums/comments.php?DiscussionID=3345#Item_6 .

Allan