sql - Checking if a string is found in one of multiple columns in mySQL

Question

I need to check if a string is found in one or more columns.

Basically, I have a program which lets you checks multiple fields (name, surname, etc...)

If both name and surname are checked and the user enters just the name, for example chris it would be easy to check it in mySQL with the LIKE parameter like this:

select * from tblClients WHERE name LIKE '%john%';

This obviously works. However, what I need to do is that if both name and surname are checked it would do something like this:

select * from tblClients WHERE (name or surname) LIKE '%john%';

I want that if this logic is made when there is a client named john doe, the second command will still find that client.

I tried this command and no syntax errors were found, however, 0 results where returned.

Any suggesstions please?

Answer 1

Can't you just use separate WHERE clauses, such as:

SELECT * FROM tblClients
WHERE (name LIKE '%john%')
OR (surname LIKE '%john%')

You're having to amend the SQL based on which columns the user selects anyway.