advanced search - browse table and filter

Re: advanced search - browse table and filter

Postby Tinka » Fri Jun 20, 2014 8:04 pm

Well, discovered a problem.

When the search field (hash variable) is empty (no search criteria) no records are found.

If I add a leading 0 to the hash variable used as filter in two different ways like here:
http://forums.nubuilder.com/viewtopic.php?f=4&t=8139&hilit=browse+filter&start=10 I get these results:

1. either the filter does not work but the field can be empty WHERE p.prim_name = 0#SrcPriName# or
2. sql error WHERE p.prim_name = 0'#SrcPriName#'

So how can I handle empty hash variables? Adding some additional code before the sql is executed?

BR, Tinka
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: advanced search - browse table and filter

Postby admin » Mon Jun 23, 2014 10:43 am

Tinka,

You will need to get the latest build (Shane has explained how to do that here.. http://forums.nubuilder.com/viewtopic.php?f=17&t=8449 )

There is another way to use hash variables. nuSetHash(name, value) will allow you to create hash variables that are not a field value from a table.

Steven
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: advanced search - browse table and filter

Postby Tinka » Mon Jun 23, 2014 11:53 pm

Steven,

Yes, I have the newest build.

I don't understand what you are suggesting to do by
There is another way to use hash variables. nuSetHash(name, value) will allow you to create hash variables that are not a field value from a table.


I try to find a way to rebuild the WHERE clause when my search fields are left empty - meaning the hash variables are not set/null.

I am looking for your advice in how to re-use some of the NuBuilder search field code that accounts for this case (function nuGetBrowseRecords($f, $p, $hashData) of nuapi.php file) OR

help to build a php function like in this example:
http://stackoverflow.com/questions/18575626/select-all-records-when-no-value-is-supplied

BR, Tinka
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: advanced search - browse table and filter

Postby fat115 » Tue Jun 24, 2014 2:33 am

Hi,

I've not tested but you may use MySQL IF statement in WHERE clause:

Something such as : WHERE IF('#SrcPriName#' != '',p.prim_name = '#SrcPriName#', 1)

If #SrcPriName# is empty, the clause is WHERE 1, else the clause is WHERE p.prim_name = '#SrcPriName#'
No more trick with the 0 preceding your hash variable.
fat115
 
Posts: 11
Joined: Sun May 11, 2014 4:48 pm
Location: France

Re: advanced search - browse table and filter

Postby admin » Tue Jun 24, 2014 10:07 am

Tinka

Tinka wrote:1. either the filter does not work but the field can be empty WHERE p.prim_name = 0#SrcPriName# or
2. sql error WHERE p.prim_name = 0'#SrcPriName#'


The results of 1 and 2 are because you will literally get this..

(if #SrcPriName# = '')

1 WHERE p.prim_name = 0
2 WHERE p.prim_name = 0''

(if #SrcPriName# = 'bob')

1 WHERE p.prim_name = 0bob
2 WHERE p.prim_name = 0'bob'

but this should work..

WHERE '0' = '0#SrcPriName#'

Steven
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: advanced search - browse table and filter

Postby Tinka » Tue Jun 24, 2014 4:52 pm

Fat115,

Thank you for your suggestion - it works!
So far, I have tested three concurrent WHERE IF statements in my sql and they work like they are supposed to ( I can leave any/all of the three fields empty).

So on Before Browse i have:
Code: Select all
$sql = "
Create table #TABLE_ID#
Select p.*,chr.*, gp.*, g.*
from primer p
Left join gene g ON g.gene_id = p.pri_gene_idFK
Left join genomic_position gp ON gp.genpos_id = p.pri_genpos_idFK
Left join chromnr chr ON chr.chromnr_id = gp.genpos_chrnr_idFK

WHERE IF('#SrcPriName#' != '',p.prim_name = '#SrcPriName#', 1) AND
IF('#Find_gene#' != '',g.gene_id = '#Find_gene#',1) AND
IF('#Findchr_nr#' != '',gp.genpos_chrnr_idFK = '#Findchr_nr#', 1)
";

nuDebug($sql);
$q = nuRunQuery($sql);


And on the form sql I just have
Code: Select all
Select * from #TABLE_ID#


Steven, I have not tested your solution.

BR, Tinka
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: advanced search - browse table and filter

Postby admin » Wed Jun 25, 2014 8:19 am

.
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Previous

Return to General