Using DataTables to add checkboxes and form inputs with data from Ajax

Using DataTables to add checkboxes and form inputs with data from Ajax

crash85crash85 Posts: 48Questions: 5Answers: 0

I am creating a table using DataTables. I need to be able to select multiple (~200) rows from the table to be placed into an output. I would like to implement the sort functionality described here for the checkboxes:

https://stackoverflow.com/questions/49475554/keep-enabled-checkboxes-at-top-of-jquery-datatables-despite-sort-order

http://jsfiddle.net/6e56cu8u/

But while using data that comes from Ajax.

On top of that, as the checked items move to the top, I want two form inputs to appear...OR it could be easier to just have the Class and Category columns display as form inputs, and have the default values be populated from the database. (A dropdown with all current database possibilities would be best, but with an option to type in a custom value.)

https://datatables.net/examples/api/form.html

From there, upon submit, any row that was checked would have the Name, Class, Category saved to a csv output.

The end result is that the user can select items from the table, they will move to the top, where the Class and Category fields will be dropdowns that have a default value of what is in the database, but dropdown options for all of the unique values that already exist in that column. Those dropdowns will also have a custom option which will allow the user to type in what they want the field to be. A submit button would then output a file that contained the Name, Class, and Category for any row that was checked.

HTML

    <!DOCTYPE html>
    <html>
    <head>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" media="screen" />
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.3.0/css/select.dataTables.min.css" media="screen" />
    <script charset="utf8" src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script charset="utf8" src="https://cdn.datatables.net/select/1.3.0/js/dataTables.select.min.js"></script>
    </head>
    </html>
    <body>
    <table id="samples" class="display" width="100%" cellspacing="0">
            <thead>
                <tr>
                    <th></th>
                    <th>Sample Name</th>
                    <th>Region/Program</th>
                    <th>Class</th>
                    <th>Category</th>
                    <th>QC_comment</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <td></td>
                    <td>Sample Name</td>
                    <td>Region/Program</td>
                    <td>Class</td>
                    <td>Category</td>
                    <td>QC_comment</td>
                </tr>
            </tfoot>
        </table>
        </form>
    <script type="text/javascript" charset="utf8" src="JS/datatables.js"></script>
    </body>

JQUERY (datatables.js)

    $(document).ready(function() {
    
        $('#samples').DataTable( {
                columnDefs: [ {
                orderable: false,
                className: 'select-checkbox',
                targets: 0,
                data: null,
                defaultContent: ''
            } ],
            select: {
                style:    'multi',
                selector: 'td:first-child'
            },
            order: [[ 1, 'asc' ]],
        
            "processing": true,
    
            "serverSide": true,
            
            "pageLength": -1,
            
            "lengthMenu": [[100, 250, 500, -1], [100, 250, 500, "All"]],
    
            "ajax": "datatables.php"
    
        } );
        
    
    } );

PHP (datatables.php)

    <?php
    
    $table = 'sample';
    
    $primaryKey = 'Name';
    
    $columns = array(
        array( 'db' => 'Name', 'dt' => 1 ),
        array( 'db' => 'Region', 'dt' => 2 ),
        array( 'db' => 'Class', 'dt' => 3 ),
        array( 'db' => 'Category', 'dt' => 4 ),
        array( 'db' => 'QC_flag','dt' => 6,),
        array('db'  => 'QC_comment','dt' => 5,)
    );
    
    // SQL server connection information
    $sql_details = array(
        'user' => 'user',
        'pass' => 'password',
        'db'   => 'test',
        'host' => 'xxx.xx.xxx.xx'
    );
    
    
    require( 'ssp.class.php' );
    
    echo json_encode(
        SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
    );

DATA

