Bending DrasticData (data grid) to filter a query by a given column’s value

Posted: 2010/06/17 in PHP

Problem:  Using DrasticData to ‘automagically’ create an editable table of MySQL data (a data grid).  Using example 9, one can select various tables in a database by using a nice drop-down (included), but there is no way to filter the query to, say, a certain “UserID” (see this post/question/request).

Background:  DrasticData.nl provides a PHP package that, is a PHP / Javascript / MySQL based open source package with an Ajax-based data grid (DrasticGrid), a configurable tag cloud (DrasticCloud) and a Google mapping component (DrasticMap).”  (a quote from their website).  The goal of the project (i.e. why I’m using it) is to easily display/edit a MySQL table.  For example, I want to just put some configuration data (user, password, db, etc.) in a file and have a nice, paged, editable grid of the data.  Learn the setup, then easily create ‘pages’ of table-data in < 1 minute (seriously).

Solution:  There is a document that has a few, very simple instructions – including what to do to ‘subclass’ (extend) the primary class to allow for filtering of data.  Poking around a little, I found what I need to enable the filtering of the data of a given table.  In my case, I want to pick a certain ‘UserID’ and only see the records associated with that UserID (a specific value).

Here’s the details of my situation.  I have a table that has a UserID column (that is NOT the primary key – I have an ID [auto-increment, numeric] column).  I want to see the records that have UserID = 1 (for example).  So here’s the path the user/data takes to get a filtered grid of data.  I am using ExampleGrid9.php as my ‘source’/template.

First, I need a drop-down for UserID (the original in ExampleGrid9.php is for tables).  I replaced the drop-down HTML with the following:

<span style="”font-family:" arial”="">User:</span>
<select id="’username’">
  <option value="’0′">All Users</option>
  <option value="’3′">Aubrey</option>
  <option value="’1′">David</option>
</select>

[[ Note: I added a zero (‘0’) for All Users because I wanted to.  See notes below for my implementation of the 0 value. ]] Next, I altered (replaced) the AJAX/javascript to see ‘username’ and send it (instead of ‘which table’):

var userselector = $('username');
userselector.addEvent('change', function(){rebuild_table()});
var  thegrid = null;
rebuild_table();
function rebuild_table() {
   if (thegrid) $('grid1').empty();
   thegrid = new  drasticGrid('grid1', {
      pathimg:"img/",
      pagelength:10,
      addparams:"&userID="+userselector.value
   });
}

You may notice, if you’re looking at the original, that I am sending a different _GET value (now sending ‘userID’).  Thus, I have to change the file: drasticSrcMySqlExampleGrid9.class.php.  In this file, there is some nice commenting that shows what & where to change.  Searching for ‘$myparameter = $_REQUEST[“myparameter”]‘.  I added the following line (after that set of comments) to take my new value (‘userID’) and use it.  I add the following line:

$userID  = $_REQUEST["userID"];

I’m not a huge fan of $_REQUEST, but it works for today (I’ll go back and clean up the class at a later date).  IMPORTANT! I also removed the line “$this->table = $tablename;” (just below the $_REQUEST line above) because I am not using the Table drop-down.  Instead, I gave the actual table-name in the configuration file.
Now to the good stuff!  Per the documentation, I overwrote (extend) the select() function to use the variable $userID (see the block quote just above).  Here’s what I did (this one’s for you, prasmalla… and for me).
File: ExampleGrid9.php — starting at line 5, replace $src=… with the following:

class byUser  extends drasticSrcMySQL {
   protected function select ($userID=NULL)  {
      if (is_null($userID) || $userID == 0) {
       // select all IF  no user is selected or, in my case, a zero (= All Users)
       // this is  the original <strong>select()</strong> function
         $res =  mysql_query("SELECT * FROM $this->table" . $this->wherestr .  $this->orderbystr, $this->conn) or die(mysql_error());
         return ($res);
      } else {
        // if a given userID is sent, use  it
        // this particular table of mine has a column labeled 'UserID'  that
        // is a numeric field
         res = mysql_query("SELECT *  FROM $this->table WHERE UserID = '$userID'" . $this->orderbystr,  $this->conn) or die(mysql_error());
         return ($res);
      }
   }
}

