I have been a fan of using datatables for a while and always wanted to generalize the server side example to be able to use SQLite, MySQL or any other PDO supported database.
For the longest time in my projects I have used PHP AdoDB as my Database abstraction layer, mostly because it came with a basic paging grid display which I had customized and used in many many projects. But time’s have changed and modern (“ajaxy”)tabular grids like datatables offer a lot more in terms of functionality .
I have a newer related article here on using Bootgrid (Data grid) with PHP ajax server via PDO.
Why DataTables?
Datatables is one of the most useful Jquery plugins because it provides very rich functionality when it comes to displaying tabular data. In its most simplest form, it’s just a few lines of code and it can transform any on-page table into searchable ,filterable table with ton’s of formatting and interaction actions. While being able to manipulate single-page HTML tables is useful for small data-sets,server-side processing is one of its best features.
power of Server Side…
The real power of datatables for large data sets is when you bundle it with a server-side code.Datatables has an example here using PHP and MySQL. The only issue is the example is fairly specific to MySQL and pretty hard-coded to a particular table structure, so my goal was to re-write it using PDO as the base database abstraction and altering the MySQL specific code to be more generic and work with SQLite (As well as MySQL). Below is my explanation of how i did this.
My class serverdatapdo…
My goals were:
- Take existing server side code and generalize it to work with PDO (PHP database abstraction)
- Alter MySql specific SQL so it works with most SQL flavors
- Make it into a SINGLE class /file (not essential but I just like dealing with one file)
- Add some simple logic for it to parse SQL strings into column names, I stress simple logic, I didn’t write a SQL parser.
- Provide timing data for returned query results.
So I bit the bullet and created a a single class (I love simple, single files) , which handles both the client-side rendering and the server-side processing that works with the PDO library and allows you to use just a few lines and generate a complete server-side example.
Usage Steps.
– Using PHP, The example page below makes use of two static classes (meaning you don’t need to instantiate them, just call them like a function) and place this code where the JQUERY (Javascript) would normally go. First fill out $db_Array with SQL (which will be passed to server-side), default table and default index
Client Side:
/* Spell out columns names do not use * as in SELECT * FROM Table */
$db_array=array(
“sql”=>’SELECT movie,year,rating_imdb,genre FROM films‘,
“table”=>’films‘, /* FROM TABLE to use assigned by constructor*/
“idxcol”=>’id’ /* Indexed column (used for fast and accurate table cardinality) */
);
$javascript = ServerDataPDO::build_jquery_datatable($db_array); generates the Jquery Javascript call to the server side. It also builds the SQL string to pass over in a base64encode to the server side. Further on down the page..
$cols=” movie,year,rating_imdb,genre “; //Column names for datatable headings (typically same as sql)
$html =ServerDataPDO::build_html_datatable($cols); This class generates the HTML5 table structure used by Datatables to fill in from the server-side response. You specify column names here that will become your column headings.
<!DOCTYPE html> <html > <head> <title>Sample HTML5 Datatables with PDO SQLite MySQL </title> <link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css" /> <link rel="stylesheet" type="text/css" href="css/style.css" /> <script type="text/javascript" language="javascript" src="js/jquery.js"></script> <script type="text/javascript" language="javascript" src="js/jquery.dataTables.min.js"></script> <?php include "serverdatapdo.php"; // this class handles is both server and client side data //now generate the datatables Jquery with this SQL here: //for added security move this array into to the serverside client $db_array=array( /* Spell out columns names no SELECT * Table */ "sql"=>'SELECT movie,year,rating_imdb,genre FROM films', "table"=>'films', /* DB table to use assigned by constructor*/ "idxcol"=>'id' /* Indexed column (used for fast and accurate table cardinality) */ ); //Custom datatables properties added to Jquery build datatable $javascript = ServerDataPDO::build_jquery_datatable($db_array); echo $javascript; ?> </head> <body> <h2>Sample Datatables with SQLITE Movies & TV Database| MySQL via PDO</h2> <hr> <?php //now generate the HTML databable structure from SQL here: $cols=" movie,year,rating_imdb,genre "; //Column names for datatable headings (typically same as sql) $html =ServerDataPDO::build_html_datatable($cols); echo $html; ?> </body> </html>
Server Side:
– using server side just make sure that the serverdatapdo.php is accessible.
/* Change these to correspond to your database type (dsn) and access credentials, example below uses sqlite w/o pass */
$db_dsn="sqlite:movies.db"; /* corresponds to PDO DSN strings refer to: http://www.php.net/manual/en/pdo.drivers.php */
$db_user=null;
$db_pass=null;
/* Sample MySQL Example -modify as needed
$db_dsn= 'mysql:host=localhost;dbname=testdb';
$db_user = 'username';
$db_pass = 'password';
*/
//When called directly via the Jquery Ajax Source look for this
//SECURITY NOTE: Consider moving this if..block to another file if security is a concern.
if ( isset($_GET['oDb']) ) //is this being called from datatables ajax?
{
//Do we have an object database info (Serialized) if so expand it\\
//echo $_GET['oDb'];
$d=unserialize(base64_decode($_GET['oDb'])); //get the SQL array object NOTE HARDEN this by encrypting
$pdo = new ServerDataPDO($db_dsn,$db_user,$db_pass,$d['sql'],$d['table'],$d['idxcol']);
$result=$pdo->query_datatables(); //now return the JSON Requested data */
echo $result;
exit;
}
couple of performance improvement tips…
There’s a few additional items not in this example that you can take advantage of to improve performance.
- Limit which columns SQL will use in the WHERE clause: By default the sever-side search algorithm will attempt to make all the columns in the SELECT col1, col2,col3 etc.. search able, and expand the SQL to something like this…
SELECT SELECT col1, col2,col3 FROM Table1 WHERE ( col1 LIKE'%search_term'%' OR col2 LIKE'%search_term'%' OR col3 LIKE'%search_term'%' )
Needless to say this is not the most efficient search . So to LIMIT the WHERE clause to a few columns in Datatables Jquery simply use “aoColumns“[ { “bSearchable“: false … making false each column that you do not want in the LiKE
// Using aoColumns $(document).ready( function() { $('#example').dataTable( { "aoColumns": [ { "bSearchable": false }, null, null, null, null ] } ); } );This will only add the columns with the bSearchable:true to the WHERE clause.
Also don’t forget to add a database index to the frequently queried columns, and use EXPLAIN SQL to identify query bottlenecks - Another performance tip (which is included) in this script is to add a key-delay or only issue ajax search requests when certain number of columns are reached. By default the server side search initiates a search request per keystroke entered in the search field, to avoid this refer to the fnSetFilteringDelay datatables plugin which checks for a delay of xxx ms between keystrokes before initiating the ajax request. Here’s the code: (Source: Datatables forums )
Demo
-
Demo SQLite Datatables Movies DB
Click on this link to see is running on a Demo: Sample server-side database of movies in SQLite..
Download Demo Files:
- If there’s interest I can put this up gitHub , but for now here’s the download link.
- Grab your copy of Datatables from the source.
- ZIP Source Code for exampel above (HTML, CSS, and PHP)
- Sample SQLite Database (movies.db 423Kb)
Please feel free to share comments or feedback on this.
Found this helpful? consider a tip for a tip… 
Really useful Tony nice write up
Hi
Very good write up. New to SQLite and MySQL. Have it working well on my own database at my first attempt. How can I fix column widths
Colin
Glad to hear, the real workhorse here is DataTables, give Allan big props for is efforts..
Colin, to fix column widths Change the server PDO that generates the HTML method: build_html_datatable method in serverdatapdo.php just alter the column widths from 100% to no width , which should resize bettter based on column width
There’s a Symfony Bundle as well for an even more advanced Doctrine integration: https://github.com/LanKit/DatatablesBundle
Very good job here!
But how can I make more complex SQL statements?
What do you mean by more complex SQL queries? can you elaborate. You can use column aliases and there is no limitations to joins. the only real limitation is using the SELECT * operator as this requires interrogating the table structure to see what columns to return. But I have used it with some pretty elaborate join queries, just be sure to specify the same returned column names in all spots.
Here’s a quick example (that I’m making up on the spot) of what might be considered a complex? query …
Maybe your approach already handle this, but I’m struggling to see the simple straightforward way to build a series of, let’s say, report pages … or better yet … one generic page that could pull the query string out of a stored database field selected by a $POST … and then produce the table using your approach. Would be cool, no? And complex, sure … but really clean and simple.
So … just to brainstorm here …
I’m imagining that the client-facing PHP page would hold the query string, check for access to see the results, and generate the javascript in html so the user could make use of ajax calls to the server-side processing.
Then a function would need to read the string of the $sql to extract the final column names to be used. They could potentially be identified by regex as the words preceding the commas that are found between “Select” and “From”. Once identified they could be pushed to an array? Then your countColumns and nameColumns functions would work as they do currently.
I’m good at asking questions and thinking creatively about how I’d like to do things, but not sophisticated as a programmer to make it happen. Is this something that you’d consider trying your hand at?
This web page won’d print the example code I’m inserting … so here’s a third try to post it …
$sql = "
select c.column 1, c.column2, c.tres as column3, case when s.hair = "Bald" then "Shine" else s.hair end as column4 from db.counting_table c inner join db.staff_table s on c.numero = s.counting_key where s.Active = 1
";
Ok, Without seeing the actual table structure this will be hard to troubleshoot.
I would make sure to Alias all the columns (which you did) and triple-check that those same aliases are matching in the html
As long as the number of Alieases and their names match the column they shoudl appear in bootgrid.
Hey, just wanted to say that I had been trying to get this to work with mySQL, but could never get it to connect to the DB for some reason. So I switched to SQLite as in your example and everything worked great (converting my DB to SQLite was a chore, but that’s another story). I then added in the TableTools so that users could download the table as needed. Now I just need to monkey around with the CSS to get it styled correctly.
Thanks for the very clear instructions, I was having a really hard time figuring it out from the datatables.net documentation. Your code made it click in my head a little better.
Oh yeah… one thing I did find annoying was that I couldn’t get it to work with View. It only worked with a Table. When I tried it with a View it ran the query, but did not display any records.
Sam, thanks for the input I never tried it with a view that’s strange that it didn’t work, just make sure that you use column aliasing when using views or calculated columns, so that the query returns the column name matching the ones you list in the $cols string..
I did try with MySQL and it should work just the same, the only difference is the connection string, I will see if I can update it with a mySql demo.
You can see the difference in these two pages… Same data. One pulls the view (resulting in Nulls). The other uses the table.
with table:
http://lalato.com/dl/dtables/leagues.php
with view:
http://lalato.com/dl/dtables/leaguesv.php
The view bring back the correct number of rows, but all the data is NULL. very strange.
Sam,
Ok yeah if you do an F12 (chrome browser ) (Network Tab, click on the line for serverpdo.php) you can see the returned JSON is returning all nulls for the columns, so the focus should be on the server side generating the JSON, can you post the SQL string that your using for the view (just change/obfuscate actual column/view/table names for security) …
Do you mean the query that the view runs inside the sqlite db? If so, this is the query.
select l.league_id as league_id, l.league_country as league_country, l.league_region as league_region, l.league_city as league_city, l.league_name as league_name,...'
where l.league_activity_ind = 1
If you mean the query from php, that is this…
SELECT league_country, league_region, league_city, league_site_url, primary_rule_cd, stage_cd FROM lgs_vI think I figured it out. It seems that you have to explicitly name the columns, which I thought I had done, but for whatever reason didn’t work. When I explicitly named the columns to something other than their original names “COUNTRY” instead of the original name of “league_country”, it worked. Anyway, that is very odd behavior around Views in SQLite.
Oh well, at least it works now. I just have to figure out how to get better performance from the view.
Sam,
cool glad to hear it worked, For your database I would recommend MySQL , SQLite is good for smaller single-use databases, it sounds like you really should be running the app from a MySQL Server.. I know you mentioned you had trouble with MySQL, but it should work fine there.
In any case as to improve View performance, don’t forget to add indices to joined columns in the tables, that may help with the view..
Yes, I would like to test it with mySQL to see if it increases performance. Unfortunately, I’m fairly new to this type of stuff and was never able to connect to the mySQL instance. I’ll play around some more with it. I’ll eventually get it to work.
I have a question on how $dbarray used in the code, there you mentioned single table, if I am using joins how I have to do that, Can you suggest me please ?.
Thanking you
Satya
$db_array holds three configuration settings.
-: SQL. is the actual SQL query you want to execute , with all the column names spelled out
– table: The main table that the SQL will be run against..
– idx: The primary (index) that is associated with this column
I downloaded the files and placed them in my xampp / htdocs
https://localhost/sqlite/client.php but it says “Processing”
I have copied movies.db beside my php files, why its not loading the data ??
This is how my files are placed in folder –
https://www.dropbox.com/s/0t7agpd619we3hn/Screenshot%202014-10-28%2011.38.39.png?dl=0
Please tell me what is wrong that i am doing ?
Check all the path’s use the browsers Debug option F12 (in Chrome) click on the network tab and see what XHR (Ajax) requests are taking place, Most likely something is not running properly during the .ajax request to ask the server.php script to render the data..
I cannot get the MySQL connection to work:
https://www.dropbox.com/s/pehnosps1eppvz0/Screenshot%202014-11-04%2011.17.27.png?dl=0
Here are the ServerDataPDO credentials:
public $db=array(
“dsn”=> ‘mysql:host=localhost;dbname=testdb’,
“user”=>’XXXXXX’,
“pass”=>’YYYYYY’,
“conn”=>null,
“sql”=>null,
“table”=>null, /* DB table to use assigned by constructor*/
“idxcol”=>null /* Indexed column (used for fast and accurate table cardinality) */
);
Please help.
Thank you!
to debug try cutting and pasting the ajax line http://server_name/serverdatapdo.php?oDb= form the POST/GET URL into a new browser window and see what output it produces. Debug from the output of the server script (serverdatapdo.php) , if the server side produces valid JSON output then you can look into the client side, but most likely the JSON output is malformed or the server is generating an error.. check that directly first.
i’m having trouble connecting to the database
Database Error!: SQLSTATE[HY000] [1044] Access denied for user ”@’localhost’ to database ‘testdatabase’ Dsn= mysql:host=localhost;dbname=testdatabase
My setting are:
$db_dsn=”mysql:host=localhost;dbname=testdatabase”;
$db_user=”root”;
$db_pass= “”;
public $db=array(
“dsn”=> ‘mysql:host=localhost;dbname=testdatabase’,
“user”=> ‘root’,
“pass”=> ”,
“conn”=>null,
“sql”=>null,
“table”=>null, /* DB table to use assigned by constructor*/
“idxcol”=>null /* Indexed column (used for fast and accurate table cardinality) */
);
Help me please..thanks in advance
First make sure that your mySQL database server is running properly and you have the right access information. I STRONGLY suggest you download http://www.heidisql.com/ and make sure you can connect with that and issue queries with the credentials. Access denied usually means that you have something wrong with your access info, either the server name is wrong or username and password are incorrect. Most instances of MySQL require a password and it cannot be blank… I bet its that straightforward.
Hello. I’ve got the same problem like Saurabh Chadha. client says ‘Processing’.
Tis is returned by serverdatapdo.php with the data of movies.db:
{"sEcho":0,"iTotalRecords":"3393","iTime":0.018000841140747,"iTotalDisplayRecords":"3393","aaData":[["A Few Good Men","1992","7.6","Crime,Drama,Mystery,Thriller"],(...whole data of movies.db...)"]]}Using only this part hard coded without something else works fine in datatables:[["A Few Good Men","1992","7.6","Crime,Drama,Mystery,Thriller"],(...whole data of movies.db...)"]]Can someone give me the right direction? Thank you.This is awesome. But is there any way to get DataTables default search functionality back? I’m referring to the “built-in DataTables filtering which does it word by word on any field”.
Yes, but it will impact performance, check the section A couple of performance tips, and you’ll see that //Using aoColumns was used to disable searching across fields, just adjust that setting
This is very great code for sqlite search,
I would change the state of the result of the last column in one url?
do you have any idea?
example:
Thank you in advance.
Using XAMPP, line 32 says undefined:
Notice: Undefined variable: user in C:\xampp\htdocs\serverdatapdo.php on line 32
Notice: Undefined variable: pass in C:\xampp\htdocs\serverdatapdo.php on line 32
{“sEcho”:1,”iTotalRecords”:”3393″,”iTime”:0.003000020980835,”iTotalDisplayRecords”:”3393″,”aaData”:[[“\”24\””,”2001″,”8.8″,”Action,Crime,Drama,Thriller,Action,Short,Action,Short”],[“\”Alias\””,”2001″,”7.8″,”Action,Adventure,Drama,Mystery,Thriller”],[“\”Angel\””,”1999″,”8.4″,”Action,Drama,Fantasy,Thriller”]
etc…
What do I do about that, it is defined as null above in the code…Two other persons have commented on here with the same issue.
thanks, yep that was it.
Doh! $db_user != $user, changed the code to reflect the same name, viola!
Check the link. It supports both Sqlite and mysql. Easy to use.
https://github.com/n1crack/Datatables
Hello
how to put “<a href=" to last columms
on "public static function build_html_datatable" function
in serverdatapdo.php ?
I would add the url link based on the results of the last column ?
best regard
bravo,
Hi, i was wandering do you have an example of code with additional column like Action in which i could place to buttons for each row, edit and delete.
I tried to add those buttons in your example, but it doesn’t work. I already made update and delete functions in php and tested it with harcoded id and it works, now I want to do that over buttons.
Hi,
I was wandering do you have example with additional column like Actions which will holde edit and delete button for each row. I already made update and delete (on database) to work with you code but i cant add new column od buttons in it like on datatables.net example with inline edit with actions. I also tried to add new row, but without success.
Check out bootgrid example here: http://www.jquery-bootgrid.com/Examples#command-buttons
Hi,
I was wandering do you have example with additional column like Actions which will hold edit and delete button for each row. I already made update and delete (on database) to work with you code but i cant add new column or buttons in it like on datatables.net example with inline edit with actions. I also tried to add new row, but without success.
Here’s a quick example (that I’m making up on the spot) of what might be considered a complex query …
I’m imagining that the client-facing PHP page would hold the query string, check for access to see the results, and generate the javascript in html so the user could make use of ajax calls to the server-side processing.
Then a function would need to read the string of the $sql to extract the final column names to be used. They could potentially be identified by regex as the words preceding the commas that are found between “Select” and “From”. Once identified they could be pushed to an array? Then your countColumns and nameColumns functions would work as they do currently.
I’m good at asking questions and thinking creatively about how I’d like to do things, but not sophisticated as a programmer to make it happen. Is this something that you’d consider trying your hand at?
Grrrr. stripped out my example. Please delete the comment above.
Hi,
I would like to draw the table using data found in column 1 that contains both “Backlog” OR “Stretch”. However, the | doesn’t work. I’ve also tired ||. I’ve tried using an array but that didn’t work either. If I search for just “Backlog” or just “Stretch” it works fine. I’d like it return the results when multiple values are found.
I want to look for both values at the same time.
how do i edit and delete the record