How to append a select dropdown to a HTML DataTable?

Viewed 1716

I have created a website mainly using HTML, CSS, PHP and MYSQL and I added a select dropdown with roles for to users to choose from. I need it to be on every row of users in the table. I have successfully gotten tabledit working on the site, but I am not sure how to append this dropdown to the Roles column.

This is how the HTML is set up

<body>
    <div class="panel panel-default">
        <!--        <div class="panel-heading">Sample Data</div>-->
        <div class="panel-body">
            <div class="table-responsive">
                <table id="sample_data" class="table table-bordered table-striped">
                    <thead>
                    <tr>
                        <th>ID</th>
                        <th>First Name</th>
                        <th>Last Name</th>
                        <th>Email</th>
                        <th>Approval</th>
                        <th>Roles</th>
                    </tr>
                    </thead>
                    <tbody>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

<!--SELECT DROPDOWN LIST-->
<select id="test">
    <?php
    for ($a = 1; $a <= $count ; $a++){
        ?>

        <option value="1"><?php echo($roles[($a-1)]);?></option>

        <?php
    }
    ?>
</select>
<!--//////////////-->
</body>

<script>
    $(document).ready(function(){

        var dataTable = $('#sample_data').DataTable({
            "processing" : true,
            "serverSide" : true,
            "order" : [],
            "ajax" : {
                url:"FetchUserTable.php",
                type:"POST"
            }
        });

        $('#sample_data').on('draw.dt', function(){
            $('#sample_data').Tabledit({
                url:'ActionUserTable.php',
                dataType:'json',
                columns:{
                    identifier : [0, 'user_id'],
                    editable:[
                        [1, 'first_name'],
                        [2, 'last_name'],
                        [3, 'email'],
                        [4, 'admin_approved', '{"1":"Approved","2":"Disapproved"}']
                        // [5, 'role_id']
                    ]
                },
                restoreButton:false,
                onSuccess:function(data, textStatus, jqXHR)
                {
                    if(data.action == 'delete')
                    {
                        $('#' + data.id).remove();
                        $('#sample_data').DataTable().ajax.reload();
                    }
                }
            });
        });

    });
4 Answers

You can use the render option on the column definitions to render what ever you want. In the render function you have access to the row and the entire data object.

$(function() {
  let $tbl = $('#sample_data'), $select = $('#select-box')

  let data = [
    [
      "Tiger Nixon",
      "System Architect",
      "Edinburgh",
      "5421",
      "2011/04/25",
      "$3,120"
    ],
    [
      "Garrett Winters",
      "Director",
      "Edinburgh",
      "8422",
      "2011/07/25",
      "$5,300"
    ]
  ]

  $tbl.DataTable({
    data: data,

    columns: [
      null, null, null, null, {

        render: function(data, type, row, meta) {
          return $select.html() + `Use this to select "${data}"`
        }
      },
      {
        render: function(data, type, row, meta) {
          return $select.html() + `Use this to select "${data}"`
        }
      },
    ]
  })
});
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.22/datatables.min.css" />

<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.22/datatables.min.js"></script>


<body>
  <div class="panel panel-default">
    <!--        <div class="panel-heading">Sample Data</div>-->
    <div class="panel-body">
      <div class="table-responsive">
        <table id="sample_data" class="table table-bordered table-striped">
          <thead>
            <tr>
              <th>ID</th>
              <th>First Name</th>
              <th>Last Name</th>
              <th>Email</th>
              <th>Approval</th>
              <th>Roles</th>
            </tr>
          </thead>
          <tbody>

          </tbody>
        </table>
      </div>
    </div>
  </div>

  <div style="display:none;" id="select-box">
    <select>
      <option>Role 1</option>
    </select>
  </div>
</body>

Take a look at how to add a row in datatables.net...

   var t = $('#example').DataTable();

       t.row.add( [
           counter +'.1',
           counter +'.2',
           counter +'.3',
           counter +'.4',
           counter +'.5'
       ] ).draw( false );

So, in your case, within onSuccess:function(data, textStatus, jqXHR), we should just be able to change this, $('#sample_data').DataTable().ajax.reload();, to....

var t = $('#sample_data').DataTable();
t.row.add([
    $('#test').html()
]).draw(false);
t.ajax.reload();

This is basically doing what your code was doing before, but now you're also calling the .row.add() on the DataTable().

its not a proper solution but it will resolve your issue. First select your roles html

var emelnt = $('#test');
//now append to table td
$('table tbody td:nth-child(6)').html(emelnt);
//you can also use a function to first get role id from table each row then create your select here in js and the append it to html so then your roles drop down will be selected.

I'm not sure if you are using DataTable or Tabledit, you seem to be mixing them for some reason. I'm not sure if this is a good idea to start with. I suggest picking one over the other. This answer focuses on Tabledit.

Since the table is essentially created and managed by Tabledit you should provide the data there. You currently already use a select for admin approval:

[4, 'admin_approved', '{"1":"Approved","2":"Disapproved"}']

Your role select can be created in a similar manner. Since the structure of $roles is not shared I will assume that it is an array with string values. First create a variable roles within your <script> tags.

const roles = <?php echo json_encode($roles); ?>;

Then add the column to the columns.editable setting:

[4, 'admin_approved', '{"1":"Approved","2":"Disapproved"}'],
[5, 'role', JSON.stringify(roles)]

You might have to convert the structure of $roles to create the correct JSON output. Here are some example results:

Example #1

// assumed <?php echo json_encode($roles); ?> output
const roles = ["role A", "role B"];

Creates:

<option value="0">role A</option>
<option value="1">role B</option>

Example #2

// assumed <?php echo json_encode($roles); ?> output
const roles = {"role_a": "role A", "role_b": "role B"};

Creates:

<option value="role_a">role A</option>
<option value="role_b">role B</option>
Related