• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Search function not displaying results for both names

I have a search function where you can search someones name. The first name and surname are in different database columns so i'm using this...

WHERE fname LIKE '%$search%' OR lname LIKE '%$search%'

It's not working though. Say for example 'John Smith' is one of the searchable names. If you search 'John' or 'Smith' he displays for if you search 'John Smith' he doesn't.

Any idea how I can get this too work?
0
BrighteyesDesign
Asked:
BrighteyesDesign
1 Solution
 
Robert SaylorSenior DeveloperCommented:
what you have to do is 1st remove the space from your search string then concat the two fields and do a like search.
0
 
GaryCommented:
Split the name so you can add them separately to the sql
Your current sql requires the column/s contain john smith exactly in either column (and ignore anything before or after the term in the column.)
Or use CONCAT
WHERE CONCAT(fname,' ',lname) LIKE '%$search%'
Though you probably don't want the wild card for this method unless there are other names, e.g. middle names
0
 
Ray PaseurCommented:
Another possibility...

WHERE fname LIKE '%$search%' OR lname LIKE '%$search%' OR CONCAT(fname,' ',lname) LIKE '%$search%'
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now