Using datatables, server side processing, and formatting data using external queries

innocentius

So I have a series of pages which share a single header and footer. The header contains an include to a class.php file I created for my site. Inside this class, I have various functions that help in querying data from a SQL database using lines similar to this:

$DB->Select('SELECT * FROM `user`');

Inside the class.php file, that function looks like the following:

    public function Select($str = NULL) {
        if(is_null($str)) {
            echo "Error in mysqli_query statement: Query not provided.";
            return false;
        }
        $result = mysqli_query($this->con,$str);
        if(!$result) {
            return "Error in mysqli_query statement: " . mysqli_error($this->con);
        }
        if(mysqli_num_rows($result) > 0) {
            while($line = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                $final_results[] = $line;
            }
            return $final_results;
        } else 
            return false;
    }

This function is returning a value of an associative array and works anytime I send it a query from any page on my site outside of the datatables server file.

The footer has the following code to set up the datatables on each page, pointing to "ajax_query.php" and passing a custom parameter telling it what page I'm on:

        table = $('#output').DataTable({
            "lengthMenu": [[20, 50, 100, -1], [20, 50, 100, "All"]],
            stateSave: true,
                "deferRender": true,
            "search": {
                "regex": true
            },
            "language": {
                "search": "Search by keyword:",
                "emptyTable": "<br />There are currently no files available",
            },
            columnDefs: [
                { "orderable": false, "targets": 5 },
                { "orderable": false, "targets": 6 },
                { "width": "20%", "targets": 5 }
            ],
            "processing": true,
            "serverSide": true,
            "ajax": {
                "url": "/../includes/DataTables/ajax_query.php",
                "data": function ( d ) {
                    d.page = window.location.pathname;
                }
            }

        });

The "ajax_query.php" file has the basic setup, pointing to the same SQL database that my class points to and uses the following code example:

switch(mysqli_real_escape_string($DB->con,$_GET['page'])) {
        case "/admin/users/":
            $table = 'user';
            $columns = array(
                array( 'db' => 'fname',        'dt' => 0 ),
                array( 'db' => 'lname',        'dt' => 1 ),
                array( 'db' => 'email',        'dt' => 2 ),
                array(
                    'db'        => 'employer',
                    'dt'        => 3,
                    'formatter' => function( $d, $row ) {
// Line 1 Issue                         $employerInformation= $DB->Select('SELECT `name` FROM `employer` WHERE `id` = ' . $d);
// Line 1 Issue                         if($employerInformation) return "Success";

// Line 2 Example                       $con = mysqli_connect("localhost",$username,$password,$db);
// Line 2 Example                       $result = mysqli_query($con,'SELECT `name` FROM `employer` WHERE `id` = ' . $d);
// Line 2 Example                       $query = mysqli_fetch_array($result, MYSQLI_ASSOC);
// Line 2 Example                       return ($query ? $query['name'] : 'Unknown Employer');
                    }
                ),
            );
            break;

My issue is the "Line 1 issue" noted in the comments in the above code. In order to get to this section, it uses $DB->con, which means it is loading the class and working at that point.

If I manually insert a query connection into the formatter, as shown in Line 2 Example, it works fine while returning the name of the employer for that column value.

If I use the class to call the same query, it fails while showing the error message

DataTables warning: table id=output - Ajax error. For more information about this error, please see http://datatables.net/tn/7

I followed the link and using the debugger, the ajax_query.php page is returning a server 500 error. However if i remove the one line of code saying "$employerInformation= $DB->Select('SELECT name FROM employer WHERE id = ' . $d);" the error goes away and it works (not loading any information for that column)

I have been racking my head around why it fails and the only thing I could think of was that the ajax_query.php page is not able to reference external classes / functions for some reason. I could load an array in the class file (outside of the class, just as part of the file), and if I try to reference that array from inside the formatter, its as if it doesn't know that array exists.

Is there anything I'm missing, something else that I need to do, or just is it even possible to call other classes / functions from inside the formatter area?