Monday, 9 September 2013

MySQL string searching technique?

MySQL string searching technique?

In my android app, I have a search field where the user can type
something. They can type anything they want like names, addresses, phone
numbers, emails, etc... in my database.
I want it to be like an all in one search bar in google search, so they
can type combinations of things like a name with an address for example.
The problem is how would I do a search where the search text can contain
multiple phrases?
For example if the user searched "Jack Daniel jdaniel@hotmail.com", then I
would want mysql select statement to end up being like:
select id from members
where firstname=`Jack` or firstname=`Daniel` or
firstname=`jdaniel@hotmail.com`
or lastname=`Jack` or lastname=`Daniel` or lastname=`jdaniel@hotmail.com`
or email=`Jack` or email=`Daniel` or email=`jdaniel@hotmail.com`
Basically each phrase of the search text needs to be compared with all the
columns.
Does MySQL have a built in mechanism to do this easily, or do I have to
parse the string in java and manually build the sql statement?
Thanks

No comments:

Post a Comment