Name Region Class Category QC_flag QC_comment
rain L7 unknown unknown 1 Groups Poorly
cormorant LL source bird 1 Groups Poorly
cat LL source pet 0
12-01-08 LL background  marine 0
12-08-08 LL background  marine 0

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    But while using data that comes from Ajax.

    The fiddle linked to would work with ajax loaded data. But that code would conflict with using the select extension you are using. We can probably come up with a cool mechanism to sort selected rows to the top. Will need to think about it for awhile.

    On top of that, as the checked items move to the top, I want two form inputs to appear.

    This could be done using rowCallback and changing the columns you want to have inputs.

    A submit button would then output a file that contained the Name, Class, and Category for any row that was checked.

    There are many options here depending on whether the submit button is on each row or if you want a submit button using the buttons extension. With the buttons extension you can control the rows output like these examples:
    https://datatables.net/extensions/buttons/examples/initialisation/select.html
    https://datatables.net/extensions/buttons/examples/print/select.html

    Your code shows that you have server side processing enabled but with a page length of -1. This will display all the rows on the page but since SSP is enabled all table draw, including sorting, will be sent to the server via SSP. This will negate the moving of selected rows to the top. Do you need server side processing?

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    I'll have to attempt to use the linked fiddle again and try to get it to work. I tried to set my html to an empty table and load the Ajax data but it would either not display anything, or get stuck saying "Processing." That fiddle moves selected items to the top, but your saying that wouldn't work with the Ajax data?

    And the submit button would be a single button that output all the selected rows.
    I'll give those links a look and see what I can figure out.

    As for the SSP... I'm not sure. I set the page length to -1 because my users will want to scroll through all of the records without paging. My code is a mish mash of different examples, so it's likely I am using something wrong, or not understanding what I am actually doing.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    That fiddle moves selected items to the top, but your saying that wouldn't work with the Ajax data?

    No, I am saying that since you are using the select extension the code in the fiddle will conflict. Also since you are using server side processing the sorting responsibility is with your server script not the client, which the fiddle works with client side processing.

    If you don't need server side processing then you should remove it from the config. It helps with loading large data sets but requires the server script to handle searching, sorting, etc. If you don't use server side processing then the sorting, searching, etc is handles by Datatables.

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Ah, ok. I guess I am uneducated on how the server side processing worked. I had copied that php script from one of the demos I believe for reading in a table from sql. I assumed SSP was required to do that. (I may be mixing up the SSP::simple in the php with the server side : true in the js.). If it is as simple as setting serverSide to false, will that have any implications to user interactions? The table will display anywhere from 400 rows, to 2000 rows as it grows.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @crash85 ,

    This section of here of the FAQ will be helpful - it show's the boundaries where serverSide should be considered.

    Cheers,

    Colin

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    If it is as simple as setting serverSide to false, will that have any implications to user interactions?

    With pageLength: -1 you are loading all of the data negating the gains used by server side processing. Every time you sort, search, etc a request is sent to the server to update the table. Tuning off server side will eliminate these requests and the data is sent to the client once at initial load. If anything it will improve while you have pageLength: -1.

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Ah, ok. I have set the serveSide to false. And that keeps the checked rows selected when I sort. That's great! Now I'm trying to implement the select behavior from the fiddle, but it has coded check boxes for each row in the html. I'm not sure how to make that happen with data coming in from ajax. Or is it as simple as removing the select function in my jammies and leaving the column defs part? (Currently on my phone at lunch, so I'll try that when I get back.). As for the csv export, that looks straightforward enough!

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    I'm attempting this, but the checkbox isn't reacting:

    $(document).ready(function() {
    
    var table = $('#samples').DataTable({
                columnDefs: [ {
                orderable: true,
                className: 'select-checkbox',
                targets: 0,
                data: null,
                defaultContent: ''
            } ],
            order: [[ 1, 'asc' ]],
            "processing": true,
            "serverSide": false,
            "pageLength": -1,
            "lengthMenu": [[100, 250, 500, -1], [100, 250, 500, "All"]],
            "ajax": "datatables.php"
        } );
        
    $('#samples').on('click', 'input[type="select-checkbox"]', function() {
       var row =  table.row($(this).closest('tr'));
       table.cell({ row: row.index(), column: 0 } ).data( this.checked ? 1 : 0 )
       row.invalidate().draw()
    })
        
    
    } );
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @crash85 ,

    We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

    Cheers,

    Colin

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    One thing you are missing in your code snippet is orderFixed: [0, 'desc']. This is key to making this technique work. This needs to be a separate column from the checkbox column otherwise the 1 or 0 will display with the checkbox. Another issue is your are using className: 'select-checkbox', which displays a checkbox but I don't think its a normal HTML checkbox input so clicking it doesn't check it.

    I created an example for you using the Select extension. It uses the select and deselect events instead of the click event in the fiddle. I removed the invalidate() method because we are using Datatables API's to update the data. Its probably not needed with the fiddle either. Replaced it with simply table.draw(). This will cause Datatables to sort the table after the update.

    Here is the example:
    http://live.datatables.net/kaburove/1/edit

    Note also I used columns.data to define where the data from the source are to be placed. This allows skipping the first two columns.

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Thank you! That helps so much.

    I have modified what you provided slightly here:

    http://live.datatables.net/kaburove/2/edit?html,css,js,output

    It doesn't display well in the editor because all of the css that is outsourced, and the buttons not showing up.

    The first button works as expected. It's the second and third buttons I am trying to figure out now.

    The second buttons behavior would be similar to the Download SELECTED button, except instead of prompting a save, it would pass the list/file to a php script, or save it to a defined location.

    The third button would allow someone to upload their own excel file that would circumvent the whole selection process. That uploaded file would get passed to a php script or saved to a defined location.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited March 2019

    It doesn't display well in the editor because all of the css that is outsourced, and the buttons not showing up.

    At the end of the HTML you added another instance of datatables.js. I commented this and now the buttons show. If you want the Excel button to appear you need to add JSZip.js. Please refer to the Download Builder.

    Maybe someone else can help with your button behavior. I'm not familiar with what you are trying to do nor what the Download SELECTED button is doing.

    http://live.datatables.net/kaburove/3/edit

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0
    edited March 2019

    Ah, thank you again. Oddly enough the buttons worked for me without the JSZip.js, but the downloaded file wouldn't pop up in the browser. After adding JSZip, it does.

    I've updated the code with my current working version. I've added a description to each button that might explain better what I'm trying to accomplish.

    http://live.datatables.net/kaburove/4/edit

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited March 2019

    I don't believe there is anything built into Datatables to automatically perform the functions you want. I'm not sure how to upload files to PHP. I would look at sending the desired data via an ajax request to your PHP scripts.

    For this I would use columns().data() along with toArray() to get an array of data. Then use JSON.stringify() to make the data a JSON and use the ajax data parameter to send the data to the server.
    http://api.jquery.com/jquery.ajax/

    Others may have better ideas for you.

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    That's what I've been finding as well. Thats the route I'll take when I get back to it tomorrow.

    I attempted to use the rowCallback function today, and also tried to make fields with dropdowns...in the hopes of figuring out how to combine them. But I couldn't get either to work.

    I really only need the Category class to become a drop-down once selected.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Not sure what you want to do with the drop downs but this shows how to keep them ordered:
    https://datatables.net/examples/plug-ins/dom_sort.html

    This example shows updating a drop down updates another filed in the same row:
    http://live.datatables.net/yogayixa/1/edit

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    I'm trying to implement this, but something isn't quite right. The dropdown box shows up, but it isn't populated.

    http://live.datatables.net/kaburove/11/edit

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited March 2019

    Your code to build the select is resulting in an empty select. I put a console.log statement to show the string being built which is <select></select>.

    Another problem is you should use the Select Extension API's (row().select() and row().deselect()) to toggle the select state instead of toggling the selected class. By toggling the class the deselect event is never called.

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Sorry I haven't been able to get back to this. I am currently out helping with flood victims.

    Is changing the row select api as simple as swapping out the function name, or will it require completely rewriting how it works? (Forgive me, I truly have no idea what I am doing!)

This discussion has been closed.