...

View Full Version : Searching on 2 fields



holty
10-15-2003, 09: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?

raf
10-15-2003, 10:10 AM
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)

holty
10-15-2003, 11:41 AM
Can I have 2 selects in one call?

I am using the LIKE command also...

raf
10-15-2003, 01:39 PM
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

fractalvibes
10-15-2003, 08:23 PM
Why not have two form fields then:
txtFirstname
txtLastName

and then

Select ....(whatever fields you need)

from sometable

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
surname

fv

holty
10-15-2003, 09: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!

any ideas?

fractalvibes
10-15-2003, 09: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
Enter:

John R. SMith or John Smith, Sr. ...etc.???

fv

glenngv
10-16-2003, 05:53 AM
searchString = Request.Form("txtSearch")
strSQL = "SELECT [FirstName],[SurName] FROM tblCustomer WHERE [FirstName] LIKE '" & searchString & "%' OR [SurName] LIKE = '" & searchString & "%' ORDER BY FirstName;"

holty
10-16-2003, 07:51 AM
glenn - tried your code and got:

Error Type:
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?

raf
10-16-2003, 08:10 AM
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
next

sql = "SELECT variables FROM table WHERE x=x" & cond 'the x=x is because the condoition starts with OR

raf
10-16-2003, 08:12 AM
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)

glenngv
10-17-2003, 03:40 AM
Sorry, that was a typo. I just copied and pasted holty's code and edited it directly. :D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum