autosuggest/complete & search example

[pietera99]

We all agree jtable is an amazing plug-in. The only thing that lacked for me is a column search and autosuggest/complete, so I decided to add this myself.

WARNING: the code below acts as an example, if you don't have a good portion of experience with programming in jquery, php and mySQL, then do not proceed, I won't provide support on the example code below, you're on your own, use at your own risk.

Here's what I've done to modify jtable to have a fully functional autocomplete+search option for listview columns. jq-UI provides an autocomplete for your input boxes. I've used this as the base for creating a nice working autocomplete+search function for jtable. Programming is somewhat quick and dirty, but what the hack, it works!

This one is for jtable 1.7.2. Scroll down and you'll find one for jtable 2.2.1.

In jquery.jtable.js

FIND:

    /* Creates a header cell for given field.
    *  Returns th jQuery object.
    *************************************************************************/
    _createHeaderCellForField: function (fieldName, field) {
        field.width = field.width || '10%';
        return $('<th class="jtable-column-header" style="width:' + field.width + '">' +
            '<div class="jtable-column-header-container"><span class="jtable-column-header-text">' + field.title +
            '</span></div></th>')
            .data('fieldName', fieldName);
    },

REPLACE WITH:

    /* Creates a header cell for given field.
    *  Returns th jQuery object.
    *************************************************************************/
    _createHeaderCellForField: function (fieldName, field) {
        field.width = field.width || '10%';
        var ss = '';
        if (field.sorting == false && field.search == true) { 
            ss = '<th class="jtable-column-header" style="width:' + field.width + '">' +
            '<div class="jtable-column-header-container"><span class="jtable-column-header-text">' + field.title +
            '</span> <img src="img/icons/magnifier_zoom_in.png" onclick="tlg(this)"><span class="findbox"><input onKeyUp="ac(this)" onkeydown="menter(event, this)" type="text" size="15" name="' + fieldName + 
            '" id="' + fieldName + '" value="" /></span></div></th>';
        } else { 
            ss = '<th class="jtable-column-header" style="width:' + field.width + '">' +
            '<div class="jtable-column-header-container"><span class="jtable-column-header-text">' + field.title +
            '</span></div></th>';
        }

        return $(ss).data('fieldName', fieldName);
    },

FIND:

    options: {
        sorting: false,
        defaultSorting: ''
    },

REPLACE WITH:

    options: {
        search: false,
        sorting: false,
        defaultSorting: ''
    },

In your icon-image directory put a nice small looking-glass icon and point to above: img src="img/icons/magnifier_zoom_in.png" or change the img filename accordingly.

        

In index.php inside your head script tags ADD THIS:

//jq-ui autocomplete loader.php?q=search&what=input-id&term=typed-value 

function ac(what) {
    $( "#" + what.id ).autocomplete({
      source: "loader.php?q=search&what=" + what.id,
      minLength: 2
    });      
}   

//toggle findbox   
function tlg($handle) {
    $( '.findbox' ).toggle(); 
    $($handle).next().find('input').focus();    //set the focus to the inputbox, important!
}

//if ENTER is pressed  
function menter(e,d) {
    if(e.keyCode==13) { 

        if (d.id == 'name') custman(d);

    }
}

In index.php inside your head style tags ADD THIS:

.findbox {
    display: none;
}

In the jTable template inside your code you'll have something like the code below. Watch how you have to append '&term=' to your listAction followed by the search-term. This extra GET-var is going to be send to the server:

//jtable customer management template
function custman(d) {  
    //Prepare jTable
    $('#innerContainer').jtable('destroy');
    $('#innerContainer').html('').hide();

    $('#innerContainer').jtable({
        title: 'Klanten',
        saveUserPreferences: false,
        sorting: true,
        paging: true,
        actions: {
            listAction: 'loader.php?action=culi&term=' + d.value,
            createAction: 'loader.php?action=cucr',
            updateAction: 'loader.php?action=cuup'
        },
        fields: {
            custid: {
                key: true,
                create: false,
                edit: false,
                list: false
            },
            name: {
                title: 'Naam',
                width: '40%',
                sorting: false,
                search: true
            },
            address: {
                title: 'Adres',
                list: false
            },
            zip: {
                title: 'Postcode',
                list: false
            },
            city: {
                title: 'Plaats',
                width: '30%',
                sorting: true
            },
            prov: {
                title: 'Provincie',
                list: false
            },
            country: {
                title: 'Land',
                list: false
            },
            tel1: {
                title: 'Tel1',
                list: false
            },
            tel2: {
                title: 'Tel2',
                list: false
            },
            fax: {
                title: 'Fax',
                list: false
            },
            email: {
                title: 'Email',
                list: false
            },
            ts: {
                title: 'Timestamp',
                width: '30%',
                create: false,
                edit: false
            }
        }
    });

    //Load test list from server
    $('#innerContainer').jtable('load').fadeIn('slow');

}

