|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
October 17th, 2002, 10:21 AM
|
|||
|
|||
|
Searching More than One Field
Hi,
Is there a way in a SQL statement to search more than one field in a table. Here is my current code. It only searches the BODY field. I would like to be able to search the title and subtitle as well. Thanks in advance. Code:
strSQL = "Select * from bv_PageSection "
strSQL = strSQL & "WHERE SectionID=" & sID & " "
strSQL = strSQL & "AND Body LIKE "
For item = 0 to UBound(objArray)
strSQL = strSQL & "'%" & objArray(item) & "%' "
If item < UBound(objArray) Then
strSQL = strSQL & " AND "
End if
Next
|
|
#2
October 17th, 2002, 12:49 PM
|
|||
|
|||
|
SELECT * FROM table WHERE condition AND condition AND condition AND condition
etc..
|
|
#3
October 17th, 2002, 12:53 PM
|
|||
|
|||
|
er missread your question sorry
 what you are doing is essentially correct, I'm really not sure where you are having a problem. Your loop is only appending " AND " to your query as far as i can tell, and not adding a condition. Do a for each x in objArray.Fields , and then... strSql = strSql & " AND body LIKE x.value AND subject LIKE x.value AND title LIKE x.value"
|
|
#4
October 17th, 2002, 01:08 PM
|
|||
|
|||
|
The current loop is for the keywords entered in the search box. (I should have put in the full code).
So I guess what I should do is instead of saying "AND BODY ..." I would do a count of the fields in my table, create an array of the field names then do a For i = 0 to objfieldsarray, get the field name, add an "OR"? , then use next to loop through the fields and the keywords? I assume it would be an OR if I want it to look to see if it's in title, or in body or in subtitle (as opposed to requiring that it be in all three). How do I ensure that it is "WHERE SectionID" & sID AND the keyword appears in body, title or subtitle. i.e. I want section 4 AND (body=keyword or title = keyword or subtitle = keyword) NOT section 4 and body = keyword OR title = keyword OR subtitle = keyword. (which would make the section 4 optional not mandatory) Last edited by aspnewbie : October 17th, 2002 at 01:13 PM.
|
|
#5
October 18th, 2002, 09:10 PM
|
|||
|
|||
|
hhmm....
I think you could do something like: PHP Code:
I do not think you need to loop through the value from "keyword" as the LIKE clause will search for any words which might contain one of them...... I think?! forgive me if I'm wrong..... it's been a while since my SQL lessons!!
|
|
#7
October 18th, 2002, 09:23 PM
|
|||
|
|||
|
Thanks Matt,
I think I tried something similar and got a SQL syntax error statement, but I'll try it as you've set it out and see what happens. Hopefully it will work. The loop was to get at the fact that the user might enter more than one keyword. It loops through all the keywords that the user entered, so I think I still need it, right? If the box was restricted to one term, then I wouldn't need the loop, but I like the idea that the user could enter a number of terms.
|
|
#8
October 18th, 2002, 09:29 PM
|
|||
|
|||
|
yeah, I quite agree that you need more than one keyword search.... but I thought that it would search each word seperately.... maybe not.....?
what was the exact SQL you used when you got the Syntax error.... if you post it we can help more on that!
|
|
| Viewing: Dev Articles Community Forums > Programming > ASP Development > Searching More than One Field |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
