I want to ask how can I insert my sql query to the html datatable table body.

This is my present code:

AJAX Query for loading datatable after button click:

$(document).on('click','#filtersearch',function(e){
    e.preventDefault();
            
    $.ajax({
            url:"index.php",
            method:"POST",
            data:{
             
                formula:"filtersearch"
            },
            dataType:"json",
            beforeSend:()=>{
                $('.load_spinner').removeClass('d-none');
            },
            success:function(res){
                $('.load_spinner').addClass('d-none');
                select_d = res;
                console.log(res);
                
                var str ="";
                if (!$.isEmptyObject(select_d)) {
                    select_d.forEach((x)=>{     

                    str  = `<tr>
                                <td>${x.assetid}</td>
                                <td>${x.assetcode}</td>
                                <td>${x.assetserial}</td>
                                <td>${x.assetname}</td>
                                <td>${x.assettype}</td>
                                <td>${x.assetcat}</td>
                                <td>${x.dpurchased}</td>
                                <td>${x.price}</td>
                                <td>${x.dperiod}</td>
                                <td>${x.finprice}</td>
                                <td>${x.status}</td>
                                <td>${x.assetage}</td>
                                <td>${x.location}</td>
                            </tr>`;
                    })
              
                }         

            
                data_table("#table_index","#tbody_index",str);
            }
        })

})

Javascript for Datatable Content transfer from AJAX:

      function data_table(table_name,tbody_name,data_tbody) {
            $(table_name).DataTable().destroy();
            $(tbody_name).empty().html(data_tbody);
            $(table_name).DataTable();
       

};

Datatable HTML cointainer that will get the ajax query:

   <table  id="table_index" width="100%" cellspacing="0">
                                <thead>
                                    <tr>
                                        <th>No.</th>
                                        <th>Asset Code</th>
                                        <th>Asset Serial</th>
                                        <th>Asset Name</th>
                                        <th>Category</th>
                                        <th>Type</th>
                                        <th>Date Purchased</th>
                                        <th>Initial Price (PHP)</th>
                                        <th>Depreciation Period</th>
                                        <th>Final Price (PHP)</th>
                                        <th>Status</th>
                                        <th>Classification</th>
                                        <th>Location</th>
                                    </tr>
                                </thead>
                                <tbody id="tbody_index">
                                </tbody>
                               
                            </table>

PHP code for database query:

<?php

 include 'include/dbconfig.php';
    $sql = 'SELECT * FROM tbl_assets';
    
    $result = mysqli_query($conn, $sql);
    
        $formula ='';
    
        if (isset($_POST['formula'])) {
            $formula = $_POST['formula'];
        }
        switch ($formula) {
          
            case 'filtersearch':
                    $result = filtersearch();
                    $supData = array();
                    while ($row = $result->fetch_assoc()) {
                        $supData[] = $row;
                    }
                    echo json_encode($supData);
                    break;
            default:
            break;
                          }
    
    
    
    
    function filtersearch()
        {
            include 'include/dbconfig.php';
            $query = mysqli_query($conn,"SELECT * FROM tbl_assets");
            return $query;
        }


?>

I just want to ask what is wrong with my code since the script doesn't show the values of Tbody as intended. Thanks.

CodePudding user response：

I found a solution after manipulating the pages instead.

Instead of coding all of them in one page, I tried creating another page (switchcase.php) that contains the PHP files and it worked as intended.

Just a hunch, but I think ajax doesn't accept urls of the same page. I don't know if thats how it works but yeah, I changed the url to switchcase.php and it worked.

CodePudding user response：

if you using datatable with ajax and php try this way

<script>

$(function(){
    
    $('#table_index').dataTable( {
        'lengthMenu': [[10, 25, 50, 100, 500], [10, 25, 50, 100, 500]],
        'processing': true,
        'serverSide': true,
        'serverMethod': 'post',
        'order': [[ 1, "desc" ]],
        'ajax': {
            'url': 'index.php'
        },
        "columns": [
            { "data": "id" },
            { "data": "asset_code" },
            { "data": "asset_serial" ,'bSortable': false},
            { "data": "asset_name" ,'bSortable': false},
            { "data": "category_id" ,'bSortable': false},
            { "data": "type", 'bSortable': false},
            { "data": "date_purchased"},
            { "data": "initial_price" },
            { "data": "depreciation_period" },
            { "data": "final_price" },
            { "data": "status" ,'bSortable': false},
            { "data": "classification" },
            { "data": "location" }
        ]
    });
    $.fn.dataTable.ext.errMode = 'none';

});

</script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table  id="table_index">
    <thead>
        <tr>
            <th>No.</th>
            <th>Asset Code</th>
            <th>Asset Serial</th>
            <th>Asset Name</th>
            <th>Category</th>
            <th>Type</th>
            <th>Date Purchased</th>
            <th>Initial Price (PHP)</th>
            <th>Depreciation Period</th>
            <th>Final Price (PHP)</th>
            <th>Status</th>
            <th>Classification</th>
            <th>Location</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>