DataTables.js Select Filters

Question

I am using DataTables.js to build a very simple table like so:

<table id="table">
    <thead>
        <tr>
            <th>Type</th>
            <th>Name</th>
            <th>Category</th>
        </tr>
    </thead>
    <tbody>

        <?php $query = 'SELECT * FROM tablename'; $total_query = "SELECT COUNT(1) FROM (${query}) AS combined_table"; $total = $wpdb->get_var( $total_query ); $results = $wpdb->get_results( $query.' ORDER BY name ASC ', OBJECT ); foreach ($results as $result) {; ?>

        <tr>
            <td><?php echo $result->type; ?></td>
            <td><?php echo $result->name; ?></td>
            <td><?php echo $result->category; ?></td>
        </tr>

        <?php };?>

    </tbody>
</table>

That works well but i need to create two independent select dropdowns on the page that filter Columns 1 (Type) and 3 (Category). Something like so ...

<select id="willfiltertype">
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
</select>

<select id="willfiltercategory">
    <option value="A">A</option>
    <option value="B">B</option>
    <option value="C">C</option>
</select>

Here is my JS

$(document).ready(function () {
    $('#table').DataTable();
});

I am really struggling with the codepens and DataTables.js sites. Any suggestions or examples would really help and be useful on here i think as there isn't much for people that are intermediates.

score 1 · Accepted Answer · answered Jan 29 '20 at 20:40

You can create these selects via PHP:

<?php 
    $query = 'SELECT * FROM tablename'; 
    $total_query = "SELECT COUNT(1) FROM (${query}) AS combined_table"; 
    $total = $wpdb->get_var( $total_query ); 
    $results = $wpdb->get_results( $query.' ORDER BY name ASC ', OBJECT ); 
?>

<select id="willfiltertype">
        <option value=" "> </option> 
    <?php foreach ($results as $result): ?>
        <option value="<?= $result->type; ?>"><?= $result->type; ?></option> 
    <?php endforeach; ?>
</select>

<select id="willfiltercategory">
        <option value=" "> </option> 
    <?php foreach ($results as $result): ?>
        <option value="<?= $result->category; ?>"><?= $result->category; ?></option> 
    <?php endforeach; ?>
</select>

<table id="table">
    <thead>
        <tr>
            <th>Type</th>
            <th>Name</th>
            <th>Category</th>
        </tr>
    </thead>
    <tbody>

        <?php foreach ($results as $result): ?>

        <tr>
            <td><?= $result->type; ?></td>
            <td><?= $result->name; ?></td>
            <td><?= $result->category; ?></td>
        </tr>

        <?php endforeach; ?>

    </tbody>
</table>

And then you can create JS where selects would filter your DataTable:

$(()=>{
    var table = $('#table').DataTable();

    $('#willfiltertype').on('change', function(){
       table.search(this.value).draw();   
    });

    $('#willfiltercategory').on('change', function(){
       table.search(this.value).draw();   
    });
});

$(()=>{
    var table = $('#table').DataTable();

    $('#willfiltertype').on('change', function(){
       table.search(this.value).draw();   
    });

    $('#willfiltercategory').on('change', function(){
       table.search(this.value).draw();   
    });
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> 
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap4.min.js"></script> 

<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css"> 
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap4.min.css"> 

<select id="willfiltertype">
    <option value=" "> </option> 
    <option value="1">1</option> 
    <option value="2">2</option> 
    <option value="3">3</option> 
</select>

<select id="willfiltercategory">
        <option value=" "> </option> 
        <option value="A">A</option> 
        <option value="B">B</option> 
        <option value="C">C</option>  
</select>

<table id="table">
    <thead>
        <tr>
            <th>Type</th>
            <th>Name</th>
            <th>Category</th>
        </tr>
    </thead>
    <tbody>

        <tr>
            <td>1</td>
            <td>rwerwer</td>
            <td>A</td>
        </tr>
        <tr>
            <td>3</td>
            <td>rwerwer</td>
            <td>B</td>
        </tr>
        <tr>
            <td>3</td>
            <td>rwerwer</td>
            <td>B</td>
        </tr>
        <tr>
            <td>2</td>
            <td>rwerwer</td>
            <td>C</td>
        </tr>

    </tbody>
</table>

That is amazing! Thank you. Is there a way to chain them so they are linked, so it only shows results that have both the selected values of each select? — onesixty, Jan 29 '20 at 20:50
@onesixty, sure, it's possible. Explain behavior of these two selects. Their values will be always in constant pair? If so, than why do you need these both selects? It could be better to create a new question and finish this one. Also, you can try to found `chained selects`. — Aksen P, Jan 29 '20 at 21:05
@onesixty, thx for a good word to me) Also, don't forget to explain behavior you wished completely. What value should be selected from the other select and by which value in this case should be searching done in DataTable. Make a new question as clear as this one — Aksen P, Jan 29 '20 at 21:09
thinking about the logic behind it you are right and its either 1. Unecessary or i can 2. Use something like https://appelsiini.net/projects/chained/. Thanks again! — onesixty, Jan 29 '20 at 21:14

DataTables.js Select Filters

1 Answers1