Get MySQL Query using Ajax then Inserting Result to Data Table on button click

Question

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 class="table table-bordered" 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.

Does this solve your problem? PHP to JSON encode with associative array — bloodyKnuckles
– bloodyKnuckles, Commented Jun 16, 2022 at 9:34
It says no error so the problem I think lies on the Javascript datatable query — entry_level_developer
– entry_level_developer, Commented Jun 16, 2022 at 10:29
This code is meant to only display the table after clicking the button (Filter) coz I tried it before without Ajax Jquery and the datatable links (script src and link href) are working. I'm not well-versed on codes of datatables so I would like to know if someone has an idea on how I should call the body from the ajax instead. The ajax was working properly since the developer tools (network section) already shows the array to be inputted to the datatable. — entry_level_developer
– entry_level_developer, Commented Jun 16, 2022 at 10:31
Are you saying json_last_error_msg() shows no error? Or PHP is showing no error message. Try echo json_encode($supData); echo json_last_error_msg();. — bloodyKnuckles
– bloodyKnuckles, Commented Jun 16, 2022 at 10:38
{"assetid":"1379","assetcode":"","assetserial":"","assetname":"Round Table ","assettype":"Other Assets","assetcat":"Furniture","dpurchased":"2022-01-27","dperiod":"10","price":"2000","finprice":"2000","image":"","status":"Working","location":null,"assetage":null}]No error <---------This is the last line. — entry_level_developer
– entry_level_developer, Commented Jun 16, 2022 at 11:22

entry_level_developer · Accepted Answer · 2022-06-17 05:41:41Z

0

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.

edited Jun 17, 2022 at 5:41

answered Jun 17, 2022 at 5:40

entry_level_developer

32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Farjan · Accepted Answer · 2022-06-16 11:11:51Z

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 class="table table-hover table-nomargin table-condensed" 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>

You shouldn't post such a dangerous code that is prone to SQL injection

Collectives™ on Stack Overflow

Get MySQL Query using Ajax then Inserting Result to Data Table on button click

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related