[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Searching all records in a table

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
earwig75
Asked:
earwig75
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
AnuroopsunddCommented:
select * from tablename
0
 
earwig75Author Commented:
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
 
_agx_Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
gdemariaCommented:
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
 
_agx_Commented:
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
 
gdemariaCommented:
.. 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
 
earwig75Author Commented:
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
 
gdemariaCommented:
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
 
gdemariaCommented:
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
 
_agx_Commented:
.. 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
 
earwig75Author Commented:
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now