Pass a Select Form to database to populate the Datatable

Question

I really can't figure out how to pass a form to datatables.

I'm trying to have a select form, this form will be use as my WHERE clause to my query on database to populate the datatables. Here's my current code.

Index.php

<form method="POST" id="frm">
<select name="selectplace">
    <option value="PLACE 1">PLACE 1</option>
    <option value="PLACE 2">PLACE 2</option>
    <option value="PLACE 3">PLACE 3</option>
</select>
<button type="submit" name="submitPlace">SUBMIT</button>

          <div class="table-responsive">
            <table class="table table-bordered table-striped text-center" id="place-table">
              <thead>
                <tr>
                  <th>PLACE #</th>
                  <th>PLACE NAME</th>
                  <th>TOTAL VISITORS</th>
                </tr>
              </thead>
              <tfoot>
                 <tr>
                  <th>PLACE #</th>
                  <th>PLACE NAME</th>
                  <th>TOTAL VISITORS</th>
                </tr>
              </tfoot>
            </table>
          </div>

JQUERY for datatable

       $(document).ready(function() {
      $('#place-table').DataTable({
        "ajax": {
          url: "json.php",
          "dataSrc": "",
            "data": function(d) {
            var frm_data = $('frm').serializeArray();
           $.each(frm_data, function(key, val) {
           d[val.name] = val.value;
   });
 }
        },
        columns: [{
          data: 'place_id',
        }, {
          data: 'place_name',
        }, {
          data: 'total_visitor',
        }]
      });
    });
  </script>

json.php

This where I want to pass the form so I can use it as my WHERE clause

<?php 
  $selectedplace = $_POST['selectedplace'];
  $sql = "SELECT * FROM placestable WHERE $selectedplace";
    $result = mysqli_query($conn, $sql);
    $data = array();
    while($row = mysqli_fetch_assoc($result)) {
        $data[] = array(
            "id"=>$row['id'],
            "place_name"=> $row['place_name'],
            "total_visitor"=> $row['total_visitor'],
        );
    }
    echo json_encode($data); //before was: echo json_encode(array('data' => $data));
     ?>

`var frm_data = $('frm').serializeArray();` - there is no element with the _tag name_ `frm` anywhere. If you want to select an element by ID, this needs the `#` prefix. — CBroe, Nov 19 '21 at 07:39
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 19 '21 at 13:26

score 0 · Answer 1 · answered Nov 19 '21 at 04:13

First thing is you need to reload the ajax from datatable whenever you submit the form. you can do that with submit event with jquery and rerender the ajax, like this:

$("#idForm").submit(function(e) {
   e.preventDefault();

   table.ajax.reload() 
});

And don't forget to assign your datatable to table variable

 var table = $('#place-table').DataTable({....})

Pass a Select Form to database to populate the Datatable

1 Answers1