Passing data from dropdowns and reloding data sent by ajax

TobyDS · February 2019

I have a table which is filled with an ajax request but I want to be able to filter the table with multiple dropdowns. Currently, I have the dropdowns populated and the table is filled with data from my ajax.php page. However, I can't figure out how to post the data from the dropdowns when they are updated and reload the table with the new data from my ajax page. Bellow is my code:
ajax2.php:

<?php
$con=mysqli_connect('localhost','root','','SportsDB')
    or die("connection failed".mysqli_errno());
$request=$_REQUEST;
$col =array(
    0   =>  'student',
    1   =>  'year',
    2   =>  'house',
    3   =>  'T1',
    4   =>  'T2',
    5   =>  'T3'
);  //create column like table in database

$sql ="SELECT st.Name AS student, st.House AS house,
(CASE WHEN st.Year = 6 THEN 'L6' WHEN st.Year = 7 THEN 'U6' ELSE st.Year END) as year,
T1.Name AS T1, T2.Name AS T2, T3.Name AS T3
From Students AS st
INNER JOIN Student_Choices AS sc
ON st.Username = sc.Username INNER JOIN Current_DB AS db
ON sc.DB_year = db.DB
INNER JOIN Choices AS c1
ON sc.T1_Choice = c1.Choice_ID
INNER JOIN Sports AS T1
ON c1.Sport_ID = T1.Sport_ID
INNER JOIN Choices AS c2
ON sc.T2_Choice = c2.Choice_ID
INNER JOIN Sports AS T2
ON c2.Sport_ID = T2.Sport_ID
INNER JOIN Choices AS c3
ON sc.T3_Choice = c3.Choice_ID
INNER JOIN Sports AS T3
ON c3.Sport_ID = T3.Sport_ID ";
$query=mysqli_query($con,$sql);
$totalData=mysqli_num_rows($query);
$totalFilter=$totalData;
//Search
$sql ="SELECT st.Name AS student, st.House AS house,
(CASE WHEN st.Year = 6 THEN 'L6' WHEN st.Year = 7 THEN 'U6' ELSE st.Year END) as year,
T1.Name AS T1, T2.Name AS T2, T3.Name AS T3
From Students AS st
INNER JOIN Student_Choices AS sc
ON st.Username = sc.Username INNER JOIN Current_DB AS db
ON sc.DB_year = db.DB
INNER JOIN Choices AS c1
ON sc.T1_Choice = c1.Choice_ID
INNER JOIN Sports AS T1
ON c1.Sport_ID = T1.Sport_ID
INNER JOIN Choices AS c2
ON sc.T2_Choice = c2.Choice_ID
INNER JOIN Sports AS T2
ON c2.Sport_ID = T2.Sport_ID
INNER JOIN Choices AS c3
ON sc.T3_Choice = c3.Choice_ID
INNER JOIN Sports AS T3
ON c3.Sport_ID = T3.Sport_ID
WHERE 1=1";
if(!empty($request['search']['value'])){
    $sql.=" AND ( st.Name Like '%".$request['search']['value']."%' ";
    $sql.=" OR st.Year Like '%".$request['search']['value']."%' ";
    $sql.=" OR st.House Like '%".$request['search']['value']."%' ";
    $sql.=" OR T1.Name Like '".$request['search']['value']."%' ";
    $sql.=" OR T2.Name Like '".$request['search']['value']."%' ";
    $sql.=" OR T3.Name Like '".$request['search']['value']."%' )";
}
$query=mysqli_query($con,$sql);
$totalData=mysqli_num_rows($query);
//Order
$sql.=" ORDER BY st.House, st.year DESC, st.Name  LIMIT ".
    $request['start']."  ,".$request['length']."  ";
$query=mysqli_query($con,$sql);
$data=array();
while($row=mysqli_fetch_array($query)){
    $subdata=array();
    $subdata[]=$row[0]; //student
    $subdata[]=$row[1]; //house
    $subdata[]=$row[2]; //year
    $subdata[]=$row[3]; //T1
    $subdata[]=$row[4]; //T2
    $subdata[]=$row[5]; //T3
    $data[]=$subdata;
}
$json_data=array(
    "draw"              =>  intval($request['draw']),
    "recordsTotal"      =>  intval($totalData),
    "recordsFiltered"   =>  intval($totalFilter),
    "data"              =>  $data
);
echo json_encode($json_data);

<?php
>
```
index 2.php:
```

      
        
          Not Set
          <?php
          include_once('connection.php');
            try{
              $stmt = $conn->prepare("SELECT * FROM Sports ORDER BY Name ASC");
              $stmt->execute();
              while ($row =$stmt->fetch(PDO::FETCH_ASSOC)){
                echo ''.$row['Name'].'';
              }
            }
            catch(PDOException $e)
            {
              echo "error".$e->getMessage();
            }
          ?>
        
