javascript - Dynamically generating filter for datatable -
i asking question again since dint find proper idea. trying implement feature filter item based on vendor name. item column according vendor name. stuck here since week . tried many ways not working. requirement steps below.
- i name of vendor db.
- use retrieved vendor name filter name list
:<option>vendorname</option> - i can item_code each vendor using vendor code foreign key there multiple item_code each vendor may more 500.
- now want create drop down in such way upon selecting vendor, corresponding item code table should filtered.
any suggestion or steps appreciated.
my final effort:
$('#vendor').on('change',function(){ var selectedvalue = $(this).val(); $("#testtable").datatable().fnfilter("^"+selectedvalue+"$", 0, true); //exact value, column, reg }); <div class="optionsdiv2" style="display:inline;" > vendor name:<select name="item" id="vendor" style="width:510px;"> <option value=" " selected>all</option> <% try{ string queryv = "select vname, vcode s_vendors"; statement stmtv=conn.createstatement(); resultset rsv=stmtv.executequery(queryv); while (rsv.next()) { vcd = rsv.getstring("vcode"); string vcd="'"+vcd+"'"; string queryi = "select icode vendor_item_details vcode="+vcd; statement stmti=conn.createstatement(); resultset rsi=stmti.executequery(queryi); try{ i=0; while (rsi.next()) array1[i++] = rsi.getstring("icode").tostring(); join1 = string.join("|", array1); } catch(exception ex) { out.println(ex); } %> <option value="<%= join1%>"><%= rsv.getstring("vname")%></option> <% arrays.fill(array1, null); } rsv.close(); stmtv.close(); } catch(exception ex) { out.println(ex); } %> </select> </div> i have added working fiddle here datatable id "mine" , dropdown id "myselect": https://jsfiddle.net/safoora/2j6w9hqt/18/ . same way trying above code.
any new suggestions develop beginning welcome.
one among option list below:
<option value="401 1|401 1.1/2|401 1.1/4|401 1/2|401 2|401 2.1/2|401 3|401 3/4|401 4|ad can|bb ss 3|bbh bu 2.1/2x1.1/2|bbh bu 2.1/2x2|bbh bu 3x1|bbh bu 3x1.1/2|bbh bu 3x2|bbh bu 3x2.1/2|bbh bu 4x2|bbh bu 4x3|bbh cp 2.1/2|bbh cr 3|bbh el 45 3|bbh el 45 4|bbh el 90 2.1/2|bbh el 90 3|bbh el 90 4|bbh ni 2.1/2|bbh ni 3|bbh ni 4|bbh rs 2.1/2x1.1/2|bbh rs 2.1/2x2|bbh rs 3x2|bbh rt 2.1/2x1|bbh rt 2.1/2x1.1/2|bbh rt 2.1/2x2|bbh rt 3x1.1/2|bbh rt 3x2|bbh rt 4x2|bbh 2.1/2|bbh 3|bbh 4|bbh te 2.1/2|bbh te 3|bbh te 4|bbh un 2.1/2|bbh un 3|bd 150 12|bd 150 3|bib 1/2|bib 3/4|bn bu 1.1/2x1|bn bu 1.1/2x1.1/4|bn bu 1.1/2x1/2|bn bu 1.1/4x1|bn bu 1.1/4x1/2|bn bu 1.1/4x3/4|bn bu 1x1/2|bn bu 1x3/4|bn bu 2.1/2x1.1/2|bn bu 2x1|bn bu 2x1.1/2|bn bu 2x1.1/4|bn bu 2x1/2|bn bu 2x3/4|bn bu 3/4x1/2|bn cr 1|bn cr 1.1/2|bn cr 1.1/4|bn cr 2|bn el 45 1|bn el 45 1.1/2|bn el 45 1.1/4|bn el 45 2|bn el 45 3/4|bn el 90 1|bn el 90 1.1/2|bn el 90 1.1/4|bn el 90 1/2|bn el 90 2|bn el 90 3/4|bn ni 1|bn ni 1.1/2|bn ni 1.1/4|bn ni 1/2|bn ni 2|bn ni 3/4|bn pg 1|bn pg 1.1/2|bn pg 1.1/4|bn pg 1/2|bn pg 3/4|bn rl 1.1/2x1|bn rl 1.1/2x1.1/4|bn rl 1.1/4x1|bn rl 1.1/4x1/2|bn rl 1x1/2|bn rl 2x1.1/2|bn rs 1.1/2x1|bn rs 1.1/2x1.1/4|bn rs 1.1/2x1/2|bn rs 1.1/2x3/4|bn rs 1.1/4x1|bn rs 1.1/4x3/4|bn rs 1x1/2|bn rs 1x3/4|bn rs 2x1.1/2|bn rs 2x1.1/4|bn rs 3/4x1/2|bn rt 1.1/2x1|bn rt 1.1/2x1.1/4|bn rt 1.1/2x1/2|bn rt 1.1/2x3/4|bn rt 1.1/4x1|bn rt 1.1/4x1/2|bn rt 1.1/4x3/4|bn rt 1x1/2|bn rt 1x3/4|bn rt 2x1|bn rt 2x1.1/2|bn rt 2x1.1/4|bn rt 2x1/2|bn rt 2x3/4|bn sl 1|bn sl 1.1/2|bn sl 1.1/4|bn sl 1/2|bn sl 3/4|bn 1|bn 1.1/2|bn 1.1/4|bn 1/2|bn 2|bn 3/4|bn som 1/2|bn te 1|bn te 1.1/2|bn te 1.1/4|bn te 1/2|bn te 2|bn te 3/4|bn un 1|bn un 1.1/2|bn un 1.1/4|bn un 1/2|bn un 2|bn un 3/4|bps40l 1|br bl 1|br bl 1.1/2|br bl 1.1/4|br bl 1/2|br bl 2|br bl 2.1/2|br bl 3|br bl 3/4|br bl 4|br ft 1|br ft 1.1/2|br ft 1.1/4|br ft 2|br ft 2.1/2|br ft 3|br ft 4|br gt 2.1/2|br hn 1/2|brp gt 1.1/2|brp gt 2|brp gt 2.1/2|brp gt 3|brp gt 3/4|brp gt 4|bsp40 1 n|bsp40 1.1/4 n|bsp40 3/4 n|bt|bwl cp 6|bwl cp 8|bwl el 45 5|bwl el 90 1|bwl el 90 1.1/2|bwl el 90 10|bwl el 90 2|bwl el 90 5|bwl rc 2x1.1/4|bwl re 10x4|bwl re 8x4|bwl rt 10x5|bwl rt 10x8|bwl rt 12x10|bwl rt 2.1/2x1.1/2|bwl rt 2.1/2x1.1/4|bwl rt 3x1|bwl rt 3x1.1/2|bwl rt 3x1.1/4|bwl rt 4x1.1/2|bwl rt 4x1.1/4|bwl rt 4x3|bwl rt 6x4|bwl rt 8x6|bwl te 5|cb cp 3|cb cp 4|cb cp 6|cb el 45 2.1/2|cb el 45 3|cb el 45 4|cb el 45 6|cb el 45 8|cb el 90 10|cb el 90 2.1/2|cb el 90 3|cb el 90 4|cb el 90 5|cb el 90 6|cb fl 2.1/2|cb fl 3|cb fl 4|cb fl 6|cb fl 8|cb mt 2.1/2x1|cb mt 2.1/2x1.1/2|cb mt 3x1|cb mt 3x1.1/2|cb mt 3x2|cb mt 4x1|cb mt 4x1.1/2|cb mt 4x1.1/4|cb mt 4x2|cb mt 4x2.1/2|cb mt 4x3|cb mt 6x1.1/2|cb mt 6x1.1/4|cb mt 6x2|cb mt 6x2.1/2|cb mt 6x3|cb rc 2.1/2x2|cb rc 2x1.1/2|cb rc 3x2|cb rc 3x2.1/2|cb rc 4x2|cb rc 4x2.1/2|cb rc 4x3|cb rc 6x3|cb rc 6x4|cb rs gs 2.1/2x2|cb rt 2.1/2x2|cb rt 3x1.1/2|cb rt 3x2|cb rt 3x2.1/2|cb rt 4x2|cb rt 4x3|cb rt 5x3|cb rt 6x2.1/2|cb rt 6x3|cb rt 6x4|cb 10|cb 12|cb 2|cb 2.1/2|cb 3|cb 4|cb 5|cb 6|cb 8|cb te 2.1/2|cb te 3|cb te 4|cb te 5|cb te 6|cb te 8|cu ad cf 1/2x3/8 ch|fn bu 1x1/2|fn bu 1x3/4|fn bu 3/4x1/2|fn el 90 1|fn el 90 1/2|fn el 90 3/4|fn ni 1|fn ni 1/2|fn ni 3/4|fn 1|fn 1/2|fn 3/4|fn te 1|fn te 1/2|fn te 3/4|fn un 1|fn un 1/2|fn un 3/4|gaz 1/2|gazm 1/2|gb st fl 2.1/2|gb st fl 3|gb st fl 4|gbh bu 2.1/2x1|gbh bu 2.1/2x1.1/2|gbh bu 2.1/2x2|gbh bu 3x2.1/2|gbh bu 4x1.1/2|gbh bu 4x2|gbh bu 4x3|gbh el 45 3|gbh el 45 4|gbh el 90 2.1/2|gbh el 90 3|gbh el 90 4|gbh ni 2.1/2|gbh ni 3|gbh ni 4|gbh pg 3|gbh rt 2.1/2x2|gbh rt 3x1|gbh rt 3x1.1/2|gbh rt 3x2|gbh rt 4x2|gbh rt 4x3|gbh 2.1/2|gbh 3|gbh 4|gbh te 2.1/2|gbh te 4|gbh un 2.1/2|gbh un 3|gbh un 4|gcb rc 3x1.1/2|gn bu 1.1/2x1|gn bu 1.1/2x1.1/4|gn bu 1.1/2x1/2|gn bu 1.1/2x3/4|gn bu 1.1/4x1|gn bu 1.1/4x1 r|gn bu 1x1/2|gn bu 1x3/4|gn bu 2x1|gn bu 2x1 r|gn bu 2x1.1/2|gn bu 2x1.1/2 r|gn bu 2x1.1/4|gn bu 2x1.1/4 r|gn bu 2x1/2|gn bu 2x3/4|gn bu 3/4x1/2|gn cp 1|gn cp 1.1/2|gn cp 1.1/4|gn cp 2|gn cr 2|gn el 45 1|gn el 45 1.1/2|gn el 45 1.1/4|gn el 45 2|gn el 90 1|gn el 90 1.1/2|gn el 90 1.1/2 r|gn el 90 1.1/4|gn el 90 1/2|gn el 90 2|gn el 90 3/4|gn ni 1|gn ni 1.1/2|gn ni 1.1/4|gn ni 1/2|gn ni 2|gn ni 3/4|gn pg 1|gn pg 1.1/2|gn pg 1.1/4|gn pg 1/2|gn pg 2|gn pg 3/4|gn rl 1.1/4x1|gn rl 1x1/2|gn rl 2x1.1/2|gn rs 1.1/2x1|gn rs 1.1/2x1.1/4|gn rs 1.1/2x1/2|gn rs 1.1/4x1|gn rs 1x1/2|gn rs 1x3/4|gn rs 2x1|gn rs 2x1.1/2|gn rs 2x1.1/4|gn rt 1.1/2x1|gn rt 1.1/2x1.1/4|gn rt 1.1/2x1/2|gn rt 1.1/4x1|gn rt 1.1/4x1/2 r|gn rt 1x1/2|gn rt 2x1|gn rt 2x1.1/2|gn rt 2x1.1/2 r|gn rt 2x1.1/4|gn rt 2x1.1/4 r|gn rt 2x1/2|gn rt 2x3/4|gn rt 3x1|gn sl 1/2|gn 1|gn 1.1/2|gn 1.1/4|gn 1/2|gn 2|gn 3/4|gn som 1.1/2|gn som 1/2|gn te 1|gn te 1.1/2|gn te 1.1/4|gn te 1/2|gn te 2|gn te 3/4|gn un 1|gn un 1.1/2|gn un 1.1/4|gn un 2|gn un 3/4|hemp|hm wt 15 1/2 ppp|permatex|pipe|ptfe|ptfe 3|ptfe 6|so 150 2|so 150 2.1/2|so 150 3|so 150 4|so 150 5|so 150 6|so 16 2|so 16 2.1/2|so 16 3|so 16 4|so 16 5|spw40 2 316l|ss un 1|sso 16 3|sso 16 4|tp in|tp inr|wn 150 2|wn 150 3|wn 150 4|wn 150 5|wn 150 8|wn 16 4|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null">abdulaziz . ismail est</option>
$(this).val() refers value attribute of <option> contains icode, going passed fnfilter.
for example, if html output this:
<option value="cl 1/2|cl 3/4|cu el 45 1.1">al- kaser a/c est.</option> if selected, current code, constructed regex ^cl 1/2|cl 3/4|cu el 45 1.1$, means match table rows first column contains text cl 1/2xxxx, xxxxxcl 3/4xxxx, , xxxxxcu el 45 1.1 (note x chars). match xxxxxcu el 45 1x1 because in regex, dot char match characters.
if want match cl 1/2, cl 3/4, , cu el 45 1.1, pattern needs enclosed parantheses ( ) between ^ , $, , special regex characters must escaped, i.e. ^(cl 1\/2|cl 3\/4|cu el 45 1\.1)$. moreover, fnfilter doesn't recognize space so need use \s instead of literal space. final regex ^(cl\s1\/2|cl\s3\/4|cu\sel\s45 1\.1)$.
looking @ current code (where second parameter fnfilter 0, i.e. first column), assume display icode in first column , perhaps vendor name in column. see illustration below.
alternative solution #1 filtering based on icode
$(document).ready(function() { var table = $('#mine').datatable(); $('#myselect').on('change', function() { var selectedvalue = $(this).val(); var patt = selectedvalue.replace(/[\[\]\/\{\}\(\)\*\+\?\.\\\^\$-]/g, "\\$&"); //escape regex chars except "|" patt = patt.replace(/ /g, "\\\s"); //change space "\s" make fnfilter works if (patt) patt = "^(" + patt + ")$"; //enclose parentheses when pattern not empty console.log(patt); $("#mine").datatable().fnfilter(patt, 0, true); //exact value, column, reg }); });<script src="https://code.jquery.com/jquery-1.9.1.min.js"></script> <script src="http://cdn.datatables.net/1.10.12/js/jquery.datatables.min.js"></script> <select id="myselect"> <option value="">all</option> <!-- --> <option value="cl 1/2|cu el 45 1.1">al- kaser a/c est.</option> <!-- ^(cl\s1\/2|cu\sel\s45\s1\.1)$ --> <option value="bolt 3/4x4|ch 1.1/2">al-alwani trading c0</option> <!-- ^(bolt\s3\/4x4|ch\s1\.1\/2)$ --> </select> <table id="mine" border="1" cellpadding="1" cellspacing="0"> <thead> <tr> <!-- header row --> <th>icode</th> <!-- column index 0 --> <th>vendor</th> <!-- column index 1 --> <th>desc</th> <!-- column index 2 --> </tr> </thead> <tfoot> </tfoot> <tbody> <tr> <!-- 1st row --> <td>cl 1/2</td> <!-- column index 0 --> <td>al- kaser a/c est.</td> <!-- column index 1 --> <td>3rd column</td> <!-- column index 2 --> </tr> <tr> <!-- 2nd row --> <td>cu el 45 1.1</td> <!-- column index 0 --> <td>al- kaser a/c est.</td> <!-- column index 1 --> <td>3rd column</td> <!-- column index 2 --> </tr> <tr> <!-- 3rd row --> <td>bolt 3/4x4</td> <!-- column index 0 --> <td>al-alwani trading c0</td> <!-- column index 1 --> <td>3rd column</td> <!-- column index 2 --> </tr> <tr> <!-- 4th row --> <td>ch 1.1/2</td> <!-- column index 0 --> <td>al-alwani trading c0</td> <!-- column index 1 --> <td>3rd column</td> <!-- column index 2 --> </tr> </tbody> </table>important should not have nulls in icode (as shown in chat conversation in comment below). if have multiple option values have null, if of option selected, rows have icode equals null shown. if null unavoidable, second alternative below may better achieve same result.
alternative solution #2 filtering based on vendor name
the code identical. difference using vendor name value of <option>s. , because of that, second parameter fnfilter() changed 1 (which refers second column showing vendor names).
$(document).ready(function() { var table = $('#mine').datatable(); $('#myselect').on('change', function() { var selectedvalue = $(this).val(); var patt = selectedvalue.replace(/[\[\]\/\{\}\(\)\*\+\?\.\\\^\$-]/g, "\\$&"); //escape regex chars except "|" patt = patt.replace(/ /g, "\\\s"); //change space "\s" make fnfilter works if (patt) patt = "^(" + patt + ")$"; //enclose parentheses when pattern not empty console.log(patt); $("#mine").datatable().fnfilter(patt, 1, true); //exact value, column, reg }); });<script src="https://code.jquery.com/jquery-1.9.1.min.js"></script> <script src="http://cdn.datatables.net/1.10.12/js/jquery.datatables.min.js"></script> <select id="myselect"> <option value="">all</option> <!-- --> <option value="al- kaser a/c est.">al- kaser a/c est.</option> <!-- ^(al\-\skaser\sfor\sa\/c\sest\.)$ --> <option value="al-alwani trading c0">al-alwani trading c0</option> <!-- ^(al\-alwani\strading\sc0)$ --> </select> <table id="mine" border="1" cellpadding="1" cellspacing="0"> <thead> <tr> <!-- header row --> <th>icode</th> <!-- column index 0 --> <th>vendor</th> <!-- column index 1 --> <th>desc</th> <!-- column index 2 --> </tr> </thead> <tfoot> </tfoot> <tbody> <tr> <!-- 1st row --> <td>cl 1/2</td> <!-- column index 0 --> <td>al- kaser a/c est.</td> <!-- column index 1 --> <td>3rd column</td> <!-- column index 2 --> </tr> <tr> <!-- 2nd row --> <td>cu el 45 1.1</td> <!-- column index 0 --> <td>al- kaser a/c est.</td> <!-- column index 1 --> <td>3rd column</td> <!-- column index 2 --> </tr> <tr> <!-- 3rd row --> <td>bolt 3/4x4</td> <!-- column index 0 --> <td>al-alwani trading c0</td> <!-- column index 1 --> <td>3rd column</td> <!-- column index 2 --> </tr> <tr> <!-- 4th row --> <td>ch 1.1/2</td> <!-- column index 0 --> <td>al-alwani trading c0</td> <!-- column index 1 --> <td>3rd column</td> <!-- column index 2 --> </tr> </tbody> </table>
Comments
Post a Comment