Filtering DataTable by WHERE condition

rugggger

I want to load my DataTable according to a user-submitted parameter, whenever a button is clicked.

The table gets its data from the server-side Editor::inst

Editor::inst( $db, 'mea_report' )
    ->fields(
        .
        .
        .
    )  
    ->where('mea_report.report_id',$_POST['user_submitted_ID'])

On the client-side , the table editor is initialised thus:

editor = new jQuery.fn.dataTable.Editor( {
        ajax: "http://app.meafood.org.il/api/reportapi.php",
        table: "#report_table",
        fields: [ {fields..}, ... ]
    } );

My question is - what kind of event to I need to write for my button, that will draw the table with the user submitted WHERE condition ?
(assume I have an input field <input type="number" name="id_to_filter"> from which the id will be collected)

Thanks,
Yaron

allan

Use ajax.data as a function to get the id and submit it to the server.

However, I would suggest it would be better to use session variables for this. It would be trivial for someone to "hack" the above method and make it look like someone else as submitted data.

Allan

rugggger

Thanks Allan.

Now I load the table correctly using the _POST information that arrives from the input field.
(since it's empty the records that are loaded are all report_id=0).

When I enter '1' into the field and click the AJAX button it runs the following command

 jQuery('#AJAX').click(function(){
    jQuery('#report_table').DataTable().ajax.reload();
 });

It should load the table this time only with records where 'report_id=1', and redraw the table, but the table remains the same.

What am I doing wrong ?

http://app.meafood.org.il/report/
(enter '1' in the first input field, then click on 'AJAX')

Thanks,
Yaron

allan

You need to use ajax.data as a function as I mentioned above. You have used it as a static object:

        "data": {
          "report_id":jQuery('input[name="reportAJAX"]').val()
        }

so that jQuery expression is only ever evaluated once.

Making it a function will allow it to evaluate every time the Ajax call is made for the DataTable. There are examples in the documentation.

Allan

rugggger

I am not sure I understand how to define that function.

"type": "POST",
"data": function ( d ) {
        d.user_submitted_ID = $('#input_field').val();
    }

Would cause that info to be available as $_POST['user_submitted_ID'] ?

If you could please point me to a complete example I'd appreciate it.

Thanks,
Yaron

allan

Would cause that info to be available as $_POST['user_submitted_ID'] ?

Yes. Assuming you have an element which is id = input_field then that will submit its value.

Allan

rugggger

Thanks. I got the table to load using user submitted data in its WHERE statement.

Right now the table loads default as [report_id=1] and by changing the number and clicking on 'AJAX' button the table reloads.

Unfortunately - this brings up a problem I did not experience before -
On updating the table data, specifically on columns that use 'Select' option and get their data from a 'leftjoin' operation, I get the following error on the console

Uncaught TypeError: Cannot read property 'contents' of undefined(…)

The table then updates, but the row disappears.

What am I doing wrong here?

page: http://app.meafood.org.il/report-ajax/

Thanks,
Yaron

allan

Could you give me exact steps on how to reproduce the error please? I don't want to just hack around and corrupt your data!

Allan

rugggger

It's okay Allan, no fear. It is only a demo data.
Under the Department column just change the values using the drop down select list.
The error will immediately reproduce.

(it doesnt delete anything, just the rows disappear from view. After page refresh it seems the data was updated correctly.)

Thanks,
Yaron

rugggger

To be more precise - the console error appears with the first change, but it updates correctly. When you change another row , 'Department' drop down list , then the row will disappear.

allan

Under the Department column just change the values using the drop down select list.
The error will immediately reproduce.

I don't get that error I'm afraid. I click on a cell in the Department column, then change the select and click out side of it. The row is correctly updated. No error shown int he console.

Allan

rugggger

I tried to check it again but none of the tables (here or in other pages) are loaded now.

Is it because the trial time for Editor is over?

for example
http://app.meafood.org.il/report-ajax/
http://app.meafood.org.il/report/

Debug info : uladiy

Yaron

allan

Yes - it looks like the extended trial has also expired (it should really show an error message stating that!). I've just re-extended the trial for your account and the same procedure as before applies to update your files.

Allan

rugggger

Thank you Allan.
I have purchased the licensed version. I'll update the files and will write you later exactly how to reproduce that error I get on the tables.

Yaron

rugggger

Okay, I updated it with my licensed version and now I can reproduce the error.

http://app.meafood.org.il/report-ajax/

1. Under 'Department' change the drop down list to a different value.
2. It will update but you'll get an error message on the console.
3. Try to update the 'Department' drop down on a different row.
4. That row disappears.

Yaron

rugggger

Hi Allan.
I checked this page again I think I know what is causing the problem, but need your help in solving it.

I have on the server the following definition

->where('mea_report.report_id',$_POST['user_submitted_ID'])

which works well when I send a read request with a data object with the d.user_submitted_ID parameter.
When I use the inline function and want to update a certain cell it must receive an empty $_POST object which interferes with the update.
If I remove the ->where statement from the editorapi page then the table updates correctly.

What would be the way to solve this?

Thank you,
Yaron

rugggger

Okay, solved it ! The data JSON was not sent correctly to the editor as it was sent in the initialization.

This thread is solved.