Now watch: in the example above for the jTable template you have an extra option called 'search' that you can use to enable your column autocomplete and search. Just remember that sorting MUST be false AND search MUST be true to enable the autocomplete-search within your jtable. You can now see the small search-icon in your column. Click on it, and the search-box appears. Type at least two charachters and you'll get the autocomplete suggestion. Press ENTER to narrow down your listview. (this actually sends an XHR-call to your server for the same listview but the result is narrowed down by your entry using a WHERE LIKE statement).

At the server-side proceed as follows:

In loader.php ADD your XHR-rpc function something like this example

// search and autocomplete for the webapp - uses other column-names
function autocomplete($what, $val) {
    //  execute the query based on $what you are looking for  
    switch ($what) {       
        case 'sitename' : $items = fetchSqlArray("SELECT sitename FROM sites WHERE sitename LIKE '%".$val."%' GROUP BY sitename LIMIT 10 ;"); break;      
        case 'name' : $items = fetchSqlArray("SELECT name FROM customers WHERE enabled = 1 AND name LIKE '%".$val."%' GROUP BY name LIMIT 10 ;"); break;     
        //when the WHERE-statement is different from the CASE-statement, correct the $what accordingly
        case 'assignedto' : $what = 'uname'; $items = fetchSqlArray("SELECT uname FROM users WHERE enabled = 1 AND uname LIKE '%".$val."%' GROUP BY uname LIMIT 10 ;"); break;
        case 'uname' : $items = fetchSqlArray("SELECT uname FROM users WHERE enabled = 1 AND uname LIKE '%".$val."%' GROUP BY uname LIMIT 10 ;"); break;     
        case 'ourref' : $items = fetchSqlArray("SELECT ourref FROM orders WHERE finished = 0 AND ourref LIKE '%".$val."%' GROUP BY ourref LIMIT 10 ;"); break; 
    }
    // serialize all entries found into an object-formatted list, sadfully json_encode doesn't work for this
    $myarr = Array();
    foreach($items as $i) $myarr[] = chr(34).$i[$what].chr(34);
    $list = chr(91).implode(chr(44),$myarr).chr(93);

    //logthis("Search for '".$val."' results in: ".$list ); 
    return $list;  //object
} 

## Quote variable to make safe for mySQL
function quote_smart($value) {
   // if magic_quotes are set, slashes may already been added, so strip them first
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value) || $value[0] == '0') {
       $value = chr(39) . mysql_real_escape_string($value) . chr(39);
   }
   return $value;
}

// for all the GET[q] variables, perform a switch-case
$reply = '';
$request =  $_GET['q'];
// catch the q=xyz request  
switch($request)  {

    case 'search' : $reply = autocomplete( $_GET['what'], $_GET['term'] ); break; 

}
echo $reply;

Here you'll have the GET-var we talked about earlier in your jTable list-calls called $_GET['term'] that holds the search-term. You can use this search term to narrow down your listview with a subselector. Use this subselector to make a WHERE LIKE, like this:

//Getting records for customer management
else if($_GET["action"] == "culi")
{

    //Get records from database, in principal, everyone may list the customers             
        $mq = "SELECT * FROM customers WHERE enabled = 1 ";
        if ($_GET["term"]!='undefined' && $_GET["term"]!='' && strlen($_GET["term"]) >1) $mq .= " AND name LIKE ". quote_smart( chr(37) . $_GET["term"] . chr(37) );        
        $rc = mysql_num_rows(mysql_query($mq));
        if (isset($_GET["jtSorting"]) && ($_GET["jtSorting"]!='')) $mq .= " ORDER BY ". $_GET["jtSorting"];
        if (isset($_GET["jtStartIndex"]) && ($_GET["jtStartIndex"]!='')) $mq .= " LIMIT " . $_GET["jtStartIndex"];
        if (isset($_GET["jtPageSize"]) && ($_GET["jtPageSize"]!='')) $mq .= " , " . $_GET["jtPageSize"];
        $result = mysql_query($mq);

        //Add all records to an array
        $rows = array();
        while($row = mysql_fetch_assoc($result)) {

            //fill the next element within array
            $rows[] = $row;                     
        }

        //Return result to jTable
        $jTableResult = array();
        $jTableResult['Result'] = "OK";
        $jTableResult['TotalRecordCount'] = $rc;
        $jTableResult['Records'] = $rows;

        //logthis("Fetch list of customers");

        return json_encode($jTableResult);
}       

All you have to do, is modify your list-actions according to the example above. There's no need for modifying your Create, Update en Delete actions.

Have fun!

[hikalkan]

Thank you very much. I'll examine your code when I have time to do.

[pietera99]

It's the least I can do in return for your wonderfull jtable plug-in :)

[KR4L3X]

