Support » Plugin: dbview » Interaction with Form Elements added

  • Hi John,
    after some playing with your plugin, i missed the capability to input parameters to a dbview from input elements on a page. Interactivity is what users often want.

    So i looked into your Javascript code and after some refactoring and some additional jQuery magic i finally managed to get something like this working:

    <!-- Basic input of searchtext with Submit-Button -->
    <div>
    <input id="dbviewinputarg1" name="Eingabe" type="text" />
    <button class="dbviewformbutton" type="button">Search</button>
    </div>
    
    <!-- the original unchanged dbview shortcode with initial values for arg1 and arg2 -->
    [dbview name='Volunteerliste2' pagesize=50 sort='user_registered' order='desc' arg1='%' arg2='%']

    On initial startup of the page, the arguments in your ShortCode are taken and the dbview loads. When you then enter some searchtext in the input control and press the Search Button, the parameters are taken from the form an thus some interactive filtering is possible.

    If you are interested in the changes i made, just drop me a line and i’ll be happy to send the changed code over to you.

    IMHO this makes the plugin so much more useful.

    Cheers,
    Joe

    https://wordpress.org/plugins/dbview/

Viewing 15 replies - 1 through 15 (of 15 total)
  • Hi Maiersoft

    Firstly, you rock!
    Secondly, would you mind sharing the code so that i can add a search function to my dbview?

    Cheers,
    Dan

    Thread Starter maiersoft

    (@maiersoft)

    Hi Dan,

    well – the plugin rocks. It’s so well written, that it was quite easy to extend. I would love to see the changes i made being merged into the core. But i don’t know if John is interested in doing so.

    What i would like to do next is to extend the possible number of arguments, because 2 args are too few if you want to do more complex queries, especially because you cannot use one argument multiple times in a query. If you use a parameter twice in a query you have to use 2 args.

    Anyway, here’s the changed code, it’s all in dbview.js. Hope you find it useful. In the markup just add one or two input fields, named ‘dbviewinputarg1’ and ‘dbviewinputarg2’ and a button with class ‘dbviewformbutton’ like i showed in the post above.

    Good luck, let me know if it worked for you,
    Joe

    /*
     * <!-- http://wordpress.org/extend/plugins/dbview -->
     */
    
    jQuery(document).ready(function() {
    
      var messageHolder = jQuery('.messageHolder');  messageHolder.empty(); 
    
      var form = jQuery(".formwrap");
      for (var selector in dbview.tooltips) // hang tooltips off buttons
      {
        dbview.addTooltip(form.find(selector), dbview.tooltips[selector]);
      }
    
      // maiersoft: Changed to use executequery function
      jQuery('.autoload').each(function()  {
        var target = jQuery(this);
        dbview.executequery(target);
      });
    
      // maiersoft: Added to add click-handler to a formbutton with class 'dbviewformbutton'
      //            which calls executequery with the custom arg1
      jQuery('button.dbviewformbutton').on('click', function() {
        var target = jQuery('.autoload');
        var customarg1 = $('#dbviewinputarg1').val();
        var customarg2 = $('#dbviewinputarg2').val();
        if (!customarg2) {
            // if no second argument specified, assume arg2=arg1
            // so arg1 can be used twice in a db-query (e.g. ... (email like %s and username like %s)
    	customarg2=customarg1;
        }
        dbview.executequery(target,customarg1, customarg2);
      });
    
      form.find('input[type=button]').click(function() {
        if (dbview.lockoutButtons != undefined)
          if (dbview.lockoutButtons)
            return ;
        var button = jQuery(this);
        var fields = button.parents('.formwrap').find("input, textarea");
        var ar = fields.serializeArray();
        var verb = button.attr('name')
        if (verb == 'delete' && !confirm("Do you want to delete this view?"))
          return ;
    //    button.attr('disabled','disabled') ;
    
        dbview.busy(null);
        jQuery.ajax({
          url:   dbview.ajaxurl,
          data : 'action=dbview&verb=' + verb + '&' + fields.serialize(),
          success: function(response, textStatus, jqXHR)  { dbview.handleDOMUpdates(response, textStatus, jqXHR, null ); },
          error: function(jqXHR, textStatus, errorThrown) { dbview.handleAjaxError(jqXHR, textStatus, errorThrown, null); } ,
          dataType: 'json'
        });
      });
    });
    
    // maiersoft: Refactored original code and extracted this function, which can be
    //            call with or without customargs
    //            If no customargs are provided, the args from the SchortCode are taken
    dbview.executequery = function(target, customarg1, customarg2) {
       var query = { action: 'dbview', verb: 'autoLoad'} ;
        var attrNames = ['name','pageSize', 'sort', 'order', 'arg1', 'arg2'];
        for (var i = 0 ; i < attrNames.length; i++)
        {
          var attr = target.attr('data-' + attrNames[i].toLowerCase());
          if (attrNames[i]==='arg1' && customarg1) {
            query[attrNames[i]] = customarg1;
    //	console.log('ExecuteQuery: arg1 = ' + customarg1 );
    	}
          else if (attrNames[i]==='arg2' && customarg2)
    	{
            query[attrNames[i]] = customarg2;
    //	console.log('ExecuteQuery: arg2 = ' + customarg2 );
    	}
          else if (attr)
            query[attrNames[i]] = attr ;
        }
        dbview.busy(target);
        jQuery.ajax({
          url:   dbview.ajaxurl,
          data:  query,
          success: function(response, textStatus, jqXHR, dataView) { dbview.handleDOMUpdates(response, textStatus, jqXHR, target );  },
          error: function(jqXHR, textStatus, errorThrown) { dbview.handleAjaxError(jqXHR, textStatus, errorThrown, target); } ,
          dataType: 'json'
        });
      }
    
    dbview.addTooltip = function(elements, text)
    {
      elements.mouseover(function(event) {
        dbview.tooltip1(event, text, true);
      }).mouseout(function(event){
        dbview.tooltip1(event, text, false);
      });
    }
    
    dbview.tooltip1 = function(event, text, display)
    {
      var tooltip = jQuery("body .tooltip7");
    
      if (display == false)
      {
        if (text ==  tooltip.html());
          tooltip.remove();
        return ;
      }
      if (tooltip.length == 0)
        tooltip = jQuery('<div class="tooltip7"></div>').appendTo('body');
    
      tooltip.html(text).css(
          { 'background' : 'lightyellow',
            'border-style' : 'solid',
            'border-color' : 'yellow',
            'border-width' : 3,
            'padding' : 10,
            'position': 'absolute',
            'top': event.pageY + 20, 'left': event.pageX}
          );
    };  
    
    dbview.enableColumnHeaderEditing = function (target)
    {
      for (var selector in dbview.tooltips) // hang tooltips off buttons
      {
        dbview.addTooltip(target.find(selector), dbview.tooltips[selector]);
      }    
    
      var td = target.find('thead tr.columnName th, thead tr.cellFunction th');
      td.click(function() {
        var cell = jQuery(this) ;
        var input = cell.find('textarea');
        if (input.length == 0)
        {
          var cellText = cell.text();
          cell.html("<textarea rows='3' style='width:100%; height:100%;'></textarea>");
          input = cell.children();
          input.val(cellText).focus();
    
          input.focusout(function() {
            cell.empty().text(cellText);
          });
    
          input.change(function () {
            var td = input.parent(); // need to use parents() here
            var tr = td.parent();
            if (tr.hasClass('columnName'))
            {
              dbview.requestCellUpdate(input, td, 'updateColumnName', input.parent().attr('id'), input.val()); return ;
            }
            if (tr.hasClass('cellFunction'))
            {
              dbview.requestCellUpdate(input, td, 'updateCellFunction', input.parent().attr('id'), input.val()); return ;
            }
            alert("No handler for cell of class : " +  tr.attr('class'));
            return true;
          });
        }
      });
      // handle sortable checkbox
      var td = target.find('thead tr.columnSortable th');
      td.click(function() {
      	event.stopPropagation(); // tryingto only have remote updates to chechbox
        var input = jQuery(this).find('input');
        var isChecked = input.is(":checked") ? true : false ;
        dbview.requestCellUpdate(input, input, 'updateColumnSortable', input.attr('id'), isChecked);
        return true;
      });
    
    };
    
    dbview.requestCellUpdate = function (input, target, verb, Id, Text)
    {
      var requestArgs = {
          action : 'dbview',
          nameHidden :  jQuery('.formwrap').find('[name=nameHidden]').val(),
          verb : verb,
          id : Id,
          text : Text,
          _ajax_nonce : jQuery('input[name=_ajax_nonce]').val()
      };
      dbview.busy();
      dbview.lockoutButtons = true ;
      jQuery.ajax({
      //  type:     "POST",
        url:   dbview.ajaxurl,
        data : requestArgs,
        success: function(response, textStatus, jqXHR) { dbview.handleDOMUpdates(response, textStatus, jqXHR, target ); },
        error: function(jqXHR, textStatus, errorThrown) { dbview.handleAjaxError(jqXHR, textStatus, errorThrown, target); } ,
        dataType: 'json'
      });
    };
    
    dbview.busy = function(target)
    {
      var messageHolder = dbview.findClosestRelative(target,'.messageHolder');
      messageHolder.html("<img src='"+dbview.loadingImage+"' />");
    };
    
    dbview.handleAjaxError = function(jqXHR, textStatus, thrownError, target)
    {
      var errorText ;
      if (jqXHR.status != undefined)
        if (jqXHR.status != 200)
      {
        errorText = jqXHR.status + " " + jqXHR.statusText;
      }
      // Server may be returning xml/html but not in JSON, e.g. stack dump
      // ignore JSON parse errors expected in textStatus and thrownError
      else
        errorText = jqXHR.responseText;
      var response = {};
      response.messages = new Array(errorText);
      dbview.handleDOMUpdates(response, "", jqXHR);
    };  
    
    dbview.handleDOMUpdates = function (response, textStatus, jqXHR, target)
    {
      dbview.lockoutButtons = false ;
      var messageHolder = dbview.findClosestRelative(target,'.messageHolder');
      messageHolder.empty();
    //  if (target != null)
      //  target.empty();  
    
      if (response.messages != undefined
          && response.messages instanceof Object
          && response.messages.length > 0)
      {
        var text = ""
        for (x in response.messages)
        {
          var text = text + response.messages[x] + "<br>";
        }
        if (messageHolder.length > 0)
          messageHolder.html(text);
        else
          alert(text);
      }
    
      if (response.updates != undefined && response.updates instanceof Object)
      {
        for (x in response.updates)
        {
          var update = response.updates[x];
          if (update.selector != undefined)  // optional selector
          {
            target = jQuery(update.selector);
            if (target.length == 0)
            {
              alert("Cannot find element '" + update.selector + "' anywhere");
              return ;
            }
          }
          else
          {
            if (!(target instanceof Object))
            {
              alert("Target not an JQuery array"); return ;
            }
            if (target.length == 0)
            {
              alert("Target not specified"); return ;
            }
          }
          if (update.val != undefined)
            target.val(update.val);    // input fields
          if (update.text != undefined)
            target.text(update.text);  // (encoded) div and textarea
          if (update.html != undefined)
            target.html(update.html);
          if (update.checked != undefined)
          {
            if (target.attr('type') != 'checkbox')
              alert('Cannot (un)set checkbox');
             target.prop('checked', update.checked);
          }
          dbview.enableColumnHeaderEditing(target);
          dbview.enableLocalAjaxLinks(target);
        }
        return ;
      }
    };
    
    dbview.enableLocalAjaxLinks = function (target)
    {
      var alink = target.find('a');
      alink.click(function ()
      {
        var query = null ;
        var href = jQuery(this).attr("href");   // href only expected to contain query
        if (href.match('page=dbview') != null)  // hijack links to tools?dbview page and convert them to ajax calls
        {
          query = href.replace('page=dbview', 'action=dbview&verb=handleLink&_ajax_nonce=' +jQuery('input[name=_ajax_nonce]').val());
        }
        if (href.match('action=dbview') != null) // e.g. table navigation request
        {
          query = href ;
        }
        if (query == null)
          return true ;   // browser processes link as normal
    
        dbview.busy(target);
        jQuery.ajax({
          url:   dbview.ajaxurl + query,
          success: function(response, textStatus, jqXHR)
          {
            dbview.handleDOMUpdates(response, textStatus, jqXHR, target);
          },
          dataType: 'json'
        });
        return false ;    // browser ignores this link
      });
    };
    
    dbview.findClosestRelative = function (elements, select)
    {
      var parents = (elements == null) ?  jQuery(document) : elements.parents();
      for (var i=0 ; i < parents.length ; i++)
      {
        var cousins = jQuery(parents[i]).find(select);
        if (cousins.length > 0)
          return cousins.first();
      }
      return jQuery();  // returns an empty set
    };

    Thank you so much! This really helps a lot!

    I seem to be having an issue, after ive added all of the code, my submit button doesnt seem to be working. It’s clickable but nothing happens.

    Any advice?

    Thread Starter maiersoft

    (@maiersoft)

    Have you added the class to the button?
    The jQuery-Code depends on this class to be added to the button.

    <button class="dbviewformbutton" type="button">Search</button>

    If that doesn’t help, look at your Javascript console if there are any errors.

    Thread Starter maiersoft

    (@maiersoft)

    Just in case i wasn’t explicit enough:
    the code i provided replaces the old dbview.js code completely.
    So just remove the old Javascript code from dbview.js and paste my code in.

    Hope this helps,
    Joe

    Plugin Author john ackers

    (@john-ackers)

    Hi maiersoft, thanks for making what is clearly a popular addition! I am happy to merge your changes into the plugin but probably it won’t be until late next week. I should be able to do something about the limited arguments as well.

    Thread Starter maiersoft

    (@maiersoft)

    Hi John,

    great to hear! In the meantime i extended the number of supported sql arguments to 10.
    And i also refactored the javascript part of my solution to now be truly usable in case of multiple arguments.

    An input control can now provide the sql-Parameter value for multiple sql arguments, depending on the the specific requirements of the sql query. The approach now clearly distinguishes between logical input parameters for the query (the input controls of the form), which can then be dispatched to the physical sql parameters (arg1…arg<n>). The input value is dispatched to the specific sql parameters by adding one or multiple dbviewsqlarg<n> dispatcher classes.

    Here’s an example of the markup of the search input form, where input control 1 provides the value for sql args 1 and 3 (by having the class=”dbviewsqlarg1 dbviewsqlarg3″ attribute, whereas input control 2 provides the value for sql arg2 (by having the class=”dbviewsqlarg2″ attribute. You get the idea..

    <div>
    <label for="dbviewinput1">Sql Parameter 1 and 3:</label>
    <input id="dbviewinput1" class="dbviewsqlarg1 dbviewsqlarg3" name="Input1" type="text" placeholder="Enter logical Input 1" />
    <label for="dbviewinput2">Sql Parameter 2:</label>
    <input id="dbviewinput2" class="dbviewsqlarg2 " name="Input2" type="text" placeholder="Enter logical Input 2" />
    </div>
    <div><button class="dbviewformbutton" type="button">Start query</button></div>
    
    [dbview name='MyDbView' pagesize=50 sort='sort column' order='desc' arg1='%' arg2='%' arg3='%' ]

    I will still do a bit of testing and will then send my changed files to you by mail, so you can decide whether this solution is acceptable for you.

    Thanks and best regards,
    Joe

    Thread Starter maiersoft

    (@maiersoft)

    Hi John,

    i’ve tested my additions like described in the post above, and submitted them to your email account.

    Thanks for considering to merge them into the core.

    Cheers,
    Joe

    Plugin Author john ackers

    (@john-ackers)

    Got your email and thanks for testing!

    Hi,

    do I understand this correctly that this is not yet part of the plugin and that the new js code is not posted here? Could I ask you for that code?

    Sorry I’m obviously missing something. I replaced the code in dbview.js. I have the buttons and inputs with the right names / ids. The table displays with the args specified in the shortcode but pressing the button changes nothing.

    (I’m not speaking about the original solution for 2 args not the later one)

    I worked it out. It’s because WordPress default doesn’t like the two $ in the code. Replacing them with jQuery fixed it.

    I would still be interested in the version for more than 2 arguments.

    Thanks for this plugin.

    Plugin Author john ackers

    (@john-ackers)

    maiersoft did send me a set of patches for dbview and they do work. However they would be difficult for me to support. If I updated the plugin and it no longer worked with particular form elements, it would be more tricky to sort out. So I don’t want to integrate into dbview. Instead I want to add a search box(es) that appear at the top of the table when the SQL includes some specific token. It’s obviously not as flexible but easier to set up. But if you email me at the address in header of dbview.php, I’d be happy to forward maiersoft’s email including attachments.

    Thanks for your reply. If that means updates won’t work with the code maiersoft post above then it won’t make a difference because I’m already using that.

    Any chance of adding more than 2 arguments feature?

Viewing 15 replies - 1 through 15 (of 15 total)
  • The topic ‘Interaction with Form Elements added’ is closed to new replies.