advanced search - browse table and filter

[Tinka]

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.cloud/viewtopic.p ... r&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

[admin]

Tinka,

You will need to get the latest build (Shane has explained how to do that here.. http://forums.nubuilder.cloud/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

[Tinka]

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/1857 ... s-supplied

BR, Tinka

[fat115]

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.

[admin]

Tinka

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

[Tinka]

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

[admin]

.