Solved

Searching all records in a table

Posted on 2012-04-09
11
252 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
 
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
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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short film showing how OnPage and Connectwise integration works.

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now