Page 2 of 2
Re: advanced search - browse table and filter
Posted: Fri Jun 20, 2014 12:34 pm
by 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
Re: advanced search - browse table and filter
Posted: Mon Jun 23, 2014 3:13 am
by 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
Re: advanced search - browse table and filter
Posted: Mon Jun 23, 2014 4:23 pm
by 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
Re: advanced search - browse table and filter
Posted: Mon Jun 23, 2014 7:03 pm
by 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.
Re: advanced search - browse table and filter
Posted: Tue Jun 24, 2014 2:37 am
by admin
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
Re: advanced search - browse table and filter
Posted: Tue Jun 24, 2014 9:22 am
by 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
Steven, I have not tested your solution.
BR, Tinka
Re: advanced search - browse table and filter
Posted: Wed Jun 25, 2014 12:49 am
by admin
.