Skip to main content
D
David Pridemore
Participant
November 17, 2008
Answered

Search form code help

  • November 17, 2008
  • 4 replies
  • 504 views
I'm a total CF newbie. I need to do a simple "Keyword" search of a small database.
The goal is the user enters one word all rows in the database are returned if the keyword appears in any field in the row. In theory I'd like to return the results in table format but first I just want it to work.


Here's my search form

<form action="results2.cfm" method="post" name="Search" id="Search">
<input name="keyword" type="text" id="keyword">
<input type="submit" name="Submit" value="GO">
</form>

Here's my query and output

<cfquery name="keysearch" datasource="ExhibitorWebDBase">
SELECT CompanyName, Email, WebSite, ExhibitorCategory
FROM ExhibitorWebDBase
WHERE CompanyName or Email or WebSite or ExhibitorCategory LIKE '%#form.keyword#%'
</cfquery>

<p class="auction1">Your Search Produced the Following Results</p>
<cfoutput query="keysearch">#keysearch.keyearch#<br></cfoutput>

This is the error message it's producing...
Element KEYEARCH is undefined in KEYSEARCH.

I feel like I am OK down to my WHERE statement (but maybe not)

Thanks you so much in advance for any help!
This topic has been closed for replies.
Correct answer Newsgroup_User
David Pridemore wrote:
>
> <p class="auction1">Your Search Produced the Following Results</p>
> <cfoutput query="keysearch">#keysearch.keyearch#<br></cfoutput>
>
> This is the error message it's producing...
> Element KEYEARCH is undefined in KEYSEARCH.
>

Ok, I reread your post, and this is not the error message I would expect
to see with an syntax error in the SQL where clause. I have never seen
"WHERE CompanyName or Email or WebSite or ExhibitorCategory LIKE
'%#form.keyword#%'" in sql, but maybe this *is* allowed.

If it is and you are getting a result back, then your output is still
wrong. First of all, #keysearch.keyearch# looks like a typo, you
probably wanted to type #keysearch.keysearch#. But that would not work
either. You need to output column names.

I.E.
<cfoutput query="keysearch">#keysearch.CompanyName#
#keysearch.Email#...</cfoutput>

This will matter whether or not you need to correct the SQL syntax in
the where clause.


4 replies

N
Newsgroup_User
Inspiring
November 18, 2008
it does not matter now, but the error you saw was because of a typo:
you had KEYEARCH instead of KEYSEARCH (missing S)

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
D
Dan_Bracuk
Inspiring
November 17, 2008
quote:
Originally posted by: David Pridemore
I'm a total CF newbie. I need to do a simple "Keyword" search of a small database.
The goal is the user enters one word all rows in the database are returned if the keyword appears in any field in the row. In theory I'd like to return the results in table format but first I just want it to work.


Here's my search form

<form action="results2.cfm" method="post" name="Search" id="Search">
<input name="keyword" type="text" id="keyword">
<input type="submit" name="Submit" value="GO">
</form>

Here's my query and output

<cfquery name="keysearch" datasource="ExhibitorWebDBase">
SELECT CompanyName, Email, WebSite, ExhibitorCategory
FROM ExhibitorWebDBase
WHERE CompanyName or Email or WebSite or ExhibitorCategory LIKE '%#form.keyword#%'
</cfquery>

<p class="auction1">Your Search Produced the Following Results</p>
<cfoutput query="keysearch">#keysearch.keyearch#<br></cfoutput>

This is the error message it's producing...
Element KEYEARCH is undefined in KEYSEARCH.

I feel like I am OK down to my WHERE statement (but maybe not)

Thanks you so much in advance for any help!

One is never too new to learn better ways of doing things. If ExhibiterCategory is a text field, you can save yourself some grief by learning about database normalization. I understand that the book, "Database Design for Mere Mortals" is pretty good.

Also, for what you are looking to accomplish, using the keyword "or" in your query is intuitive and will work. The problem is that it might be slow. You can accomplish the same thing, usually quicker with a union query. If you don't know what that is, I've heard good things about the book, "Teach Yourself SQL in 10 Minute" by Ben Forta.
N
Newsgroup_UserCorrect answer
Inspiring
November 17, 2008
David Pridemore wrote:
>
> <p class="auction1">Your Search Produced the Following Results</p>
> <cfoutput query="keysearch">#keysearch.keyearch#<br></cfoutput>
>
> This is the error message it's producing...
> Element KEYEARCH is undefined in KEYSEARCH.
>

Ok, I reread your post, and this is not the error message I would expect
to see with an syntax error in the SQL where clause. I have never seen
"WHERE CompanyName or Email or WebSite or ExhibitorCategory LIKE
'%#form.keyword#%'" in sql, but maybe this *is* allowed.

If it is and you are getting a result back, then your output is still
wrong. First of all, #keysearch.keyearch# looks like a typo, you
probably wanted to type #keysearch.keysearch#. But that would not work
either. You need to output column names.

I.E.
<cfoutput query="keysearch">#keysearch.CompanyName#
#keysearch.Email#...</cfoutput>

This will matter whether or not you need to correct the SQL syntax in
the where clause.


D
David PridemoreAuthor
Participant
November 17, 2008
Thanks so much to those of you who have posted help! I'll implement your suggestions.
N
Newsgroup_User
Inspiring
November 17, 2008
David Pridemore wrote:
> WHERE CompanyName or Email or WebSite or ExhibitorCategory LIKE

This is not allowed. You need an operator and value for each field.
I.E.
WHERE CompanyName LIKE '%#form.keyword#%' OR Email LIKE
'%#form.keyword#%' ...

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices