Bootstrap Bootgrid with PHP json server side script 37

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

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

boot_grid_sample_pageHere’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.

 

 

37 thoughts on “Bootstrap Bootgrid with PHP json server side script

  1. Reply tony Dec 3,2014 8:32 pm

    Does this work with Word press, do you have a plugin that does this?

    • Reply Tony B. Dec 5,2014 5:03 am

      No sorry no wordPress Plugin, this is really best for standalone PHP apps, but I suppose creating a wordPress plugin shouldnt take too long

  2. Reply paul Dec 16,2014 12:48 am

    Looks like the link to download the complete code isn’t working?

  3. Reply darkshifty Jan 13,2015 2:59 pm

    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!

    • Reply Tony B. Jan 14,2015 7:58 pm

      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)

  4. Reply ywel Feb 2,2015 8:06 am

    if (isset($_REQUEST['rowCount']) ) //Means we're using bootgrid library
    echo "{ \"current\": $current, \"rowCount\":$rows, \"rows\": ".$json.", \"total\": $nRows }";
    what does this line do?

    • Reply darkshifty Feb 2,2015 8:17 am

      Thank you very much, great addition!

    • Reply Tony B. Feb 2,2015 5:35 pm

      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.

  5. Reply kekalen Mar 14,2015 9:24 am

    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

  6. Reply yourock Mar 18,2015 6:56 pm

    THAAAAAAAANKKKKK YOUUUUUUUUUU!!!!!!!!!!!!!!!!!!!!

  7. Reply siva Apr 10,2015 11:49 pm

    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

  8. Reply rioabc May 6,2015 10:21 pm

    Hi Mr. Tony B,

    Does this work with MySQL database ??

    Thanks

    • Reply Tony B. May 16,2015 4:07 pm

      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.

  9. Reply obi May 20,2015 9:48 am

    I just downloaded the code to play with it. But I am not getting data in the html page.. can you please help

  10. Reply Hafees May 21,2015 6:09 am

    $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;
    ?>

  11. Reply eperaza May 28,2015 8:14 am

    how do you implement the command buttons (edit/delete)???

      • Reply Niandou Jan 29,2016 7:49 am

        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”));
        });
        });

  12. Reply Gagan Jun 7,2015 1:30 am

    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.

  13. Reply Gagan Jun 7,2015 1:45 am

    Hi,

    Able to make it work after correcting the error in server.php.

    thanks.

  14. Reply teguh Jun 22,2015 10:28 am

    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

  15. Reply Marcos Sep 9,2015 1:32 pm

    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!

  16. Reply Cristiano Sep 23,2015 3:33 pm

    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

  17. Reply Cristiano Sep 23,2015 3:35 pm

    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

  18. Reply Jakommo Sep 29,2015 5:58 am

    HI

    how would you fix the script to DB for search full movie name instead of the first word?

  19. Reply Jakommo Sep 29,2015 5:59 am

    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

    • Reply Tony B. Oct 12,2015 8:30 pm

      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.

  20. Reply Kenneth Nov 13,2015 1:03 am

    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.

    • Reply Tony B. Nov 15,2015 6:19 pm

      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.

  21. Reply Davy Jan 17,2016 4:40 am

    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?

  22. Reply jose angel Mar 28,2016 1:03 pm

    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

Leave a Reply to paul Cancel Reply