Home > jQuery > Searchdb Plugin

Searchdb Plugin

May 11th, 2007

Searchdb is another jQuery plugin. The reason to write this plugin is that I’m a little be “lazy”. Imagine that you have a dynamic table (grid) or report in wich the user will search on many fields with diffrent criteria (equal, less, greater …). Constructing a form is a first problem. You must write so many tags and html code. The second and more importand problem is that I must analyze too much data passed to the server and construct it in a apropriate way in the WHERE clause of the SELECT statement. So, I think – will be possible to pass only one parameter (the search criteria) , which can be directly passed in the WHERE clause? My answer after little thik was – yes this can be done.

I use again the best JS library jQuery – remember the logo of jQuery – “Write less, do more”. Below is a little guide:

This plugin creates a form of type condition – field value and return a string with a ready to use WHERE clause. Example: “name like ‘tony%’ and rating >= 1″.

As usual the function can be placed in the document ready function to any html element.

$(‘#search1′).searchdb({searchModel: [
{label:'Title',dbname:'message_title',defval:"", dtype:"S", dopt:"cn;eq;lt;le;gt;ge;bw;ew"},
{label:'Autor',dbname: 'author', dtype:"S"}
],
capition: “Search Data Where”,
hideonSearch: false,
disableClose: false,
onSearch: function(s) {
alert(s);
}
});

searchModel: array of values which describes the form elements

  • label The description text of the field
  • dbname the real database name which is used in the where clause
  • size the actual size of the element in the form default 15
  • maxsize the maximal size of the element (maxLength) attribute. Default 15
  • defval the default value of the element when constructing the form. Default empty
  • dtype data type of the element. Values “S” (string) or “N” (numeric). Default is “S”
  • dopt allowed sequence of options for the condition separated with “;”. Here is a list of the possible options:

“eq” is equal,
“ne” is not equal,
“lt” is less
“le” is less or equal
“gt” is greater
“ge” is greater or equal
“bw” is begins with
“ew” is ends with
“cn” is contains

capition: (optional) This is the Title of the form. Default value ‘Search data where’.

hideonSearch: (optional) if set to true the form is hidden after clicking of the button Search. Default is false.

disableClose : (optional) if set to true the button Close (hide) is disabled. Default is false.

onSearch: (optiona) this function is raised after clicking of button Search and accepts a string parameter with a ready to use where clause.

By default when constructing a form the flowing buttons are created:

Search: This button activates onSearch function with a search string parameter.

Clear: This button clears the entered values.

Close: This button hides a form.

Before to call this function you may want to translate some texts in your language. The default language is English.

This can be done via setLanguage function. Example:

var mybuttons = ['Search','Clear','Close' ];
var myodata = ['equal', 'not equal', 'less', 'less or equal','greater','greater or equal', 'begins with','ends with','contains' ];
$.searchdb.setLanguage(mybuttons, myodata);

Tags:
  1. May 12th, 2007 at 18:24 | #1

    I think there’s a serious problem with the idea behind your plugin.
    Mainly because of this: http://en.wikipedia.org/wiki/Model-view-controller

    In any large application, the developer if likely to follow a certain architecture in which data/control/interface are separate. Javascript is a client language used in interface design and it goes against most architectures to use it for the purpose of querying a database.

    Problems:
    1. Whatever is generated by the script will have to be validated by a server-side language
    2. The script will have to be changed if the data structure is changed but interface and data organization should be independent

  2. Tony
    May 14th, 2007 at 20:28 | #2

    Hi Diego,
    I’m not sure what you mean with separation of logic and presentation.
    If you change logic – presentation can be or can not be changed and vice versa.
    It depends on the needs.
    Maybe you will point me that the WHERE script is generated with Javascript and you don not rely on JS to parse a form and pass the query to the
    PHP script that will be processing it.. especially by just using the
    query w/out checking.
    This is not fully true.
    This is solution for my project, and not for all other.
    Remember Web 2.0 is solution.

    Tony

  3. May 16th, 2007 at 04:18 | #3

    Hi,

    is it possible to put up a demo of this?

    Cheers

    Justin

  4. Tony
    May 16th, 2007 at 10:58 | #4

    Hi justin,
    In the example page there is a demo. This demo combines the two plugins – jqGrid and searchdb. With a browser open the source code of the page and look how to configure the searchdb plugin. If you have problems, please let me know.
    Please read the documentation of this plugin – “Continue reading” link in post
    Tony

  5. Svilen Sabev
    June 28th, 2007 at 00:35 | #5

    Zdrasti Tony.
    I will not write on our native language because of the society. i really like your plugin and jquery also. The plugin works great but I have to say that the params generated from js have to be escaped in php. i just tried to simulate error and i succeed.
    Anyway. great job

    Pozdravi

  6. Tony
    June 28th, 2007 at 19:32 | #6

    Zdravej Svilen,

    Thanks for the good words. :)

    I escape only the % char when constructing the string. I think there is no other way since of use of something like:… WHERE field LIKE something% …

    If you have problems try this:
    $whereclause = $_GET['wherestring'];
    urlencode(Strip($whereclause));

    where

    function Strip($value)
    {
    if(get_magic_quotes_gpc() != 0)
    {
    if(is_array($value))
    for($j = 0; $j < sizeof($value); $j++)
    $value[$j] = stripslashes($value[$j]);
    else
    $value = stripslashes($value);
    }
    return $value;
    }

    or simple use only the strip function.

    Pozdravi

  7. Tac
    March 22nd, 2009 at 15:16 | #7

    I’m looking around for the “Continue Reading” link referenced above, to find a demo, but can’t seem to find it.

    I’m also looking for a demo of the more generic solution, the multi-select search, described at

    http://www.secondpersonplural.ca/jqgriddocs/_2hj0z66wb.htm

    Can you post a link to working examples of either of these? Although I’ve read the documentation several times, I’m still not sure how to contruct (or call) a form that will do a multi-select search.

    Thanks!

    Tac

  8. Tac
    March 22nd, 2009 at 16:01 | #8

    It looks like this plugin is not compatible with the latest version of jQuery, it throws an exception (like 996 of jquery-1.3.2.js). The problem is that ‘type’ is now a reserved property in IE, so jquery won’t let it be used:

    if ( name in elem && notxml && !special ) {
    if ( set ){
    // We can’t allow the type property to be changed (since it causes problems in IE)
    if ( name == “type” && jQuery.nodeName( elem, “input” ) && elem.parentNode )
    throw “type property can’t be changed”;

    elem[ name ] = value;
    }

    Tony, can you fix this plugin and then integrate it into one of the demo programs for jqgrid 1.5 alpha? If you get a chance, can you add a “New in 3.5″ section to the documentation, so we can know where to poke around and test? The alpha looks quite nice, as we’re not in production yet using this plugin, I’m tempted to just do our development with the alpha version.

    Thanks again for building these very slick tools.

    Tac

Comments are closed.
Privacy Policy   Terms and Conditions   Contact Information