Then depending on what they have selected to search is how to determine what
SQL like statement you execute.
Shawn Regan
-----Original Message-----
From: Candace Cottrell [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 7:25 AM
To: CF-Community
Subject: Need some SQL help
Hey all!! I am working on a little search function and need just a
little help with the query.
The search box is called "keyword". I'd like for the users to be able
to search both the question text, the answer text, and the doctors
name:
<cfquery name="list" datasource="ASKANEXPERT">
SELECT
dbo.QUESTION.Question_Text,
dbo.QUESTION.Question_Date,
dbo.ANSWER.Answer_Text,
dbo.QUESTION_ANSWER.Question_ID,
dbo.QUESTION_ANSWER.Answer_ID,
dbo.ANSWER.Answer_ID,
dbo.ANSWER.Answer_Date,
dbo.ANSWER.Various_Name,
dbo.TBLUser.USer_First_Name,
dbo.TBLUser.User_Last_Name,
dbo.TBLUser.User_Suffix,
dbo.TBLUser.User_ID
FROM
dbo.QUESTION,
dbo.ANSWER,
dbo.QUESTION_ANSWER,
dbo.TBLUser
WHERE
<CFIF IsDefined("Keyword") AND Len(Trim(Keyword))>
Question_Text LIKE '%#Keyword#%'
AND
</CFIF>
dbo.QUESTION_ANSWER.Answer_ID = dbo.ANSWER.Answer_ID
AND dbo.QUESTION.Question_ID = dbo.QUESTION_ANSWER.Question_ID
AND dbo.TBLUser.User_ID = dbo.ANSWER.User_ID
ORDER BY
dbo.QUESTION.Question_Date DESC
</cfquery>
So here is where I get stuck:
WHERE
<CFIF IsDefined("Keyword") AND Len(Trim(Keyword))>
Question_Text LIKE '%#Keyword#%'
AND
</CFIF>
I have tried:
WHERE
<CFIF IsDefined("Keyword") AND Len(Trim(Keyword))>
Question_Text LIKE '%#Keyword#%'
OR
Answer_Text LIKE '%#Keyword#%'
OR
User_First_Name LIKE '%#Keyword#%'
OR
User_Last_Name LIKE '%#Keyword#%'
AND
</CFIF>
And it hoses the page.
Any help is appreciated!!!
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
"There is no right price for the wrong product, even if it is
inexpensive and delivered on time."
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
