Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching on 2 fields

    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?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can I have 2 selects in one call?

    I am using the LIKE command also...

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #5
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #7
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,048
    Thanks
    0
    Thanked 251 Times in 247 Posts
    searchString = Request.Form("txtSearch")
    strSQL = "SELECT [FirstName],[SurName] FROM tblCustomer WHERE [FirstName] LIKE '" & searchString & "%' OR [SurName] LIKE = '" & searchString & "%' ORDER BY FirstName;"

  • #9
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #10
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    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

  • #11
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  • #12
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,048
    Thanks
    0
    Thanked 251 Times in 247 Posts
    Sorry, that was a typo. I just copied and pasted holty's code and edited it directly.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •