How to implement server side logic to determine row level colors based on data, user and the page
How to implement server side logic to determine row level colors based on data, user and the page
crcucb
Posts: 85Questions: 29Answers: 0
Looking for advice
I am using datatasbles editor with MS Sql backend. My server-side PHP is utilizing pretty much the commercial Datatables Editor library to pull data from a table, joining on a few views which provides additional data. Now I am implementing highly customizable color schemes for the rows, what I call coloredgoals. Basically, the user can decide what criteria they want for each color. There could be multiple AND criteria to determine if the row should be green. Example: if (row.field1 >=x and row.Field2 >=y) then the row background to green.
There can be multiple OR criteria to determine if the row should be red. Example: (if row.field3 = a or row.field4=b or row.field5=a) then the row background should be red.
The colorgoals will be based on the current user and the page they are viewing.
I am trying to determine the best way to implement the coloring logic. Each row will need to be evaluated considering logic that is specific for the user and the current page. I believe it's logic that should be kept on the server side to reduce the load on the client.
I am unsure if I can do this within the server PHP because it's logic that would have to be evaluated for each row and set a field to be returned to the client withing the json data with the color to use. In the past, I had issues with trying to add a custom field in the PHP server-side code. But I could add a new field to the database called background color if I have the ability to modify it before returning to the client.
First question is, Is it possible in the server-side PHP using datatables editor to have logic for each row returned that sets a field being returned to the client? At the start, I would need to retrieve the coloredgoals data (which could be several rows) for the user and page being viewed to apply the logic (it could be one call, read about 10-20 rows into an array, then the function would use logic to evaluate fields on each row to determine the color to set).
The other way I thought of is to use a stored procedure or a tvf and join it as a source in the server-side php. But I would have to pass the current id and the page being displayed, and return a dataset that includes the ID for each row and the determined color to use. Second question, wiithin the server side PHP, am I able to join a Stored Procedure or tvf and include parameters?
Answers
Field->getValue()will help a bit here, but isn't the complete solution as it doesn't give access to the rest of the row's data. ThepostGetevent, however, will give you access to all of the data from the DB. You'd need to loop over the rows, and calculate the value you need.Use
getValue()to create an empty string, then modify the value based on your logic to calculate the row colour.Not with the Editor classes. But if you aren't using the editing features, just call the stored procedure directly - you just need to return JSON data to the client-side for the DataTable to read it. You don't need to use the Editor PHP libraries.
Allan
thank you. I do edit some fields in the base table so I'll stick with the editor and look at the Field->getValue() and postGet. Are you aware of any examples that are similar to what I will need to do?
Not off the top of my head I'm afraid, but if you get stuck, let me know and I'll stick some code together
Allan