I recently came across Jquery Bootgrid, which is Twitter Bootstrap (aka bootstrap) friendly table grid, that is very similar in function to Datatables.While not as feature rich as datatables (yet), it provides a very tiny 4Kb (minified and gzipped) footprint and allows you to leverage all the typography and layout control elements that are present in bootstrap. Because of this, its almost instantly pleasing to the eye with its neatly formatted cell spacing and padding, polished typography and a whole bunch of methods and configuration settings that allow you to display and manipulate tabular data in a modern format.
The basic functionality out of the box, it that it converts any HTML5 table with properly marked up <tbody> <thead> etc. tags into a sortable, searchable data grid. While this is pretty useful, making static HTML (in page) tables come alive, it has attributes in it that allow the table data source to be rendered from an ajax call using a JSON formatted stream , all this makes the HTML markup even cleaner.
The purpose of this post is to provide one PHP implementation of that server side JSON script to allow you to use it with boot grid.
Source code available on GitHub: https://github.com/acbrandao/PHP/tree/master/bootgrid
What is Twitter Bootstrap (aka Bootstrap) & Bootgrid
Twitter bootstrap is a very popular front-end css framework, that combines modern HTML5 , CSS 3 and other elements of front-end web design to help create consistent responsive web pages. Using this framework one can quickly create consistently formatted pages, that use a wide variety of marked up css classes to provide a responsive design.
While plain vanilla Bootstrap provides basic table formatting, it does not offer more rich data grid or database table functionality . That is where Bootgrid comes in. It builds on the design language of the bootstrap table but adds, many more features, when working with tabular data. If you have seen or used Datatables, there may be some similarity, but bootgrid’s focus is on a lean Bootstrap plugin rather than a stand-alone feature-rich jQuery component.
Client Site Code
First setup the web page that will use Jquery , Bootstrap and Bootgrid to provide the HTML view of the server side data. First lets include all the HTML5 , CSS and Javascript dependencies, to do this you can point to the BootStrap CDN as shown here, or copy all the required .js and .css files locally to your web server.
Client side dependencies
- Jquery js/jquery-1.11.1.min.js
- Jquery Boot grid jquery.bootgrid.min.js
- Twitter Bootstrap bootstrap.min.css
You can download all these and then setup up them in your own environment, or simply link them in from a corresponding CDN, check CDNJS.COM for a master list of popular javascript and css library files with CDN links..
Client side code
The web page code below is pretty straight forward, after you setup (include) all the dependant files, then you simply need to spell out the table. This example uses a simple movies database table as its structure. Using the proper class attributes for bootstrap and a unique id=grid-data name for the table, plus the column headings that you want to display, you can have a dynamically generated table appear.
Notice the data-ajax and data-url attributes inside the table tag these tell boot-grid that the data source is coming from outside the page. For a more complete description of bootgrid’s methods, check out its own documentation.
<!--define the table using the proper table tags, leaving the tbody tag empty --> <table id="grid-data" class="table table-condensed table-hover table-striped" data-toggle="bootgrid" data-ajax="true" data-url="server.php"> <thead> <tr> <th data-column-id="id" data-type="numeric" data-identifier="true">id</th> <th data-column-id="movie">Movie</th> <th data-column-id="year" data-type="numeric">year</th> <th data-column-id="genre">Genre</th> <th data-column-id="rating_imdb" data-type="numeric">Rating</th> </tr> </thead> </table>
Once this is defined, then somewhere on your page, be sure to instantiate the grid with the command below.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Bootgrid Sample Template</title>
<!-- Bootstrap CSS-->
<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="js/jquery.bootgrid.css" rel="stylesheet">
</head>
<body>
<h2> My Bootgrid Implementation </h2>
for more info about <A href="http://www.jquery-bootgrid.com/">bootgrid</a>
<!--define the table using the proper table tags, leaving the tbody tag empty -->
<table id="grid-data" class="table table-condensed table-hover table-striped"
data-toggle="bootgrid" data-ajax="true" data-url="server.php">
<thead>
<tr>
<th data-column-id="id" data-type="numeric" data-identifier="true">id</th>
<th data-column-id="movie">Movie</th>
<th data-column-id="year" data-type="numeric">year</th>
<th data-column-id="genre">Genre</th>
<th data-column-id="rating_imdb" data-type="numeric">Rating</th>
</tr>
</thead>
</table>
<!-- jQuery (necessary for Bootstrap's JavaScript plugins and Bootgrid) -->
<script src="js/jquery-1.11.1.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="js/bootstrap.min.js"></script>
<!-- Include bootgrid plugin (below), -->
<script src="js/jquery.bootgrid.min.js"></script>
<!-- now write the script specific for this grid -->
<script language="javascript">
//Refer to http://jquery-bootgrid.com/Documentation for methods, events and settings
//load gird on page\e load...
$("#grid-data").bootgrid(
{
caseSensitive:false /* make search case insensitive */
});
</script>
</body>
</html>
PHP server side ajax json
Much like I described in my Datatables server side post. I again turn to PHP to provide the server side database read layer and to allow me to read from a database (SQLite in this example) and then render that data out as JSON ,in a format friendly to bootgrid. The database I will be using will be a simple one-table movies database stored in a SQLite 3 formatted file .
Unlike my datatables PHP example, I did not try to make the server script generic, this time I kept the server side simpler, opting for a simple procedural script that can be easily extended. Here’s the outline of the steps.
- uses PHP PDO as the database layer to connect to the database
- setups various QueryString checks to see if the incoming request has them, this is what Bootgrid issues when it creates an Ajax request for paging information, sorting, etc.
- Substitute the search Phrase into the SQL command
- Execute the query and return the results as a JSON string
- finally add some extra metadata about number of records and paging information to help Bootgrid display the grid properly
Important Note: This is just for a proof of concept script and I strongly encourage if you’re going to use this as a basis of a production system, you tighten up the security especially of the SQL component. With that said here’s the PHP script.
<?php
try {
// $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); //MYSQL database
$conn = new PDO("sqlite:db/movies.db"); // SQLite Database
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$where =" 1=1 ";
$order_by="rating_imdb";
$rows=25;
$current=1;
$limit_l=($current * $rows) - ($rows);
$limit_h=$limit_lower + $rows ;
//Handles Sort querystring sent from Bootgrid
if (isset($_REQUEST['sort']) && is_array($_REQUEST['sort']) )
{
$order_by="";
foreach($_REQUEST['sort'] as $key=> $value)
$order_by.=" $key $value";
}
//Handles search querystring sent from Bootgrid
if (isset($_REQUEST['searchPhrase']) )
{
$search=trim($_REQUEST['searchPhrase']);
$where.= " AND ( movie LIKE '".$search."%' OR year LIKE '".$search."%' OR genre LIKE '".$search."%' ) ";
}
//Handles determines where in the paging count this result set falls in
if (isset($_REQUEST['rowCount']) )
$rows=$_REQUEST['rowCount'];
//calculate the low and high limits for the SQL LIMIT x,y clause
if (isset($_REQUEST['current']) )
{
$current=$_REQUEST['current'];
$limit_l=($current * $rows) - ($rows);
$limit_h=$rows ;
}
if ($rows==-1)
$limit=""; //no limit
else
$limit=" LIMIT $limit_l,$limit_h ";
//NOTE: No security here please beef this up using a prepared statement - as is this is prone to SQL injection.
$sql="SELECT id, replace(movie,'\"','' ) as movie, year, rating_imdb,genre FROM films WHERE $where ORDER BY $order_by $limit";
$stmt=$conn->prepare($sql);
$stmt->execute();
$results_array=$stmt->fetchAll(PDO::FETCH_ASSOC);
$json=json_encode( $results_array );</pre>
<pre> /* specific search then how many match */
$nRows=$conn->query("SELECT count(*) FROM films WHERE $where")->fetchColumn();
header('Content-Type: application/json'); //tell the broswer JSON is coming
if (isset($_REQUEST['rowCount']) ) //Means we're using bootgrid library
echo "{ \"current\": $current, \"rowCount\":$rows, \"rows\": ".$json.", \"total\": $nRows }";
else
echo $json; //Just plain vanillat JSON output
exit;
}
catch(PDOException $e) {
echo 'SQL PDO ERROR: ' . $e->getMessage();
}
?>
Some considerations..
One immediate consideration is related to the search performance of the server script, because every keystroke initiates a server search (ajax request) this could quickly overwhelm a server on a busy site, or one with a large database. Also the server side (in this case) searches across multiple columns (in this example) , it will impact your performance substantially if this table is of any real size. Since our example table only has around ~3300 records, its hard to simulate performance issues, which may not be apparent.
A better approach is to only begin issuing ajax requests after n-characters (string length) are typed into the search box. Also limit the search on the server to the most relevant fields and avoid using LIKE %search_term% whenever possible , finally and be sure to have those columns indexed properly.
Also a very nice feature of Bootgrid is that it maintains state and handles SORTING internally for AJAX completed requests. This is very nice if you have large results sets as sorting can be done intelligently (it may need to fetch from the server), but it makes for snappy performance.. Try this search for same “drama” then click on one of the columns to sort.. notice how quick and efficient.
Demo
Here’s a link to a short demo of the bootgrid PHP server system.. and you can compare this against my datatables demo.
For comparison purposes take a look at my earlier PHP PDO datatables sample
Download
You can download all the core files here, but I recommend you, update the links to the dependant files(Bootstrap, Bootgrid and Jquery ) to ensure you’re getting the latest stable packages.
- Download complete code, php and database file (Sqlite) of Bootgrid PHP server (bootgrid.zip 365Kb)
- If you find this useful, share your love by donating a beer (via PayPal link) to the author of Bootgrid and for that matter to the other contributors to the Jquery and Bootstrap eco-system. I encourage you to share this link.
- Source code also available on GitHub: https://github.com/acbrandao/PHP/tree/master/bootgrid
Does this work with Word press, do you have a plugin that does this?
No sorry no wordPress Plugin, this is really best for standalone PHP apps, but I suppose creating a wordPress plugin shouldnt take too long
Looks like the link to download the complete code isn’t working?
Paul,
Thanks I just fixed the broken link, just revisit the page and the download link should work now.
looks and works great !, im not good with jquery though. is there an easy way to make lets say a movie clickable to a url in example star trek with the url profile.php?s=star trek
With thanks!
Yep,bootgrid supports formatters to format the output of columns before they are rendered..
For example: in the html where you define the header use data-formatter=”link”:
.. data-column-id=”link” data-formatter=”link” data-sortable=”false” Link …
Then in the same HTML Page inside the jquery code you create the formatter like:
formatters:
“link”: function(column, row)
(see second example: http://www.jquery-bootgrid.com/Examples)
if (isset($_REQUEST['rowCount']) ) //Means we're using bootgrid librarywhat does this line do?echo "{ \"current\": $current, \"rowCount\":$rows, \"rows\": ".$json.", \"total\": $nRows }";
Thank you very much, great addition!
That line means if we have a valid rowCount (ie. some records returned) we want to echo out the JSON string in the proper format that Bootgrid understands. In this example it adjusts the current (what row # am I serving) out of how many nRows there are. This is what allows Bootgrid to keep track of paging.
sorry for my poor english… how could i get the ‘id’ in ur example using :
“link”: function(column, row)
and parse/disply the row of data on modal bootstrap?
thx a lot and i’ve read ‘http://www.jquery-bootgrid.com/Examples’, and i can’t understand… T.T
THAAAAAAAANKKKKK YOUUUUUUUUUU!!!!!!!!!!!!!!!!!!!!
hi.. its really good.. and its work good in my localhost… but in my host result not showed to me… is it work table contain more then 30lack entries
Hi Mr. Tony B,
Does this work with MySQL database ??
Thanks
It should work just the same with MySQL , just change the string in the PHP PDO line, the connection string is different for each database.
I just downloaded the code to play with it. But I am not getting data in the html page.. can you please help
$value)
$order_by.=” $key $value”;
}
//Handles search querystring sent from Bootgrid
if (isset($_REQUEST[‘searchPhrase’]) )
{
$search=trim($_REQUEST[‘searchPhrase’]);
$where.= ” AND ( category_title LIKE ‘”.$search.”%’ OR category_id LIKE ‘”.$search.”%’ OR cat_slug LIKE ‘”.$search.”%’ ) “;
}
//Handles determines where in the paging count this result set falls in
if (isset($_REQUEST[‘rowCount’]) )
$rows=$_REQUEST[‘rowCount’];
//calculate the low and high limits for the SQL LIMIT x,y clause
if (isset($_REQUEST[‘current’]) )
{
$current=$_REQUEST[‘current’];
$limit_l=($current * $rows) – ($rows);
$limit_h=$rows ;
}
if ($rows==-1)
$limit=””; //no limit
else
$limit=” LIMIT $limit_l,$limit_h “;
//get the fields from table
$sql=”SELECT `category_id`, `category_title`, `cat_slug`, `category_parent_id` FROM category WHERE $where ORDER BY $order_by $limit”;//
$re=mysqli_query($conn,$sql);
//fetch the all data into a associative array
$results_array=mysqli_fetch_all($re,MYSQLI_ASSOC);
$json=json_encode( $results_array );
$nRows=mysqli_num_rows($re);
header(‘Content-Type: application/json’); //tell the broswer JSON is coming
if (isset($_REQUEST[‘rowCount’]) ) //Means we’re using bootgrid library
echo “{ \”current\”: $current, \”rowCount\”:$rows, \”rows\”: “.$json.”, \”total\”: $nRows }”;
else
echo $json; //Just plain vanillat JSON output
exit;
?>
how do you implement the command buttons (edit/delete)???
http://www.jquery-bootgrid.com/Examples#command-buttons
id
Movie
year
Genre
Rating
Commands
***********************
var grid = $(“#grid-command-buttons”).bootgrid({
ajax: true,
post: function ()
{
return {
id: “b0df282a-0d67-40e5-8558-c9e93b7befed”
};
},
url: “/server.php”,
formatters: {
“commands”: function(column, row)
{
return ” ” +
“”;
}
}
}).on(“loaded.rs.jquery.bootgrid”, function()
{
/* Executes after data is loaded and rendered */
grid.find(“.command-edit”).on(“click”, function(e)
{
alert(“You pressed edit on row: ” + $(this).data(“row-id”));
}).end().find(“.command-delete”).on(“click”, function(e)
{
alert(“You pressed delete on row: ” + $(this).data(“row-id”));
});
});
Hi,
I have downloaded your example,but its not displaying any data on page.
i got error in server.php for $limit_lower not defined.
Hi,
Able to make it work after correcting the error in server.php.
thanks.
Thanks if possible please post the correction , so others can make use of it..
Hi, Tony
Thanks for the article and Nice results , and I have a question and I need a clue for making ID on bootgrid as people did with another plug in , I try by myself but no luck , thanks in advance
Ther’s a little mistake on the code:
$limit_l=($current * $rows) – ($rows);
$limit_h=$limit_lower + $rows ;
The variable $limit_l in the next line is call $limit_lower. You have to cahnge it to $limit_l.
That will fix the problem!
thanks appreciate it.
Estou com problemas na paginação…
-Primeira pagina exibe corretamente 10 registros, informando exibição de 1 a 10
-Quando clico no botão “2” da paginação, ele mostra 20 registros, informando exibição de 11 a 20
-Se apertar o botão “>” para avançar a próxima página, ele salta muitas páginas…
Obs. A medida que vou apertando o botão 2,3,4,5… ele vai aumentando automaticamente o numero de registros exibidos na tela
translation
I’m having problems paging …
-First Page displays 10 records properly informing display 1-10
‘When I click the “2” button paging , it shows 20 records , informing display 11-20
If press the ” >” button to move to the next page, he jumps many pages …
Note . As will by pressing the button 2,3,4,5 … it will automatically increase the number of records displayed on the screen
Cristiano
Please take a look at the Bootgrid settings here: http://www.jquery-bootgrid.com/Documentation#table try adjusting the rowcount values, you need to be sure that row count is properly being sent to the server so it know where it ended and where it needs to begin
FYI Eu so também português (minho)
HI
how would you fix the script to DB for search full movie name instead of the first word?
HI
how would you fix the script to search full movie name in DB instead of the first word?
try it out with any movie name
Try changing this code :
movie LIKE ‘”.$search.”%’
to
movie LIKE ‘%”.$search.”%’
The reason it was originally searching just for the first word, is because there’s a performance penalty in SQL when yo do wild card %word% matches as the SQL execution planner than needs to do more complex string matches.
Thanks for the great bootgrid implementation. I’ve successfully implemented it onto my site and have altered the code a bit to allow for searching across multiple columns using keywords. It works great, however, I’ve noticed that if I type into the search box repeatedly over several seconds (say 30 or more), eventually the entire server stops responding and I receive a “could not load resource” and “connection lost” error. It takes roughly 30 more seconds for the server to come back online. Do you have any thoughts on as to what the problem could be. It seems like I’m overloading the server, but CPU and memory usage don’t see excessively high, but maybe I’m not looking in the right place. The database I’m using is less than 1K rows.
Kenneth, yeah the issue is likely due to too many requests saturating the web server (network) remember the response is usually a full JSON result set. also maybe the sql server is overwhelmed. .. this is why its advisable to only submit requests once the ENTER key is pressed or after n number of character or after a delay between some number of seconds.. . Because each keystroke is a new request and it your web/and or db server is slow or on a slow connection it will eventually bog down. There is some code on the web to handle the keystroke delay check it out.
Hi Tony. I’ve adopted your scripts so it shows more details about a movie. I’m having a problem now with querying special characters from MySQL. When calling
http://mysite/movielist/get-all-movies.php?current=1&rowCount=1000&searchPhrase=spectre, the Actors column has special chars in it:{ "current": 1, "rowCount":1000, "rows": [{"id":"tt2379713","movie":"Spectre","year":"2015","genre":"Action, Adventure, Thriller","rating":"7.0","votes":"161,439","runtime":"148 min","added":"2016-01-17","collected_jin":"","poster":"\/mSvpKOWbyFtLro9BjfEGqUw5dXE.jpg","fanart":"\/wVTYlkKPKrljJfugXN7UlLNjtuJ.jpg","tagline":"","plot":"A cryptic message from Bond's past sends him on a trail to uncover a sinister organization. While M battles political forces to keep the secret service alive, Bond peels back the layers of deceit to reveal the terrible truth behind SPECTRE.","director":"Sam Mendes","actors":null,"trailer":"http:\/\/youtube.com\/watch?v=7GqClqvlObY"}], "total": 1 }MySQL has them nicely store in the DBd, but I can’t figure out how to output them via your script … Any help?Found it!
In your server.php script, immediately after making the connection I added this:
// DB collocation is in UTF8 ...$sql="SET CHARACTER SET utf8";
$stmt=$conn->prepare($sql);
$stmt->execute();
Found a very nice hint here: Stackoverflow
🙂
Thanks for sharing!
Hi Tony B. This example is awesome and i wonder if you can help me, i try to do a system whit datagrid using you’r example but to the moment to see i can’t see the content of the database on the table nothing happen, i try to create a new table in database movies.db but it’s still equal, i’m working with sqlite2 and botstrap 3.3.0 for you’r time THAKS YOU