Need advice to make php server side Datatables work properly

Question

There's something weird going on with server-side Datatables from this: https://github.com/n1crack/Datatables.

Don't understand what's going on, i do exactly the same thing as the examples but it simply doesn't work. According to the "where" example https://github.com/n1crack/Datatables/blob/master/public/examples/where/ajax.php, look how simple it should be:

$dt = new Datatables(new MySQL($config));
$dt->query("Select film_id as fid, title, description from film where film_id > 47 and film_id < 83");
echo $dt->generate();

My code:

$config = [ 'host'     => 'localhost',
        'port'     => '3306',
        'username' => 'root',
        'password' => '',
        'database' => 'sys_db_gincana' ];

        $dt = new Datatables( new MySQL($config) );
        $dt->query("
        SELECT seen, id, name, cep, date_format(created,'%d/%m/%Y %h:%i:%s') as created
        FROM user
        ");

        echo $dt->generate();

Now look at what my js console prints from my ajax:

A PHP Error was encountered
Severity: 4096

Message: Argument 1 passed to PHPSQLParser\builders\WhereBuilder::build() must be of the type array, boolean given, called in C:\Program Files (x86)\EasyPHP-DevServer-14.1VC11\data\localweb\projects\gincana\vendor\greenlion\php-sql-parser\src\PHPSQLParser\builders\SelectStatementBuilder.php on line 74 and defined

Filename: builders/WhereBuilder.php

Line Number: 112

A PHP Error was encountered
Severity: Warning

Message: Invalid argument supplied for foreach()

Filename: builders/WhereBuilder.php

Line Number: 114

A PHP Error was encountered
Severity: 4096

Message: Argument 1 passed to PHPSQLParser\builders\WhereBuilder::build() must be of the type array, boolean given, called in C:\Program Files (x86)\EasyPHP-DevServer-14.1VC11\data\localweb\projects\gincana\vendor\greenlion\php-sql-parser\src\PHPSQLParser\builders\SelectStatementBuilder.php on line 74 and defined

Filename: builders/WhereBuilder.php

Line Number: 112

A PHP Error was encountered
Severity: Warning

Message: Invalid argument supplied for foreach()

Filename: builders/WhereBuilder.php

Line Number: 114

Fatal error: Call to a member function fetch_assoc() on a non-object in C:\Program Files (x86)\EasyPHP-DevServer-14.1VC11\data\localweb\projects\gincana\vendor\ozdemir\datatables\src\DB\MySQL.php on line 40

And if if i change the query to:

        $dt = new Datatables( new MySQL($config) );
        $dt->query("
        SELECT seen, id, name, cep, date_format(created,'%d/%m/%Y %h:%i:%s') as created
        FROM user
        WHERE approved = 0 and canceled = 0
        ");
        echo $dt->generate();

the error changes:

A PHP Error was encountered
Severity: Warning

Message: array_merge(): Argument #2 is not an array

Filename: src/Datatables.php

Line Number: 47

Fatal error: Call to a member function fetch_assoc() on a non-object in C:\Program Files (x86)\EasyPHP-DevServer-14.1VC11\data\localweb\projects\gincana\vendor\ozdemir\datatables\src\DB\MySQL.php on line 40

Well, the library is a bit new (january 2015), i wonder if someone that had success using it could help me. Regards

The query builder is failing to compile the query appropriately, or has changed what it returns. Did you try a basic query without the use of functions (like DATE_FORMAT) and aliases? — prodigitalson
yes sir, indeed i tried already without the aliases, the formats and the where, but no result so far — user4934914
maybe i should post here the methods that build the query? it's a third party library called php sql parser that the n1crack's lib uses — user4934914
Yeah, i looked at it already. I would say that if you cannot get even a basic query to run, then i would open a ticket on Datatables or PHPSQLParser... seems somehting is definitely a miss. You can reference this question as well. I suspect the dependency isnt pegged to major version and is using dev-master or version range where something with the API or internals of the parser have changed. — prodigitalson

witchi witchi · Accepted Answer · 2015-06-18T06:24:08

The problem in the WhereBuilder comes from the PHPSQLParser, can you test the latest version from https://github.com/greenlion/PHP-SQL-Parser? It seems to be a simple SQL statement, so actually it should work. I have moved your issue to GitHub, please follow the changes there.

Need advice to make php server side Datatables work properly

1 Answers