Datatables php library [new]

numberone

Hi all,

I have made a new php library. Since the library
Ignited DataTables on Forums is outdated, I can not provide any updates for it and needed to use more powerful library. Then I decided to make a new one from scratch.

Hope it helps you. (It is really easy to use, It generates json using only a few lines of code.)

Example ajax file:

<?php
require_once 'vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\MySQL;

$config = [ 'host'     => 'localhost',
            'post'     => '3306',
            'username' => 'homestead',
            'password' => 'secret',
            'database' => 'sakila' ];

$dt = new Datatables( new MySQL($config) );

$dt->query("Select film_id, title, description from film");

echo $dt->generate();  

Links:
- Datatables php library on Packagist
- Datatables php library on Github

Any feedback is welcome.

Regards,
Yusuf

numberone

updated.

-added sqlite db support.

-refactored code.

allan

Very nice! Thanks for sharing this with us :-)

Allan

funstuff234

I think the newest version is broken, when I try to use it mysqli returns a query error. When outputting the query it looks like the library is modifying the syntax to invalid SQL.

numberone

are there any code pieces or sql queries that u can share ?

funstuff234

It doesn't matter what the query is it doesn't work. Mysqli returns boolean false.

EDIT: specific error I get is
"Fatal error: Call to a member function fetch_assoc() on a non-object in /directory/vendor/ozdemir/datatables/src/DB/MySQL.php on line 40"

numberone

It matters. Please share..

funstuff234

Ok. I run a simple query such as "select * from apvendor" and it returns that error. If i modify the library to spit the query back out it is parsed to

"Select * from (select * from apvendor) t1 ORDER BY * asc LIMIT 0, 0 "

numberone

You have to define columns. Do not use wildcards.

try "Select column1, column2, column3 from table1"

funstuff234

I tried "SELECT apvendor.VendorName, apvendor.VendorNumber, apinvoid-apinvprd.LineAmount FROM apvendor" and it returns the same result.

numberone

Thank you. I posted a quick fix to github.

funstuff234

Ok so two things. 1) "apinvoid-apinvprd" is still causing me a problem, I think because of the dash, I think I need to escape it which I'm not sure how to do (In my MySQL program I use backticks which still doesn't seem to work).

2) When I take that out the script runs without errors, however its returning an empty data array! Ex: {"draw":false,"recordsTotal":10569,"recordsFiltered":10569,"data":[]}

These queries run fun in a MySQL client program, so I know its not my databases.

Also I appreciate your help on this.

numberone

I tried this:
$dt->query("Select film_id as `test-test`, title, description from film");

and it is working. I am not sure what is the problem.

funstuff234

Ok so I've been playing with the library for the past hour and I really can't get anything to work. I did notice that there is a problem with capital letters so making everything lowercase does fix some issues. Anyway, here is another example of the input query compared to the query the library generates along with the error the database generates.

My Query:
"Select apvendor.vendorname as venname, vendornumber from apvendor left join apinvoid-apinvprd on apinvoid-apinvprd.vendornumber = apvendor.vendornumber"

Library Query:
"Select venname, vendornumber from (Select apvendor.vendorname as venname, vendornumber from apvendor left join apinvoid-apinvprd on apinvoid-apinvprd.vendornumber = apvendor.vendornumber) t1 ORDER BY venname asc LIMIT 0, 0 "

Error in this particular instance:
"Column 'vendornumber' in field list is ambiguous"

numberone

I'll look into it today.

numberone

I think it is fixed now. Can you try it ?

funstuff234

Thanks so much, everything works!

One thing though, does the library support MySQL functions like SUM()? I tried to use it in my select statements but the library throws a SQL error.

numberone

happy to hear that.

try it with an alias.

Select SUM(column) as total from...
or
Select SUM(column) total from...

funstuff234

That did the trick, you're the best!

funstuff234

So I may have found another bug, it seems that when paging is set to false the library has trouble setting the LIMIT clause?

numberone

it should be fixed atm. Btw thanks for letting me know about these bugs.

funstuff234

Newest version works, thanks!

And no problem, if I find anything else I'll let you know.