Datatable Manual Search with Ajax

Question

I use Datatable with Ajax... I want to do a manual search of coulombs but I couldn't do it.

Datatable working no problem with it... Everything working well... But I couldn't handle the manual search. Please can someone help me?

What I try is :

<table id="tedarik_data"  class="table table-bordered table-hover table-sm table-striped " width=100%>
<thead>
<tr>
   <th><input type="text" id="soyisim_ara" class="form-control form-control-sm" placeholder="Soyisim Ara..."></th>
<th><input type="text" id="birim_ara" class="form-control form-control-sm" placeholder="Search..."></th>
<th>Güncelle</th>
<th>Sil</th>
</tr>
</thead>

<script>
$('#birim_ara').on( 'keyup', function () {
  birim_ara= this.value ;
  DataTable.draw();
});


var DataTable = $('#tedarik_data').DataTable({
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"team/fetch.php",
type:"POST",
data:{birim_ara:birim_ara}
},
"columnDefs":[{"targets":[0,1,2,3,4,5,6,7,8,9],"orderable":false,"searchable": true}],
"paging": false,
"searching": false,
"autoWidth": true,
"dom": '<"top"lp<"clear">>rt<"bottom"ifp<"clear">>'
});

</script>

and the team/fetch.php file below ;

<?php
include('../db.php');
include('function.php');
$birim_ara = $_POST['birim_ara'];
$query = "SELECT * FROM team WHERE birim LIKE ? ";
$params = array("%$birim_ara%");
$output = array();
$statement = $connection->prepare($query);
$statement->execute($params);
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
    foreach($result as $row)
    {
        
        $sub_array = array() ;
        $sub_array[] = $row["isim"];
        $sub_array[] = $row["birim"];

    
        $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-sm updatemusteri">Update</button>';
        $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-sm tedarikdelete">delete</button>';
        $data[] = $sub_array;
    }
    $output = array(
        "draw"              =>  intval($_POST["draw"]),
        "recordsTotal"      =>  $filtered_rows,
        "recordsFiltered"   =>  get_total_all_records(),
        "data"              =>  $data
    );
    echo json_encode($output);
    ?>

