Dynamic table with Datatables and AJAX

Question

I am creating a class to generate CRUD tables based on the parameters passed to it.

I don't know how to correctly pass my data in the "ajax": and "columns": request so that the table is created for me.

I am using the following 4 files:

1.- main.php: This file contains my class GenerateCrud.

<?php
        
    class GenerateCrud {
        
        // Properties.
        
            public $tableName;
            public $id;
            public $tableFields = array();
    
        // Constructor.
        
            function __construct($tableName, $id, $tableFields){
                $this->tableName = $tableName;
                $this->id = $id;
                $this->tableFields = $tableFields;
            }
            
            public function create(){               
                if(empty($_SESSION['Cookie'])){
                    $strCookie = tempnam("/tmp", "COOKIE");
                    $_SESSION['Cookie'] = $strCookie;
                }else{
                    $strCookie = $_SESSION['Cookie'];
                }
                
                // Creating the session.
                $curl_handle = curl_init (SITE_URL.'/admin/login.php');
                curl_setopt ($curl_handle, CURLOPT_COOKIEJAR, $strCookie);
                curl_setopt ($curl_handle, CURLOPT_RETURNTRANSFER, true);
                $vars= 'user=cron&pass='.CRON_PASS.'&action='.md5('login');
                curl_setopt($curl_handle, CURLOPT_POSTFIELDS, $vars);
                $output = curl_exec ($curl_handle);

                // Loging in.           
                curl_setopt ($curl_handle, CURLOPT_URL, SITE_URL_ADMIN.'/alexcrudgenerator/crud/res/');
                curl_setopt ($curl_handle, CURLOPT_COOKIEJAR, $strCookie);
                curl_setopt ($curl_handle, CURLOPT_RETURNTRANSFER, true);
                $vars= 'action=showtable&tableName='.$this->tableName.'&id='.$this->id.'&tableFields='.json_encode($this->tableFields);
                curl_setopt($curl_handle, CURLOPT_POSTFIELDS, $vars);               
                $output = curl_exec ($curl_handle);
                
                // I get the result.
                return $output;     
?>
<?php 
            }
    }
?>

2.- res.php: Contains the code responsible for constructing the table.

<?php

    include_once(DIR_PLUGINS.'/alexcrudgenerator/main.php');
    
    $test = new GenerateCrud('users_test', '2', ['usuario', 'apellido1', 'apellido2', 'email']);
    
    switch($_POST['action']){
        
        case 'showtable':
            
            $res = getEntireTable();
            
            // Getting the <TH>.
            $theCol = array();
            foreach ($test->tableFields as $r){
                $theCol[]=array('data'=>$r);
            }
            $myHeader = json_encode($theCol);
            echo $myHeader . '<br><br>';

            // Gettint the columns (data of my database).
            $json = array();
            foreach ($res as $data){
                $json['data'][] = $data;            
            }
            $myContent = json_encode($json);
            echo $myContent;
                
?>
            <div class="container caja">
                <div class="row">
                    <div class="col-lg-12 col-sm-12">
                        <div>
                            <table id="tablaUsuarios" class="table table-striped table-bordered table-condensed" style="width:100%" >
                                <thead class="text-center">
                                </thead>
                                <tbody>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
            
            <script>
                $(document).ready(function() {
                    var tableName = "<?= $test->tableName; ?>";
                    var id = "<?= $test->id; ?>";
                    var tableFields = "<?= $test->tableFields; ?>";
                    var tableFieldsJson = <?= json_encode($test->tableFields); ?>;
                    var myContent = <?= $myContent ?>;
                    var myHeader = <?= $myHeader ?>;

                    console.log(myContent);

                    $('#crudTable').DataTable({
                        "language": {"url": "//cdn.datatables.net/plug-ins/1.10.20/i18n/Spanish.json"},
                        "paging": true,
                        "lengthChange": true,
                        "searching": true,
                        "info": true,
                        "autoWidth": true,
                        "scrollX": true,

                        "ajax": {
                            "url": '<?=SITE_URL_ADMIN?>/alexcrudgenerator/crud/res/',
                            "method": "POST",
                            "data": {myContent, action: 'showtable'}
                        },
                        "columns": [
                            {"data": myHeader},
                            {"defaultContent": "<div class='text-center'><div class='btn-group'><button class='btn btn-primary btn-sm btnEditar' value='edit'><i class='material-icons'>edit</i></button><button class='btn btn-danger btn-sm btnBorrar'><i class='material-icons' value='delete'>delete</i></button></div></div>"}
                        ]
                    });
                })
            </script>
<?php
        break;      
}
?>

3.- funciones.php: Contains functions (mainly database calls).

<?php

    function getEntireTable(){
        global $DB;
        
        $test = new GenerateCrud('users_test', '2', ['usuario', 'apellido1', 'apellido2', 'email']);
        
        $myStringArray = implode(",", $test->tableFields);
            
        $sql = "SELECT $myStringArray FROM $test->tableName";
        $res = $DB->get_records($sql);
        
        return $res;
    }

?>

4.- test.php: The file where I will create the final table (This is what the user would see).

<?php
    
        // Instanced object
    
        $test = new GenerateCrud('users_test', 'id', ['usuario', 'apellido1', 'apellido2', 'email']);
        $res = $test->create();
    
        echo $res;
        
?>

My problem: I'm trying to pass $myHeader and $myContent as "data": to build my table, but I'm doing something wrong and I don't know what.

(I have checked if any request is sent in the "Network" tab of my browser, but no request is launched).

$myHeader: Contains the TH of my table.
$myContent: Contains my database content.

Here an image to see it more clearly.

Exactly, what I'm doing wrong? I have those 2 variables correctly obtained in JSON format.

Thanks in advance, guys, and have a good Monday!

Weber · Accepted Answer · 2021-06-22 08:54:16Z

I think you missed up something in js and php return data.

Let's start with js, usually i set this option in datatable call for do a ajax table:

"processing": true,
"serverSide": true,
"ajax":{
   "url" : MyUrl,
   "type": "POST",
    "data": function ( d ) {
        d.somedata = "some data";
    },
    "error": function(){
        $("#table").css("display","none");
        //here you can also display a error message for end user
    },
},
drawCallback: function() {
   //here you can call whatever function in js need to be restored
   //also buttons function or tooltips or popovers
},

For php datatable want the data return in specific format json:

$requestData    =   $_REQUEST;//in this $requestData you will get every parameter from $_POST and $_GET and can be used in elaboration of the data of the table
$query = "myquery";
$row_db = $mysqli->query($query);
$TotalRow = $row_db->num_rows;
//Apply here the filter based on search or whatelse
$row_db = $mysqli->query($query);
$RowFiltered = $row_db->num_rows;
$rowTable = array();
while($row = $row_db->fetch_assoc()){ //or fetch_object
   $nestedData = array();
   $nestedData[] = $row["col1"];
   $nestedData[] = $row["col2"];
   //at the end of insert data in nestedData array put this
  $rowTable[] = $nestedData;
}

When all data is ready to be print out for datatable

$json_data = array(
     "draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
     "recordsTotal"    => intval( $TotalRow ),  // total number of records
     "recordsFiltered" => intval( $RowFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
     "data"            => $rowTable   // total data array
);
print_r(json_encode($json_data));

In the final result the "data" printed out from php is really differt from your(imge) data you show up.

Collectives™ on Stack Overflow

Dynamic table with Datatables and AJAX

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related