Solved

Searching all records in a table

Posted on 2012-04-09
11
253 Views
Last Modified: 2012-04-09
I currently search records with a form and send it to another page with  a query similar to the one below. Is it possible to search all records in a table without having to type each column name in the query? Basically a global search that will search all records in a specified table?

Thank you.

<cfquery name="movies"
            datasource="#ds#"
            dbtype="ODBC" >

SELECT MovieTitle, PitchText, Summary, DateInTheaters
FROM [OWS].[dbo].Films
WHERE 0=0
<!--- Search by movie title --->
<cfif FORM.MovieTitle IS NOT "">
      AND MovieTitle LIKE '%#FORM.MovieTitle#%'
</cfif>
<!--- Search by tag line --->
<cfif FORM.PitchText IS NOT "">
      AND PitchText LIKE '%#FORM.PitchText#%'
</cfif>
<cfif FORM.RatingID IS NOT "">
      AND RatingID LIKE '%#FORM.RatingID#%'
</cfif>
ORDER BY MovieTitle
</cfquery>
0
Comment
Question by:earwig75
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37823867
select * from tablename
0
 

Author Comment

by:earwig75
ID: 37823874
select * from tablename

I probably didn't word what I am asking well. I'd like to create 1 search box that will search all records and return all records that have that have what is searched for. I'd like to do this without having to type each column name as a "where" in the query.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37823931
Can I ask why don't you want to type out the column names?  

What you've got above is the standard way to do it .. and you only have to write the query once.  Unless there's a good reason, I wouldn't recommend trying to do it dynamically. It's almost always more difficult than you think to do dynamic sql correctly and safely ie You have to handle data types and sql injection.  Not to mention, dynamic code is usually harder to debug and maintain.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 37823956
You could loop through the form fields and check to see if there is a matching table column with the same name, then create a where clause segment based on that...

The problem is that you don't know the data type of the column, so you have to assume they are all strings; using LIKE on everything include numbers and dates doesn't really make sense.


<!--- a dummy query to fetch the column names ---->
<cfquery name="getCols" datasource="#request.datasource#">
  select * from myTable where 1 = 2
</cfquery>

<cfquery name="getData" datasource="#request.datasource#">
  select *
    from myTable
  where 1 = 1
  <cfloop index="aField" list="#form.formFields#">
   <cfif listFindNoCase(getCols.columnList, aField) and len(form[aField])>
    and #aField# like <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form[aField]#">
   </cfif>
  </cfloop>
</cfquery>
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 37823990
I guess I don't see why you can't just change the form field name.  It'd do the same thing, and you'd have more control over shaping the correct sql (Though you should be using cfqueryparam)

       <cfif LEN(TRIM(FORM.YourSingleFieldName))>
           AND MovieTitle LIKE '%#FORM.YourSingleFieldName#%'
       </cfif>
      <<cfif LEN(TRIM(FORM.YourSingleFieldName))>
           AND PitchText LIKE '%#FORM.YourSingleFieldName#%'
      </cfif>

so you have to assume they are all strings; using LIKE on everything include numbers and dates doesn't really make sense.

.. and won't always return the results you'd expect because you're letting the db decide how to interpret and compare the string values. Implicit conversion also tends to be less efficient.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 37824066
.. and if you're going to do this like a full text search (one search field for all fields), then use a verity collection; comes with ColdFusion
0
 

Author Comment

by:earwig75
ID: 37824157
Right now I am using the below in my function. I have 50+ fields and didn't want to create an "or" for each and every field. I guess that is the right way to do it though so that's what I'll do. Thanks guys.


            <cfif len(arguments.getSearchString)>
 
            and lower(dbo.Tbl_record_Master.Field1) like <cfqueryparam value="%#lcase(arguments.getSearchString)#%" cfsqltype="cf_sql_varchar">

            or lower(dbo.Rtbl_record_OfferType.OfferType) like <cfqueryparam value="%#lcase(arguments.getSearchString )#%" cfsqltype="cf_sql_varchar">
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 37824235
Thanks -

For this type of thing, you should look into Verity.   Verity can handle multiple words found in multiple fields regardless of the order of the terms, stuff like that.  Like Google-ish..
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 37824256
Earwig, why the "B" grade?  

Did you have unanswered questions?   I don't see anything that was not addressed...

And you did use the solution provided..  

A "B" means you didn't get what you needed... what was missing?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37825023
.. and in case you're new to EE, grading is generally used to indicate how accurate and complete the given answers are.  A "B" usually means you didn't get enough information. Though it's not always what you want to hear, sometimes the correct answer is "no" or "it's not advisable".  In this case you were given three answers along with the pros/cons of each one

           1) how to do it dynamically as asked (less efficient and less accurate)
           2) the more standard approach of listing the columns individually (more code, but more accurate)
           3) using verity, which is more suitable when searching such a large number of columns (most accurate)

Was there something else you needed explained?
0
 

Author Comment

by:earwig75
ID: 37825381
Sorry, I will fix if possible when I am on a computer again. I am new to EE and guess I didn't understand the grading. I thought it was more related to how good the solution would work for others. Because of the limitations you pointed out it isn't a great solution for others to use. I didn't realize I was actually grading the experts.

Again, I apologize.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
need some regex help 15 29
Need to use URL Encode in CFDiv Tag ColdFusion, receiving error 2 63
Sending Text Messages 1 56
On Submit return to same spot 7 92
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question