cool, do you have a demo we could view? I am looking for to replace the add record with a search records and have the same style popup with search options

[pietera99]

Here are some screenshots:

The default view

When clicking on the search-icon

After typing two or more chars

When ENTER is pressed the final search results

[pietera99]

BTW, I have it working now on jTable2.2.1 now

In jquery.jtable.js

FIND:

    /* Creates a header cell for given field.
    *  Returns th jQuery object.
    *************************************************************************/
    _createHeaderCellForField: function (fieldName, field) {
        field.width = field.width || '10%'; //default column width: 10%.

        var $headerTextSpan = $('<span />')
                .addClass('jtable-column-header-text')
                .html(field.title);

        var $headerContainerDiv = $('<div />')
                .addClass('jtable-column-header-container')
                .append($headerTextSpan);

        var $th = $('<th></th>')
                .addClass('jtable-column-header')
                .css('width', field.width)
                .data('fieldName', fieldName)
                .append($headerContainerDiv);           

        return $th;
    },

REPLACE WITH:

    /* Creates a header cell for given field.
    *  Returns th jQuery object.
    *************************************************************************/
    _createHeaderCellForField: function (fieldName, field) {
        field.width = field.width || '10%'; //default column width: 10%.

        var ss = '';
        if (field.sorting == false && field.search == true) {
            ss = ' <img src="img/icons/magnifier_zoom_in.png" onclick="tlg(this)"><span class="findbox"><input onKeyUp="ac(this)" onkeydown="menter(event, this)" type="text" size="7" name="' + fieldName + 
            '" id="' + fieldName + '" value="" /></span>';

        } 

        var $headerTextSpan = $('<span />')
                .addClass('jtable-column-header-text')
                .html(field.title + ss);

        var $headerContainerDiv = $('<div />')
                .addClass('jtable-column-header-container')
                .append($headerTextSpan);

        var $th = $('<th></th>')
                .addClass('jtable-column-header')
                .css('width', field.width)
                .data('fieldName', fieldName)
                .append($headerContainerDiv);           

        return $th;
    },

FIND:

    options: {
        sorting: false,
        defaultSorting: ''
    },

REPLACE WITH:

    options: {
        search: false,
        sorting: false,
        defaultSorting: ''
    },

In your icon-image directory put a nice small looking-glass icon and point to above: img src="img/icons/magnifier_zoom_in.png" or change the img filename accordingly.

Cheers :)

[KR4L3X]

woaaah, sweet man!!! good work! do you think its easy to implement operators such as "greater than or equal to" or less than.. etc.. ?

[pietera99]

That's actually quite easy. You can change the mySQL queries into what you need:

change:

 x LIKE ". quote_smart( chr(37) . $_GET["term"] . chr(37) )

into:

 x ". $_GET["term"]  

and then you can enter in the search-box your greater/lessthan/equal signs like this: ">123" or "<123" or "=123" etc

(do remember to protect against SQL-injections)

[OHB]

I'd like to see this brought in as a standard plugin. Text fields need some styling. See ExtJS for some ideas.

[lucky222452]

Hello,

How to wrap the listAction: on button click event. I mean i wanna to load my jTable when someone click a button on ASPX page.

[hikalkan]

See API documentation: http://jtable.org/ApiReference#met-load

Just call $('#MyTableContainer').jtable('load'); on button's click event.

[pietera99]

Thnx, didn't know that :)

[wallstcheater]

Could you add this feature to the Add a Record menu? I am trying to add stock tickers to my table and I would like the user to search for a company name or ticker before adding it the table. (I can't use a pull down menu, because I have 7,000 tickers/company names. They can't scroll through that size list.)

[wildantea]

yeup..i hope this is included in standard release

[pietera99]

@wallscheater: YES you can, it's actually quite easy to do so. What you need to do is all the stuff as described above, and then add a tiny bit of code (example below) to every field you want the autocomplete/suggest to popup within the form:

client-side code jTable:

                assignedto: {
                    title: 'Assigned to',
                    create: false,
                    edit: true,
                    list: false,
                    input: function (data) {
                            return '<div class="ui-widget"><input onkeyup="ac(this)" type="text" name="assignedto" id="assignedto" value="' + data.record.assignedto + '" /></div>';
                    }
                },

server-side code: in de php-code above find the autocomplete function switch statement and add your query here: something like:

    case 'assignedto' : $what = 'uname'; $items = fetchSqlArray("SELECT uname FROM users WHERE enabled = 1 AND uname LIKE '%".$val."%' GROUP BY uname LIMIT 10 ;"); break;

That's all :) have fun!

[himalr]

@pietera99

Thank you very much for this!

[gurgeh]

So, will this be included one day? It is a good addition.

[bleuscyther]

Upvoted feature

[boast1570]

This is exactly what I'm looking for! Any chance someone has tried this in ASP.NET MVC?