?
Solved

Searching all records in a table

Posted on 2012-04-09
11
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

 
LVL 39

Accepted Solution

by:
gdemaria earned 750 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 750 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
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 video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

770 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