CODEWITHSUNDEEP
mysqlsearch
Ray
Ray

Reputation: 3060

mysql search across joined fields

I have customer table with fileds for first name and last name plus other details.

In a form a user can enter a search which I have working but on unique fiels only eg searches for john in any of first name or last name. This all works ok

However I dont know how to search if a user enters john smith which would be a combination of two fields. How can I do this search with my current data structure?

Ive tried adding a field in the select statement using:

Concat_ws (' ', firstname, lastname) as fullname

But I cant use where on this generated field and throws an error

Should I create a new field to contain full name?

Thank you

Upvotes: 0

Views: 58

Answers (2)

Vatev
Vatev

Reputation: 7590

It depends on which combinations of fields you want. For your example firstname = 'john' AND lastname='smith' would do the job, but its probably not what you need.

The best solution would be to use a fulltext search.

Upvotes: 1

Akhil
Akhil

Reputation: 2602

You don't use aliases in where condition. instead use the complete function

e.g. 

where Concat_ws(' ', firstname, lastname) like '%<your search>%'

But, yes, it is good idea to create a new field to optimize performance
And make sure you dont have space after the function name

Upvotes: 1

Related Questions