?>


        <select class='form-control-sm col-md-2 mr-4' id="filter">
          <option value="">Not Set</option>
          <?php
            try{
              $stmt = $conn->prepare("SELECT * FROM Term");
              $stmt->execute();
              while ($row =$stmt->fetch(PDO::FETCH_ASSOC)){
                echo '<option value="'.$row['Term_ID'].'">'.$row['Name'].'</option>';
              }
            }
            catch(PDOException $e)
            {
              echo "error".$e->getMessage();
            }
          ?>
        </select>
        <select class='form-control-sm col-md-2 mr-4' id="Sex">
          <option value="">Not Set</option>
          <option value="M">Male</option>
          <option value="F">Female</option>
        </select>
        <select class='form-control-sm col-md-2 mr-4' id="Year">
          <option value="" >Not Set</option>
          <option value="1" >1st Form</option>
          <option value="2" >2nd Form</option>
          <option value="3" >3rd Form</option>
          <option value="4" >4th Form</option>
          <option value="5" >5th Form</option>
          <option value="6" >L6th Form</option>
          <option value="7" >U6th Form</option>
          <option value="9" >6th Form</option>
          <option value="8" >1st and 2nd Form</option>
          <option value="13" >3rd - U6th</option>
          <option value="11" >4th - U6th</option>
          <option value="12" >5th - U6th</option>
        </select>
        <select class='form-control-sm col-md-2 mr-4' id="filter_house">
          <option value="">Not Set</option>
          <option value="B" >Bramston</option>
          <option value="C" >Crosby</option>
          <option value="D" >Dryden</option>
          <option value="F" >Fisher</option>
          <option value="G" >Grafton</option>
          <option value="K" >Kirkeby</option>
          <option value="Ldr" >Laundimer</option>
          <option value="L" >Laxon</option>
          <option value="N" >New House</option>
          <option value="Sn" >Sanderson</option>
          <option value="Sc" >School House</option>
          <option value="Sco" >Scott House</option>
          <option value="S" >Sidney</option>
          <option value="StA" >St Anthony</option>
          <option value="By" >Berrystead</option>
          <option value="W" >Wyatt</option>
        </select>
      </form>
    </div>
    <div class="row">
        <div class="col-md-8 mx-auto border rounded py-3 mb-3">
          <table id="example">
              <thead>
              <tr>
                <th width="20%">Name</th>
                <th width="10%">House</th>
                <th width="10%">Year</th>
                <?php
                  try{
                    $stmt = $conn->prepare("SELECT * FROM Term");
                    $stmt->execute();
                    while ($row =$stmt->fetch(PDO::FETCH_ASSOC)){
                      echo '<th width="20%">'.$row['Name'].' Sport</th>';
                    }
                  }
                  catch(PDOException $e)
                  {
                    echo "error".$e->getMessage();
                  }
                ?>
              </tr>
              </thead>
          </table>
        </div>
      </div>
    </div>
    <script>
        $(document).ready(function(){
            var dataTable=$('#example').DataTable({
              "processing": true,
              "serverSide":true,
              lengthMenu: [[10, 25, 100, 5000], [10, 25, 100, "All"]],
              pageLength: 10,
              "ajax":{
                  url:"ajax2.php",
                  type:"post",
              },
              'dom': 'Bfrtipl',
              'buttons': [
                {
                  "text" : 'Email'
              },
              {
              extend: 'excel',
              },
              {
                extend: 'pdf',
                orientation: 'landscape',
                title: 'Oundle School Student Sports Options',
                download: 'open',
                // Function to automatically size and center each collumn in export
                customize: function (doc) {
                  doc.content[1].table.widths =
                  Array(doc.content[1].table.body[0].length + 1).join('*').split('');

                  var rowCount = doc.content[1].table.body.length;
                  for (i = 1; i < rowCount; i++) {
                    doc.content[1].table.body[i][0].alignment = 'center';
                    doc.content[1].table.body[i][1].alignment = 'center';
                    doc.content[1].table.body[i][2].alignment = 'center';
                    doc.content[1].table.body[i][3].alignment = 'center';
                    doc.content[1].table.body[i][4].alignment = 'center';
                    doc.content[1].table.body[i][5].alignment = 'center';
                  };
                },
              },
              ]
            });
        });
    </script>
</body>

allan · February 2019

Call ajax.reload() when your select elements trigger a change event. Also use ajax.data as a function to submit data to the server.

Allan

Passing data from dropdowns and reloding data sent by ajax

Passing data from dropdowns and reloding data sent by ajax

Answers

Howdy, Stranger!

Categories

DataTables

Passing data from dropdowns and reloding data sent by ajax

Passing data from dropdowns and reloding data sent by ajax

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables