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

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?

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.

You'll need to do this:, as SQL will choke on the (name or surname) part

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

I'm not sure what language your using, but in psuedocode, you can make this a little easier with a simple function

query = [whatever you are searching for]
var columns = array('name', 'surname')
var where = array
foreach columns as column
    where[] = column + ' like "%' + query + '%"'

sql = "select * from tblClients WHERE " + join(where, ' OR ');

//where join joins values of an array into a string

I recommend using UNIONs over ORs - ORs are notorious for poor performance, and risk maintenance issues if not properly understood:

SELECT c.* FROM tblClients c WHERE c.name LIKE '%john%'
UNION
SELECT c.* FROM tblClients c WHERE c.surname LIKE '%john%'

Keep in mind that UNION will return a distinct list - duplicates will be removed, but it will perform slower than using UNION ALL (which will not remove duplicates). Use what suits your purpose