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 RandhawaCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Gurpreet Singh RandhawaCEOAuthor 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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Gurpreet Singh RandhawaCEOAuthor 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 RandhawaCEOAuthor 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
gdemariaCommented:
> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gurpreet Singh RandhawaCEOAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.