Basically, if no one is selected or if All Users (‘userID’ = 0) is selected, I simply use the original select function’s query.  If, however, someone IS selected, then it uses their UserID to filter (WHERE clause).

On an aside, I also added the ability to only show specific columns!  I use the $options array (used when instantiating the class), adding a ‘option’ called “cols2show” which is an array of columns that are to be shown – only these and no others.  Find the comment in drasticSrcMySqlExampleGrid9.class.php and add the ternary operator below.

// Get the table in memory
$this->result = $this->select($userID);
$this->num_rows = mysql_num_rows($this->result);
$this->num_fields = mysql_num_fields($this->result);
 //&nbsp; allows one to 'set' which  columns even show up in the datagrid
$this->cols =  ($options['cols2show']) ? $options['cols2show'] : $this->cols;
Advertisements
Comments
  1. B G says:

    Hi,
    I’m very glad I came across this… However I’m having problems getting it to work .. but thats more than likely a problem with my Limited PHP experience !!.

    I’m just wondering if it is possible to have a combination of your User selector .. and a table selector in the one code ie : is it possible to keep the table selector as well – in your code ?

    I’d be very interested in seeing the code of a working example ..

    regards

  2. B G ~

    Does that mean the code worked… somewhat? Feel free to comment on what is getting ‘stuck’ for you and we’ll see if we can work it out.

    Unless the ‘issue’ is that you would like to see multiple tables! For me, I decided to not make the changes for multiple tables for two reasons:
    1) My JavaScript skill just isn’t that great. And I had a limited amount of time to work on this project. So when I ran into some ‘issues’ with one drop-down (ex. Select Table) messing up the other drop-down (ex. Select User), I had to drop the Table drop-down and move on.
    (2) There is a paradigm-assumption that would have to be handled: how would the script know which column of a given Table is the desired Filter (in this case UserID)? That is, each Table would have to have either the identical Column name (e.g. UserID) or, perhaps, the same relative column ‘number’ (ex. 2nd column). It’s probably not hard to think of a number of situations where this ‘problem’ is not a problem (same table used for January, February, …). But then issue #1 above comes into play 🙂

    Regardless, if I get a little time, I’ll definitely go back and see if I can bend the system a little more to get it to work with multiple tables AND have a ‘filter’!

    Bit do let me know if the ‘problems getting it to work’ are something other than multiple tables!

    David

    • bg says:

      Hi David,
      at my first time of writing I ahdn’t got you code working yet.. but now I have.
      My ultimate aim is to combine example9 and your code … which I now gather from your reply above may present some problems. Yes, I would like to have a dropdown to select the table name and then a second dropdown to select a specific instance in one of the colums in that table ( similiar to the user id filter in your example)
      In my case all my tables will be the same format, same columns and same column names – so I gather from your reply above this is a help – makes it less complicated.

      I’m not sure if it is something I’m doing wrong but I can’t seem to get both dropdowns working together – I seem to be having problems getting the second dropdown working – which is my case is the UserID dropdown and I’m unsure of the java script and how to pass both variables from the selectors ( table and user) into the class.
      I’m only starting at java and php so it may be a simple problem but am just wondering if you managed to get anything like this working and if you have and working examples.

      regards
      Brendan

  3. Hans Kuppers says:

    David,

    I am trying to install the DrasticGrid in a WordPress template file.
    I have copied the source file from example9 to a copy of working template.
    After I changed the path-setting of the include files and the JavaScript files there is no grid showing up.
    When I look in firebug I see a error message: syntax error ( in localhost/DrasticTools/js/mootools-1.2-core.js
    In mootools a string with the complete source of the page is decoded as JSON;
    This is the page where all this happens: http://harleydenkers.nl/kosmeoo/drasticgrid1/
    I do not know how to solve this.

    Did you get your code working in WordPress?
    What do I have to do to get it working in WordPress?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s