In Server-Side processing unable to search and paginate for data from diff db tables
In Server-Side processing unable to search and paginate for data from diff db tables
Hello @allan
We are using Server-Side processing for our table. The data in the table is being called from 3 different mysql tables.
1. We are in a position where we cannot search the user request through general search and column filter search for the data displayed from a different table. General search & Column Filter Search works only from a single table.
Ex: Unable to search "Receivable" and "Receivable Advance" as the data is arriving from a different table.
2. The table rows displayed shows all the 13 entries irrespective of "show 10 entries.". So pagination isn't working for us.
Can you help us to know where we are wrong?_ Unable to create a test case by having different tables._
Attaching a screenshot and the script for your reference.
Answers
With server side processing enabled your server script is responsible for sorting, searching and paging. That is the place to look Are you using a Datatables supplied server side processing script?
Not sure if this thread is relevant to your issue but hope it helps.
Kevin
@kthorngren Tried all the possibilities, but no success.
That sounds like it's going to be a fiddly script to filter from three different sources, but as Kevin said it'll be that script on the server that needs to handle the filtering.
The protocol is discussed here. Also see examples here. If you download the DataTables repo, there are examples of the server-side scripts in
/examples/server_side/scripts,Cheers,
Colin
We were able to fix the Pagination issue ourselves. But when the data is arriving from 3 different MySQL tables, the column filter fails.
We were able to fix the Pagination issue ourselves. But when the data is arriving from 3 different MySQL tables, the column filter fails.! And more over the search text get automatically copied to the global search at the top.
What do you mean "the data is arriving from 3 different MySQL tables"? Presumably you are using a
joinstatement in the SQL to combine all three tables into a singleselect?Perhaps you can show me what
cashentry_serverdata.phpcontains?Allan
Here is the cashentry_serverdata.php file attached. We are not using any join statement for SQL.
It looks to me that you have a lot of work to do there if you want to use server-side processing! How many rows of data are you going to be working with. Obviously you are down at least one query per row, so you'll want to keep the number of rows down - what that the idea of using server-side processing?
Combining sorting, filtering and paging with that structure I suspect will not be trivial! If it is possible to combine it all together into a single query, I would strongly recommend that.
Allan
@allan thanks for the review. Will surly work on that and get back to you ASAP.