XAJAX PHP Live Datagrid / Gridview

I’ve come across a lot of these ‘live’ datagrids / gridviews and I haven’t really found one I like. Mainly because they seem to only deal with a small bit of data or the code isn’t portable. So I figured I’d just have to code my own and here’s what I came up with.

I’ll start with the primary or main page of the live datagrid. As you see, it’s fairly simple. Oh and this is probably the time that you should visit http://www.xajaxproject.org/ and familiarize yourself with the XAJAX library. I found it to be very easy to work with dispite the lack of complete documentation. Moving on…

gridview.php

  1. <?php
  2.         require_once(‘dgCommon.php’);
  3. ?>
  4. < !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  5. <html xmlns="http://www.w3.org/1999/xhtml">
  6.         <head>
  7.                 <title>iCE Breakers XAJAX Datagrid</title>
  8.                 <?php
  9.                         $xajax->printJavascript("/javascript/");
  10.                 ?>
  11.         </head>
  12.  
  13.         <body>
  14.                 <center>
  15.                         <div id="dataGrid" align="center"></div>
  16.                         <script type="text/javascript">
  17.                                 xajax_showDataGrid();
  18.                         </script>
  19.                 </center>
  20.         </body>
  21. </html>

Now heres the glue that holds all this XAJAX stuff together and allows it to do it’s thing. Simply, include the XAJAX library and then register a callback function showDataGrid, which should be fairly self-explanatory.

dgCommon.php

  1. <?php
  2.         require_once(‘includes/xajax.inc.php’);
  3.         session_start();
  4.  
  5.         $xajax = new xajax("callbacks/cbDataGrid.php");
  6.         $xajax->registerFunction("showDataGrid");
  7. ?>

Next for the meat, the actual work-horse of the whole live datagrid. This callback function is only able to sort, search and count our rows. I’ve tried to keep this as simple and basic as possible. I use my own home brewed database class, but you can use PEAR’s DB class if you like. You’ll notice I’m using clsDataGrid which I won’t include here since it’s rather long. The clsDataGrid class is basically a class that displays a HTML table. The methods/setters set up CSS styles and define how the datagrid is going to look.

callbacks/cbDataGrid.php

  1. <?php
  2.         require(‘clsDB.php’);
  3.         require(‘clsDataGrid.php’);
  4.         require_once(‘../dgCommon.php’);
  5.  
  6.         $commonDB = new clsDB($GLOBALS[‘DB’]);
  7.  
  8.         /*
  9.         *       getNumRows
  10.         *
  11.         */
  12.         function getNumRows($strColumn=NULL,$strValue=NULL)
  13.         {
  14.                 GLOBAL $commonDB;
  15.  
  16.                 // Possibly use memcache here?
  17.                 $query = "SELECT COUNT(*) AS numrows FROM tblCustomer";
  18.                 if ($commonDB->query($query))
  19.                 {
  20.                         $row = $commonDB->fetchRow();
  21.                 }
  22.                 return $row[‘numrows’];
  23.         } // END getNumRows
  24.  
  25.  
  26.         /**
  27.         *
  28.         *       Primary Callback Function
  29.         *
  30.         */
  31.         function showDataGrid($intStart=0, $intLimit=25, $strOrderCol=NULL, $strSortDir="ASC", $strWhere=NULL)
  32.         {
  33.                 GLOBAL $commonDB;
  34.  
  35.                 $objResponse = new xajaxResponse();
  36.                 $objDataGrid = new clsDataGrid();
  37.  
  38.                 $objDataGrid->setTableName(‘ibDataGrid’);
  39.                 $objDataGrid->setTableStyle(‘style="width:80%; border: 2px solid #C3DAF9; color:#000; padding:0px; margin:0px;" cellspacing=0 cellpadding=0′);
  40.                 $objDataGrid->setHeaderStyle(‘style="text-align: center; font-family: tahoma; font-size: 10px; border:0; margin:0; padding:3px; font-weight:bold; color:#000; background-image:url(images/mso-hd.gif); no-repeat; border-bottom: 1px solid #6593CF;"’);
  41.                 $objDataGrid->setCellStyle(‘style="text-align: left; font-family: tahoma; font-size: 10px; color:#000; border:0; margin:0; padding:3px; border-bottom: 1px solid #DDECFE; border-left: 1px solid #F1EFE2"’);
  42.                 $objDataGrid->setStyleEven(‘style="background: #F5F5F5; border:0; margin:0; padding:0;"’);
  43.                 $objDataGrid->setStyleOdd(‘style="background: #fff; border:0; margin:0; padding:0;"’);
  44.                 $objDataGrid->setNavRowStyle(‘style="font-family: tahoma; font-size: 10px; height: 20px; border:0; margin:0; padding:0px; font-weight:bold; color:#000; background-image:url(images/mso-hd.gif); no-repeat; border-top: 1px solid #6593CF;"’);
  45.  
  46.                 $objDataGrid->setLimit($intLimit);
  47.                 $objDataGrid->setOffset($intStart);
  48.                 $objDataGrid->setOrderCol($strOrderCol);
  49.                 $objDataGrid->setSortDir($strSortDir);
  50.  
  51.                 $cols = array();
  52.                 $cols[‘customer_id’]    = "Customer ID";
  53.                 $cols[‘first_name’]             = "First Name";
  54.                 $cols[‘last_name’]              = "Last Name";
  55.                 $cols[‘email_address’]  = "E-Mail Address";
  56.                 $cols[‘phone_number’]   = "Phone Number";
  57.  
  58.                 $objDataGrid->setDBColumns( array_keys($cols) );
  59.                 $objDataGrid->setColumnHeaders( array_values($cols) );
  60.  
  61.                 $query = ‘SELECT
  62.                                 ‘ . join(‘, ‘,array_keys($cols)) .
  63.                                 FROM
  64.                                         tblCustomer’;
  65.  
  66.                 if ($strWhere != NULL)
  67.                 {
  68.                         $query .= ‘ WHERE ‘ . $strWhere;
  69.                 }
  70.  
  71.                 if ($strOrderCol != NULL)
  72.                 {
  73.                         $query .= ‘ ORDER BY ‘ . $strOrderCol . ‘ ‘ . $strSortDir;
  74.                 }
  75.  
  76.                 $query .= ‘     LIMIT ‘ . $intStart . ‘,’ . $intLimit;
  77.  
  78.                 if ($commonDB->query($query))
  79.                 {
  80.                         while($row = $commonDB->fetchRow())
  81.                         {
  82.                                 $objDataGrid->addRow($row);
  83.                         }
  84.                         $objDataGrid->setTotalRows(getNumRows());
  85.                         $objResponse->assign(‘dataGrid’, "innerHTML", $objDataGrid->renderDataGrid());
  86.                 }
  87.                 else
  88.                 {
  89.                         $objResponse->assign(‘dataGrid’, "innerHTML", "No results found.");
  90.                 }
  91.                 return $objResponse;
  92.         }
  93.         $xajax->processRequest();
  94. ?>

