How to Paginate PHP Dropdown Search Result with Datatable

How to Paginate PHP Dropdown Search Result with Datatable

gautambosegautambose Posts: 8Questions: 3Answers: 0

I'm trying to get dropdown search results to paginate by the use of Datatable.But for some reason Datatable Pagination is not working properly. Is it possible to paginate search result differently? Anyone able to help please? Code is below: Index.php and loadData.php I am enclosing my search result
index.php

<?php
//index.php

$connect = new PDO("mysql:host=localhost;dbname=leadsqqe_all", "leadsqqe_bl", "gbose123BO*");

$query = "SELECT DISTINCT City FROM company ORDER BY City ASC";

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

?>

```html
<!DOCTYPE html>

<html>
<head>
<meta charset="UTF-8">
<title>Leadstart test</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
<link href="cssleadstart/bootstrap-select.min.css" rel="stylesheet" />
<script src="jsleadstart/bootstrap-select.min.js"></script>
<!-------------Data Table----->

<link href='https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>


    <!-------------Data Table-----> 
    <style>


        #search_filter_city{
            width: 50px;
            height: 50px;
            overflow-style: scrollbar

        }
    </style>
</head>
<body>
   <div class="container">
        <br />
        <h2 align="center"></h2><br />

        <select name="search_filter_city" id="search_filter_city" multiple class="form-control selectpicker">
        <?php
        foreach($result as $row)
        {
            echo '<option value="'.$row["City"].'">'.$row["City"].'</option>';  
        }
        ?>
        </select>
        <input type="hidden" name="hidden_city" id="hidden_city" />
        <div style="clear:both"></div>
        <br />
        <div class="">
            <table id="aero" class="display table table-striped table-bordered  nowrap ui celled responsive page_table" style=" ">
                <thead>
                    <tr>
                        <th>Company Name</th>
                        <th>City</th>
                        <th>State</th>
                        <th>Zip Code</th>
                        <th>Country</th>
                    </tr>
                </thead>
                <tbody>
                </tbody>
            </table>
        </div>
        <br />

    </div>
     <!-------------Data Table----->


    <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
 ```
```js   
<script>
$(document).ready(function() {
    $('#aero').DataTable({
        "pagingType": "simple",
        "searching": false,
        "lengthChange": true,
        "pageLength": 5
    });
});
</script>
    
 ```js     

<script>

$(document).ready(function(){

    //load_data();
    
    function load_data(query='')
    {
        $.ajax({
            url:"loadData.php",
            method:"POST",
            data:{query:query},
            success:function(data)
            {
                $('tbody').html(data);
            }
        })
    }

    $('#search_filter_city').change(function(){
        $('#hidden_city').val($('#search_filter_city').val());
        var query = $('#hidden_city').val();
        load_data(query);
    });
    
});
</script>

</body>

</html>

loaddata.php

<?php

//fetch.php

$connect = new PDO("mysql:host=localhost;dbname=leadsqqe_all", "leadsqqe_bl", "gbose123BO*");

if($_POST["query"] != '')
{
    $search_array = explode(",", $_POST["query"]);
    $search_text = "'" . implode("', '", $search_array) . "'";
    $query = "
    SELECT * FROM company 
    WHERE City IN (".$search_text.") 
    ORDER BY companyID DESC
    ";
}
else
{
    $query = "SELECT * FROM company ORDER BY companyID DESC";
}

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

$total_row = $statement->rowCount();

$output = '';

if($total_row > 0)
{
    foreach($result as $row)
    {
        $output .= '
        <tr>
            <td>'.$row["CompanyName"].'</td>
            <td>'.$row["City"].'</td>
            <td>'.$row["State"].'</td>
            <td>'.$row["ZipCode"].'</td>
            <td>'.$row["Country"].'</td>
        </tr>
        ';
    }
}
else
{
    $output .= '
    <tr>
        <td colspan="5" align="center">No Data Found</td>
    </tr>
    ';
}

echo $output;


?>

This question has accepted answers - jump to:

Answers

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

    Looks like you are initializing Datatables before the data is loaded into the table. So Datatables sees an empty table. You are adding the table data directly to the DOM which Datatables doesn't know about so it always reports the table as empty.

    Try moving the Datatables init code into the success function of your Ajax call. Place it after the $('tbody').html(data); statement, like this:

            $.ajax({
                url:"loadData.php",
                method:"POST",
                data:{query:query},
                success:function(data)
                {
                    $('tbody').html(data);
                    $('#aero').DataTable({
                      "pagingType": "simple",
                      "searching": false,
                      "lengthChange": true,
                      "pageLength": 5
                  });
                }
            })
    

    Kevin

  • gautambosegautambose Posts: 8Questions: 3Answers: 0

    Thank You Very Much

  • gautambosegautambose Posts: 8Questions: 3Answers: 0

    Following message I receive

    DataTables warning: table id=aero not reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    Answer ✓

    Did you read the diagnosis provided by the link? http://datatables.net/tn/3

  • gautambosegautambose Posts: 8Questions: 3Answers: 0

    Yes

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734
    Answer ✓

    I'm guessing by the one word response that the link didn't help you solve the problem. If you are calling load_data() more than once then you will cause a reinitialization of the Datatable with the change I suggested. In order to reinitialize the Datatable you can add the destroy option as shown in this section of the technote. Like this:

    $.ajax({
        url:"loadData.php",
        method:"POST",
        data:{query:query},
        success:function(data)
        {
            $('tbody').html(data);
            $('#aero').DataTable({
              "destroy": true,
              "pagingType": "simple",
              "searching": false,
              "lengthChange": true,
              "pageLength": 5
          });
        }
    })
    

    Kevin

  • gautambosegautambose Posts: 8Questions: 3Answers: 0

    Thank You.It's OK Now

This discussion has been closed.