search interface question

Hi, i have a query where my situation is quite complex, all structure values are coming as dynamic,

now, the situation is like this

<cfset searchby = "id,department">
<cfset searchString = "UPPER(ID) LIKE '%dynaicFormvalue%',UPPER(department) LIKE '%dynaicFormvalue%">

now the issue is department might not exist in the table from the main view from where i am fetchng the results, so how can i check that if dept exists in search remove the searchString of department from it

a note here, department name the operator and the value all are coming as dynamic
LVL 16
Gurpreet Singh RandhawaWeb DeveloperAsked:
Who is Participating?
 
gdemariaConnect With a Mentor Commented:
> the string from which to remove the data exists like this

You really need to rework the way you are creating these lists.   They would be much better as structures.   Or like in your other question, there is one element on each list for each where clause.   I dont' see how you're going to parse out half of the where clause with any consistency.

You should assemble the where clause later, after you validate each column.   You can store a structure for each column, something like this..

<cfset clause["DeptID"].search  = "Hello">
<cfset clause["DeptID"].operator  = "=">

<cfset clause["ID"].search  = "2">
<cfset clause["ID"].operator  = "Like">

That way you can easily remove columns that don't match the search and you can build the where clause.  

You have to keep the association between the column and the search criteria clear so you can control which values are included in the where clause and which are not.


0
 
SidFishesCommented:
A bit hard to say but something like this may work

<cfset searchby = "id,department">
<cfset dept = department> < not sure where department value is coming from but ...

<cfquery name="checkDept"...>
select * from tbl where department =  #dept#
</cfquery>

<cfif checkDept.recordcount eq 0>
<cfset searchString = "UPPER(ID) LIKE '%dynaicFormvalue%'>
<cfelse>
<cfset searchString = "UPPER(ID) LIKE '%dynaicFormvalue%',UPPER(department) LIKE '%dynaicFormvalue%">
</cif>
0
 
gdemariaCommented:

 You can check it against a query with no records returned and make a list of valid fields...



<cfset searchby = "id,department">
<cfquery name="checkFields" datasource="#request.datasource#">
  select * from myTable where 1 = 2
</cfquery>

<cfset searchFields = "">

<cfloop index="aField" list="#searchBy#">
  <cfif listFindNoCase(checkFields.columnList,aField)>
     <cfset searchFields = listAppend(searchFields,aField)>
  </cfif>
</cfloop>

Now you can search by any column name still on the list "searchFields"...

<cfif listFindNoCase(searchFields,"Department")>

</cfif>


0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
That's fine GD but i have to FIND this

UPPER(ID) LIKE '%dynaicFormvalue% and remove it from the searchString because searchString is build upon the SearchBy, because if the DEPartment is defined, then ionly this will appear and i have to completely eracticate from the searchstring which will be building like this

SearchString will be like this UPPER(ID) LIKE '%dynaicFormvalue% AND UPPER(DEPARTMENT) = 'MyDept' OR UPPER(ID) LIKE '%thisvalu%

so suppose i check for Dept in searchby and it is linked with AND UPPER(DEPARTMENT) = 'MyDept' , so then i have to remove this from the searchString, like this there are couple of 5/6 fields to do it like this

AND can be OR and equal to sign could be like or not equal to, all selected dynamically and the value passed against each parameter

isuppos e i have abeen able to properlyexplain what i m trying
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Any Updates @gd, @sid
0
 
gdemariaCommented:
If I understand correctly, you are saying that you already have DEPARTMENT in the list of search criteria and you need to REMOVE it if the column doesn't exist in the table.


<cfset searchby = "id,department">
<cfset searchString = "UPPER(ID) LIKE '%dynaicFormvalue%',UPPER(department) LIKE '%dynaicFormvalue%">

<cfquery name="checkFields" datasource="#request.datasource#">
  select * from myTable where 1 = 2
</cfquery>

<cfset searchFields = "">
<cfset searchClause = "">

<cfset counter = 0>
<cfloop index="aField" list="#searchBy#">
  <cfset counter = counter + 1>
  <cfif listFindNoCase(checkFields.columnList,aField)>
     <cfset searchFields = listAppend(searchFields,aField)>
     <cfset searchClause = listAppend(searchClause,listGetAg(searchString,counter))>
  </cfif>
</cfloop>

Now searchClause contains only the criteria you are allowed to search on..

searchClause

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
the string from which to remove the data exists like this

AND (UPPER(ID) LIKE ''%2%'' AND UPPER(DEPTID) =''HELLO''so this is not need

 AND UPPER(DEPTID) =''HELLO''
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
in this case, i have to detect the followng:

AND symbol

Upper(Fieldtowhich to do the replacement)

Like or = operator

so like this

AND Upper(Fieldtowhich to do replacement) LIKE %'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.