Datatables data filtering with multiple checkboxes (server-side)

Question

I'm using DataTables with server-side processing to display tens of thousands rows. I need to filter these data by checkboxes. I was able to make one checkbox which is working fine, but I don't know how to add multiple checkboxes to work together. I found similar solution here, but my skills doesn't allow me to modify it to my needs:( Here is what I have tried..

My index.php:

Statuses:<br>
<input type="checkbox" id="0" onclick="myCheckFunc()" name="statuses">Open<br>
<input type="checkbox" id="1" onclick="myCheckFunc()" name="statuses">Closed<br>
<input type="checkbox" id="2" onclick="myCheckFunc()" name="statuses">Solved<br>

<script>
var idsa = 5;
$('input[name=statuses]').click(function(){
        idsa = [];
        $('input[name=statuses]:checked').each(function() {
                idsa.push($(this).attr('id'));
        });
        idsa = idsa.join(",");
        console.log("idsa fcia: " + idsa);
        $('#example').DataTable().ajax.reload();
});
</script>

The idsa variable is initially set to 5, which means all statuses(no checkbox checked) then send to server side script with it's format(d) function (this part is working fine). This is how I modify sql query in server side script:

if ($_GET['idsa'] == 5){
         $idsa = "0,1,2"; 
} else { if (isset($_GET['idsa'])) {
         $idsa = "('" . str_replace(",", "','", $_GET['idsa']) . "')"; } 
} 
$whereAll = "STATUS IN ($idsa)";

EDIT: Now after click on first of these three checkboxes, the data are filtered correctly (Open tickets with status 0), but uncheck don't bring back the initial state with all data. When I click on other two, the data are filtered, but when I uncheck, the data are fitered byt first filter (Open). When I click two or more checkboxes, I get this error:

An SQL error occurred: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

Hi Just code, that would be great, but I dynamically load data from my local database and I can't expose these data to the public. I think it's possible to do it with sample data, but isn't it too complicated? I don't know, I have some experience with making "working" example of simple problems with frontend part, but this sounds little bit harder to me. — culter
– culter, Commented Dec 4, 2018 at 8:37

culter · Accepted Answer · 2018-12-04 11:34:28Z

Ok, here is working code:

Status:<br>
  <input type="checkbox" id="0" name="statuses">Open<br>
  <input type="checkbox" id="1" name="statuses">Closed<br>
  <input type="checkbox" id="2" name="statuses">Solved<br>

<script>
var idsa = 5;
$('input[name=statuses]').click(function(){
        idsa = [];
        $('input[name=statuses]:checked').each(function() {
                idsa.push($(this).attr('id'));
        });
        idsa = idsa.join(",");
        console.log("idsa fcia: " + idsa);
        if (idsa == '') {
                idsa = 5;
        }
        $('#example').DataTable().ajax.reload();
});
</script>

And in the server-side script:

if ($_GET['idsa'] == 5){
        $idsa = "0,1,2";
} else {
        $idsa = $_GET['idsa'];
}
$whereAll = "STATUS IN ($idsa)";

The problem was with the server-side part which I grabbed from someone else and there was added another comma. Now it's working fine. Thank you very much for your time.

Alexey · Accepted Answer · 2018-12-04 08:33:34Z

0

Try wrapping your STATUS column name into backquotes, that seems to be a MySQL keyword

answered Dec 4, 2018 at 8:33

Alexey

3,4847 gold badges32 silver badges47 bronze badges

2 Comments

culter Over a year ago

Hi Alexey, I don't think this is a problem, but of course I tested it - no change :(

Alexey Over a year ago

are you able to log the query with params filled in or dump it right before the launch? I'd advise using mysql proxy to view what the server gets or if you don't want to install it, you can try dumping the full query before the launch in PHP

Collectives™ on Stack Overflow

Datatables data filtering with multiple checkboxes (server-side)

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related