Solved

Searching all records in a table

Posted on 2012-04-09
11
251 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
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 …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
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: …

744 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

11 Experts available now in Live!

Get 1:1 Help Now