I hope a lot of this code is self explanatory. You don’t have to put your CSS inline like I did. You can use your own CSS files, just as long as you use proper CSS HTML tags in the setters. Update the column names to fit your database scheme. If you use my DB class, you’ll have to update config.php and setup the DB credentials there.

Download the example project here

Example Live Datagrid
Example Live Datagrid

Let me know if you use this in any of your projects.

, , , ,

13 Responses to “XAJAX PHP Live Datagrid / Gridview”

  1. drifter Nov 19, 2008 at 08:17 pm #

    This class is great but is a little overkill in some aspects and short in others. Try the Eyesis Data Grid Control: http://www.eyesis.ca/projects/datagrid.html

    It’s one or two classes and has way more features.

  2. bouton Nov 27, 2007 at 10:39 am #

    I am trying to make it such that the columns can be dynamic and “generify” it and are passed in rather than hard coded as they are in your files. This is what I am trying
    gridview.php

    var cols = null;
    cols = new Array();
    cols['user_ID'] = “User ID”;
    cols['first_name'] = “First Name”;
    cols['last_name'] = “Last Name”;
    xajax_showDataGrid(0,25,”",”DESC”,”",cols);

    This is where I have changed your function so the columns are dynamically processed. (I use ezsql which builds the query on the fly)

    function showDataGrid($intStart=0, $intLimit=25, $strOrderCol=NULL, $strSortDir=”ASC”, $strWhere=NULL.$cols=NULL)
    {
    GLOBAL $commonDB;
    $objResponse = new xajaxResponse();
    $objDataGrid = new clsDataGrid();
    $objDataGrid->setTableName(‘ibDataGrid’);

    $objDataGrid->setSortDir($strSortDir);
    $objDataGrid->setDBColumns( array_keys($cols) );
    $objDataGrid->setColumnHeaders( array_values($cols) );

    $query = I use my own builder here

    if ($commonDB->query($query))
    {
    while($row = $commonDB->fetchRow())
    {
    $objDataGrid->addRow($row);
    }
    $objDataGrid->setTotalRows(getNumRows());
    $objResponse->assign(‘dataGrid’, “innerHTML”, $objDataGrid->renderDataGrid());
    }
    else
    {
    $objResponse->assign(‘dataGrid’, “innerHTML”, “No results found.”);
    }
    return $objResponse;
    }

    This works great. I am able to build the table on the fly fine with whatever columns I pass in via the javascript – UNTIL I sort. Thenit blows up. I see there is an onclick that needs to be edited in classes/clsDataGrid.php but I can’t seem to be able to pass the dynamic cols in.

    Any suggestions?
    Thanks

  3. bouton Nov 27, 2007 at 06:30 am #

    Timothy
    Still no luck with the url. Even with removing the period from the link above. I tried
    http://dev.surveybench.com/iBLivedatagrid/gridview.php which existed – but didn’t work. I have it all working – by far the easiest of the grids I’ve found- but am missing some of the images – so just trying to get them to complete my page. Thanks!

  4. Timothy Nov 26, 2007 at 01:31 pm #

    I moved the example to http://dev.surveybench.com/gridvew.php. Once I implement a few more features and clean up some things here and there, I’ll repackage the whole project. Thanks for all the input and suggestions!

  5. bouton Nov 26, 2007 at 12:28 pm #

    Your download does not include all the images and your example site site does not exists. Other thanthat – looks good.

  6. martinbascal Sep 9, 2007 at 04:16 am #

    Try phaajaxgrid http://freelancesoft.com.ar/phaajaxgrid/ is more simple and easy. Please!!! only a constructor and a config file.

    A PHP library by http://freelancesoft.com.ar that allows you to create html tables from a mysql database ( DataGrid ). Supports sorting, paging, inner joins, left joins right joins, where conditions, drop down lists in cells, customization by css, etc.
    Published in sourceforge…

  7. Timothy Jun 27, 2007 at 06:43 am #

    @dandrade, You most likely just need to remove the leading forward-slash as I have an alias set up for /javascript on my dev server. You can get more help and info from the XAJAX site itself. I must admit, getting XAJAX to work was a bit confusing at first.

  8. dandrade Jun 27, 2007 at 01:59 am #

    Got a few days trying. Always get message:

    Error: The xajax javascript component could not be included. Perhaps the URL is incorrect?

    URL: /javascript/xajax_js/xajax_core.js

    Amazing message “Perhaps” ?

    Besides the URL is OK. So what now?

  9. oly May 12, 2007 at 01:36 pm #

    Don’t take me wrong but I feel that the result html is more than ugly :(

    I haven’t seen the element for a while and most new pages would bail out with the non xhtml-strict doctype.

    It would be much more convenient to have all styles in a css file including the images and the tables/cell styles. So my suggestion is:
    change the inline styles and instead of setting each style within the table use a classname like:

    $objDataGrid->setTableStyle(‘ class=”TableStyle”‘ );
    $objDataGrid->setHeaderStyle(‘ class=”HeaderStyle”‘);
    $objDataGrid->setCellStyle(‘ class=”CellStyle”‘);
    $objDataGrid->setStyleEven(‘ class=”StyleEven”‘);
    $objDataGrid->setStyleOdd(‘ class=”StyleOdd”‘);
    $objDataGrid->setNavRowStyle(‘ class=”NavRowStyle”‘);

    and a css file like:

    .TableStyle {
    width:80%;
    border: 2px solid #C3DAF9;
    color:#000;
    padding:0px;
    margin:0px;/*” cellspacing=0 cellpadding=0′); */
    }
    /* more here .. */

    I had some “trouble” to get your example to run on my webserver.
    There are some images missing on your demo zip file and the xajax you used is not the latest version (and these files are not in your zip file, so it does not work without changing the sourcecode).

    Btw -the flickering is a result of the “poor” html that you write – give your images a width and hight then the flicker is more or less gone.

    But anyway:

    Nice and it shows how ajax works.
    - Thank you :)

  10. lifeofbrian May 10, 2007 at 06:55 pm #

    It looks like under IE7 the images attempt to reload (flicker) when using the paging and sorting controls. Otherwise under Firefox 2 and Safari it looks great!

  11. cms21 May 9, 2007 at 11:21 pm #

    The idea of great number or records its cool but the sort buttons every time i press them take me back to the first page

    Also a suggenstion also idea i’m working on, its to have a select field or check boxes that i mark and the jump to another page it still reminds the record i have selected and when returning to the page keeps them marked
    for late process of them.

    Good table work..!

  12. Timothy May 9, 2007 at 10:27 am #

    Johnnied, I intend on optimizing this a bit. I’m thinking about using JSON and building the table on the client-side of things. There’s plenty of optimizations that can be done, agreed. The idea was to have something that’s quick, simple, and most of all portable. This project is very easy to use in your project.

    Love the idea about a ‘Go To Page’ option. I’ll have to add it! Thanks!

  13. johnnied May 9, 2007 at 08:07 am #

    It would be very nice if we could jump to pages directly (textfield and a button ‘Go to page:’) and if the table would not rebuild itself everytime somethingis pressed. It would be great if only the rows would refresh.

    Great work, keep it up.

Leave a Reply