Obtain specific column value of current row

patrickmau

Hi,

I am pulling data from my MySQL database via AJAX and then use DataTables to generate dynamic buttons in an additional column for each row. This column provides Edit / Delete buttons for each row, which in turn bring up a modal where the action is being taken care of.

The problem now is that I need the buttons to have a data-id attribute where id refers to the ID column of the row. I have tried many ways to get this done, but so far I am only able to extract the full row data as a comma separated string, which is not really how I want to do this.

    <script type="text/javascript" language="javascript" class="init">                  
        $(document).ready(function() {
            $('#auto_cc_table').DataTable( {
                // load table data via AJAX
                "processing": true,
                "serverSide": true,
                "ajax":{
                    url: "../../plugins/MySQL/ajax_action.php", // json datasource
                    data: { action:"view_auto_cc_email_AJAX" },
                    type: "post",  // connection method (default: GET)
                },
                "columns": [
                    { "aaData": "ID" },
                    { "aaData": "Holidex" },
                    { "aaData": "First" },
                    { "aaData": "Last" },
                    { "aaData": "Email" },
                    { "aaData": null },
                ],
                columnDefs: [{
                    className: "text-right", "targets": [1],
                    className: "text-nowrap", "targets": [3],
           
                    // puts a button in the last column
                    targets: [-1], render: function (data, type, row, meta) {

                        // look at your console and make sure there is a value here.
                        //console.log(data);        // error - undefinded in console
                        console.log(row);           // ok
                        console.log(row.ID);        // error - undefined in console
                        //console.log(row.Holidex); // error - undefined in console
                        //console.log(row.First);       // error - undefined in console
                        //console.log(row.Last);        // error - undefined in console
                        //console.log(row.Email);       // error - undefined in console

                        return '<button type="button" class="btn btn-primary btn-xs" data-toggle="modal" data-target="#EditEmailModal" data-keyboard="true" data-id="' + row.ID +'"><i class="fa fa-edit"></i></button>' 
                           +'<button type="button" class="btn btn-danger btn-xs" data-toggle="modal" data-target="#EditEmailModal" data-keyboard="true" data-id="' + row.ID +'"><i class="fa fa-times"></i></button>'
                    }
                }],
                dom: 'Bfrtip',
                stateSave: true,
                buttons: [
                  'copyHtml5',
                  'excelHtml5',
                  'csvHtml5',
                  'pdfHtml5',
                  {
                    extend: 'print',
                    message: '(c) http://www.website.com/'
                  },
                  {
                  extend: 'collection',
                  text: 'Others',
                  buttons: [
                    {
                      text: 'Toggle button',
                      action: function ( e, dt, node, config ) {
                      dt.column( -6 ).visible( ! dt.column( -6 ).visible() );
                      }
                    },
                    'colvis',
                    'columnsToggle',
                  ]
                  },
                ],
                "pagingType": "full_numbers",
                "pageLength": 25,
                "lengthChange": true,
                "searching": true,
                "ordering": false,
                "order": [[ 1, "asc" ], [ 2, "asc" ]],
                "info": true,
                "autoWidth": true
            })
        });
    </script>

I am logging the output for data, row, row.ID, etc into the console, where all I get is an error message saying 'undefined'.

I think my question is this: How can I extract the ID value of the specific row and attach it to the button as a data-id="xx" element?

And on a sidenote, I am unable to get the column text aligned to the right / left / nowrap, even though the columnDefs seem ok.

Any help would be appreciated.
Thank you.

kthorngren

Looks like your code should work. Does your table populate properly if you remove the last column?

console.log(row); // ok

What is the output from this?

//console.log(data); // error - undefinded in console

You are using aaData, which is a legacy command, instead of data. Is there a reason? Not sure why this command outputs undefined with aaData but should work if you use data.

I am unable to get the column text aligned

This is typically due to not having width="100%" configured in the html of your table definition.

Kevin

patrickmau

Thank you for your response, appreciate you taking the time to look at this.

console.log(row); outputs the following, which is the full row content of the current row:
["7", "ADMIN", "Jane", "Doe", "jane.doe@gmail.com"]

as for aaData, no I have no particular reason, I just reused portion of some code that I had put together previously and never had an issue with. I am changing to `data to see how it goes.

I will have a look at the width="100%" as you mentioned. Is this applied to the table element or the the td tr?

Thank you

patrickmau

Just changed from aaData to data and the whole script stopped working. Not sure why, but it seems like DataTables requires me to define the query data as aaData during the SQL interaction before sending it back to my page for display. Below my MySQL interaction:

            // run final query
            $result = $db->query($sql) or die(mysqli_error($db));       
            
            if($result->num_rows > 0) {
                // return total number of rows for pagination
                $totalFiltered = $result->num_rows;
            
                // return table data - MUST BE NON-ASSOCIATIVE ARRAY
                while($row = mysqli_fetch_array($result)) {
                    $data[] = array(
                        $row['ID'],
                        $row['Holidex'],
                        $row['First'],
                        $row['Last'],
                        $row['Email']
                    );
                }
                
                // finalize array with elements required by DataTable plugin
                $json_data = array(
                  "draw"            => intval( $requestData['draw'] ),  // unique draw number identifier (required)
                  "recordsTotal"    => intval( $totalData ),            // total number of records
                  "recordsFiltered" => intval( $totalFiltered ),        // total number of records after searching, if there is no searching then totalFiltered = totalData
                  "success"         => true,                            // success message - false / true (required)
                  "aaData"              => $data                            // table data as array
                );
                
                echo json_encode($json_data);
            }

patrickmau

Alright, found the problem.
Turns out that the row variable was an array but needed to be defined as an object in order to address the ID column directly. Code below is updated and working.

                                columnDefs: [{
                                    className: "text-right", "targets": [1],
                                    className: "text-nowrap", "targets": [3],
                                    
                                    // place button in the last column
                                    targets: [-1], render: function (data, type, row, meta) {
                                        
                                        var ID = row[0];    // define ID as a variable since array is an object
                                        console.log(row);   // check console for data row output
                                        
                                        return '<button type="button" class="btn btn-primary btn-xs" data-toggle="modal" data-target="#EditEmailModal" data-keyboard="true" data-id="' + ID +'"><i class="fa fa-edit"></i></button>' 
                                           + ' <button type="button" class="btn btn-danger btn-xs" data-toggle="modal" data-target="#DeleteEmailModal" data-keyboard="true" data-id="' + ID +'"><i class="fa fa-times"></i></button>'
                                    }
                                    
                                }],