**Warning:** Your code is vulnerable to SQL Injection attacks. You should use prepared statements **and parameters** to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli / PDO. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data, or just break your query with a simple `'`. — ADyson, Jun 11 '21 at 10:28
Anyway, you need to clarify your problem. `couldnt do it` gives us no idea what is actually happening with your code. What specifically goes wrong when you try to run the search? Do you get any Javascript or PHP errors? Or AJAX problems? What debugging have you done, actually? You need to be more precise about the issue. We can't run your code to see what happens, we can only stare at it and look for potential issues, but that doesn't usually tell the whole story. — ADyson, Jun 11 '21 at 10:29
please can you give an example.. I dont know about security much . if you help me ı will be appreciate Thanks... — Haluk Aydoğan, Jun 11 '21 at 10:30
I literally just gave you a link to a place which has examples. — ADyson, Jun 11 '21 at 10:30
If you need something more specific for a LIKE query, then that's easy to google, but here you go anyway: https://stackoverflow.com/questions/11117134/implement-like-query-in-pdo — ADyson, Jun 11 '21 at 10:31
when I start to type and work key up.. datatable always response as "birim_ara='empty'".. never filter.. I use bootsrap 5.0. — Haluk Aydoğan, Jun 11 '21 at 10:31
Ok. That's the visual symptom of the problem in the UI. But any user could us that. You're a programmer...so how did you start to debug that? What detailed investigations have you made, to try and narrow down the issue to a specific area in your code? Do you understand what debugging is? If not, then now would be a good time to learn, otherwise you'll never be able to solve even the simplest problems with your code. — ADyson, Jun 11 '21 at 10:32
for example I put birim_ara="OFFICE" at the top of code it works... only OFFICE coloumbs shown.. but the code which is not working; — Haluk Aydoğan, Jun 11 '21 at 10:34
$('#birim_ara').on( 'keyup', function () { var birim_ara= this.value ; DataTable.draw(); }); — Haluk Aydoğan, Jun 11 '21 at 10:34
That code it not working well... it always search empty value.. — Haluk Aydoğan, Jun 11 '21 at 10:35
Do you understand about "scope" in Javascript (and other programming languages)? `var birim_ara` defines a variable inside your `function()`. That variable and its value does not exist outside the function. If there is another variable elsewhere with the same name, that is not the same variable. Remove the `var` and it might work better, as it will use the global variable with the same name. — ADyson, Jun 11 '21 at 10:35
`not working well... it always search empty value`...again, just a symptom. You appear to have made no attempt to actually debug it. https://developer.chrome.com/docs/devtools/javascript/ — ADyson, Jun 11 '21 at 10:36
I will watch that video.. Also please can you just give me an example of security wrong in my code? — Haluk Aydoğan, Jun 11 '21 at 10:39
This bit: `'WHERE birim LIKE "%'.$_POST['birim_ara'].'%" ` - i.e. the bit where you include user input directly into your query without parameterising it. (You're using prepared statements, which is a good start, but they are pointless if you don't also use parameters as well.) — ADyson, Jun 11 '21 at 10:42
Anyway did you try removing the `var` as I suggested above? Does it change the behaviour at all? Any errors in your console, or in PHP? Does it send the right value in the AJAX request? All this info you can easily check with your developer tools. — ADyson, Jun 11 '21 at 10:43
I deleted "var" in front of birim_ara but nothing change.. eachtime I push a button datatable response "Processing" but nothing change. — Haluk Aydoğan, Jun 11 '21 at 10:52
for 2 days ı tried to solve that and at last ı asked here.. — Haluk Aydoğan, Jun 11 '21 at 10:53
That's why you need to debug it then and find out specifically what is going on underneath, in the code, line by line. We can't run the code, so we can only sit at the side and make suggestions. — ADyson, Jun 11 '21 at 10:53
My next suggestion would be to fix the SQL query, as it's not just security that's wrong with it, there are potentially other issues too, but a parameter would solve them. Again, see https://stackoverflow.com/questions/11117134/implement-like-query-in-pdo for how to do a LIKE query properly. — ADyson, Jun 11 '21 at 10:54
I updated codes as you write down... table is working no problem but still searching doesnt working... "processing" but always show whole table... — Haluk Aydoğan, Jun 11 '21 at 11:31
Ok good. That looks like it should work better for the query. Did you test it (including testing independently of datatables)? What was the result? What is happening in your code now? You're _still_ not providing any useful information about the behaviour of your code. I suggest you spend some time learning how to debug, and then come back to this problem. There is nothing more I can do for you without more specific information. — ADyson, Jun 11 '21 at 12:13
I am prety sure that : the wrong is in the code next message — Haluk Aydoğan, Jun 11 '21 at 12:17
$('#birim_ara').on( 'keyup', function () { birim_ara = this.value ; DataTable.draw(this.value); }); — Haluk Aydoğan, Jun 11 '21 at 12:17
if I call alert(This.value) I get correct data... but datatable.draw(this.value) is always search "empty" cell :( — Haluk Aydoğan, Jun 11 '21 at 12:18
I don't know datatables very well, but from quickly reading the docs I suspect draw() isn't actually the function you need - that re-draws the table, but as far as I can see it doesn't fetch the data again. Again, if you were debugging properly, you'd be able to tell me if any calls to fetch.php are actually occurring. But I suspect that ajax.reload() is actually the function you should be calling, to make it refresh the data from the server. https://datatables.net/reference/api/ajax.reload() — ADyson, Jun 11 '21 at 12:24
a clue = DataTable.clear().draw(); also doesnt work.. refresh same table.. — Haluk Aydoğan, Jun 11 '21 at 13:24
I watch video 3 times, but I couldnt connect it with my problem unfortunatly :( — Haluk Aydoğan, Jun 11 '21 at 14:16
Why, what was confusing you? Basically you need to use the Developer Tools in your browser to a) step through your code as it runs, and check what the code does and what values your variables have, b) add console.log commands and then check the console, as an alternative way to check what values your variables have and where your code goes to, c) check for script errors in the Console, and d) check for AJAX problems in the Network tool and see what response is coming from your PHP script (if any). — ADyson, Jun 11 '21 at 14:19
Then as an additional step, you may need to debug the PHP code as well, in case that's causing any problems. There's some guidance on basic PHP debugging here: http://www.phpknowhow.com/basics/basic-debugging/ — ADyson, Jun 11 '21 at 14:20
P.S. Debugging is a basic skill which every programmer needs to start learning almost as soon as they write their first program. I am continually astonished when I look at questions on this site - such as this one - where people attempt to write reasonably complex programs, but seemingly without having understood (or apparently even attempted to find out) anything about how they might go about testing them or detecting errors in them. Instead they seem to rely entirely on guesswork and luck, or hoping other people will figure it out for them. It must be a very frustrating way to work. — ADyson, Jun 11 '21 at 14:20
you are so kind thanks.. I am on debugging now.. searching and learning it.. — Haluk Aydoğan, Jun 11 '21 at 14:32
'data': function(data){ var gender = $('#searchByGender').val(); var name = $('#searchByName').val(); data.searchByGender = gender; data.searchByName = name; } — Haluk Aydoğan, Jun 11 '21 at 21:54
Please write it in full as an answer below, then others may find it if searching, and you may receive upvotes. Really pleased that you solved it and hopefully learned a lot too — ADyson, Jun 11 '21 at 22:32

Haluk Aydoğan · Answer 1 · 2021-06-12T06:46:32.560

I Changed the script as below : the data sending method is important...

<script>

var dataTable = $('#team_data').DataTable({
"language": {
"lengthMenu": "Sayfa Başına _MENU_ ürün Göster",
"zeroRecords": "Aradığınız Kriterlere uygun Tedarikçi Bulunamadı, Özür Dileriz!",
"info": "",
"infoEmpty": "Hiç Bir Kayıt Uygun Değil",
"infoFiltered": "(_TOTAL_  Tedarikçi arasından _MAX_ Tedarikçi filtrelendi)",
"search": "Tedarikçiler Arasında Ara",
"paginate": {
"previous": "Önceki ",
"next": "Sonraki "
}},
"processing":true,
"serverSide":true,
"order":[],
'serverMethod': 'post',
'ajax': {
       'url':'team/fetch.php',
       'data': function(data){
          var soyisim_ara= $('#soyisim_ara').val();
          var birim_ara= $('#birim_ara').val();
          data.soyisim_ara= soyisim_ara;
          data.birim_ara= birim_ara;
       }
    },
"columnDefs":[{"targets":[0,1,2,3,4,5,6],"orderable":false,"searchable": true}],
"paging": true,
"searching": true,
"autoWidth": true,
"dom": '<"top"lp<"clear">>rt<"bottom"ifp<"clear">>'
});
$('#soyisim_ara').keyup(function(){
    dataTable.draw();
  });

  $('#birim_ara').change(function(){
    dataTable.draw();
  });
</script>

Datatable Manual Search with Ajax

1 Answers1