View Full Version : Searching on 2 fields
10-15-2003, 10:31 AM
Ok - this is more of an sql question...
If I have a search box where a user can enter a name of a customer, I want it to pull back the details of that customer - simple.
But in the database the customers name is 2 fields - firstname and surname...
How could I make it search for:
'Joe' and return all the Joe
'Bloggs' and return all the Bloggs
'Joe Bloggs' and return all the Joe Bloggs
I can get it to do the first 2 but when typeing the full name it doesn't return anything - do you need to concat the firstname and surname somehow?
If i understand it correctly then you need something like
select variables from table where firstnamevariable='Joe' and lastnamevariable='Bloggs'
--> returns records where both conditions are true
select variables from table where firstnamevariable='Joe' or lastnamevariable='Bloggs'
--> will return the records that meet your 3 examples. at least one condition must be true
(or replace the names with variables, of course)
10-15-2003, 12:41 PM
Can I have 2 selects in one call?
I am using the LIKE command also...
If you use "LIKE" operator, then there is no point in also using the "=" operator.
Records that are selected with
select ... where var='test'
will also be selected by
select ... where var LIKE '%test%'
If you do need to run two selects and you want them returned as one recordset, then you can use UNION. Like
select ID, name from books where author LIKE '%john%' UNION select ID, name from CD where distributor='JackPublishing'
But fot your situation ...
select ... where var='test' UNION select ... where var LIKE '%test%'
will return just the same as
select ... where var LIKE '%test%'
since UNION will not return duplicate rows, unless you use UNION ALL.
select ... where var='test' UNION ALL select ... where var LIKE '%test%'
will return all records from the first select twice.
If you use union, then both selects must have the same number of variables (in the same order and meaning). Just post back if you need more info on UNION
10-15-2003, 09:23 PM
Why not have two form fields then:
Select ....(whatever fields you need)
where FirstName = Trim(Request.Form("txtFirstName"))
and SurName = Trim(Request.Form("txtLastName"))
You could also use Like to get a fuzzy match on firstname and/or
10-15-2003, 10:07 PM
Really it needs to be one text box that will search on any part of the name...
so if you want 'Joe Bloggs'
you could type 'Jo' and it would return all people whose firstname starts with 'Jo' and last names start with 'Jo' - e.g. Joe and Jones
Is there a way to concat the first name and last name in the select then do a like on it?
my SQL statement is:
strSQL = "SELECT [FirstName] & " " & [SurName] FROM tblCustomer WHERE [FirstName] & " " & [SurName] LIKE = '" & Request.Form("txtSearch") & "' ORDER BY FirstName;"
but that doesn't work!
10-15-2003, 10:22 PM
Ok - using a table of mine:
select * from mytable
where (CLMT_CITY_TXT || CLMT_ST) LIKE 'WAC%T%'
city and state are two separate columns.
The string you are comparing with could be from a single textbox.
In the Case, someone might have entered WACO,TX.
You will have to determine how to break that name field up and
how much of the two resulting pieces are significant and what to wildcard to build the argument used in the Like phrase.
What if you have JOHN SMITH in the database columns and they
John R. SMith or John Smith, Sr. ...etc.???
10-16-2003, 06:53 AM
searchString = Request.Form("txtSearch")
strSQL = "SELECT [FirstName],[SurName] FROM tblCustomer WHERE [FirstName] LIKE '" & searchString & "%' OR [SurName] LIKE = '" & searchString & "%' ORDER BY FirstName;"
10-16-2003, 08:51 AM
glenn - tried your code and got:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '[FirstName] LIKE 'jo%' OR [SurName] LIKE = 'jo%''.
when i searched for jo
are the '%' in the right places?
Ah. Now i get it. You have one searchstring you want to search for in two variables.
Glenngv's code will work as long as the type in only one word.
If they would type in "Joe Blogg" then no records will be returned. In this case you should run a seperate select for each word, or include each word as 2 seperate conditions in the where clause. Like
dim searchterms, item, cond
searchterms = split(request.form("searchstringvariable"), " ") 'splits the searchstring on spaces and stores each word in an array
for each item in searchterms
cond = cond & " OR firstnamevariable LIKE '%" & item & "%'" OR lastnamevariable LIKE '%" & item & "%'" 'so for each word, the condition is added
sql = "SELECT variables FROM table WHERE x=x" & cond 'the x=x is because the condoition starts with OR
Try my code. (previous post)
In glenngv code you have "LIKE =" which causes the problem. It's either "LIKE" (match with wildcards, like you need) or "=" (match for the exact value)
10-17-2003, 04:40 AM
Sorry, that was a typo. I just copied and pasted holty's code and edited it directly. :D