search result

adamhw · February 13th, 2010, 05:22 AM

Hi there

If I wanted to do a search by âfirstname surnameâ what would the sql be like? Would I need to join them with a +?

For example I want to search for âdavid smithâ rather than just âsmithâ

At the moment itâs like this:

if Trim(request("txtSearch")) <> Empty then

SQLSearch = SQLSearch & " product.firstname LIKE '%"& Replace(request("txtSearch"), "'", "''") &"%' OR "
SQLSearch = SQLSearch & " product.productname LIKE '%"& Replace(request("txtSearch"), "'", "''") &"%' OR "

etc

But if I wanted to search by product.firstname and product.surname as one?

Would it be something like this:

SQLSearch = SQLSearch & " product.firstname LIKE '%"& Replace(request("txtSearch"), "'", "''") &"%' + product.productname LIKE '%"& Replace(request("txtSearch"), "'", "''") &"%' OR "
SQLSearch = SQLSearch & " product.firstname LIKE '%"& Replace(request("txtSearch"), "'", "''") &"%' OR "
SQLSearch = SQLSearch & " product.surname LIKE '%"& Replace(request("txtSearch"), "'", "''") &"%' OR "

etc?

Thanks in advance.

om_prakash · February 15th, 2010, 08:01 AM

Correct, If you want to search by firstname or surname then

Code:

SearchText = Trim(request("txtSearch")) & ""
SearchText = Replace(SearchText, "'", "''")

if SearchText <>"" then
 'If you want to search for firstname or last name then:
 SQLSearch = SQLSearch & "(firstname LIKE '%"& SearchText  &"%' OR surname LIKE '%"& SearchText  &"%') " 
 
 'If you want to combine these two fields then (Remove space beteen first/surname if require.
 SQLSearch = SQLSearch & "(firstname + ' ' + surname LIKE '%"& SearchText  &"%') " 
end if