A search field that looks in several columns

Databases
1

I am trying to streamline my PHP code by making only one search field instead of having several. This one field will allow you to type in a person’s ID, part of their name, email, etc. Here is my query:

select date_format(registerDate, ‘%m-%d-%Y’) as registerDate, uID, firstname, lastname, concat( firstname, ’ ', lastname ) as fullname,
city, state, visited, date_format(last, ‘%m-%d-%Y’) as last, member, subscription, totalSearches from users
where uID = ‘smith’ or
membernumber = ‘smith’ or
email like ‘%smith%’ or
phone like ‘%smith%’ or
city like ‘%smith%’ or
concat( firstname, ’ ', lastname ) like ‘%smith%’
order by uID;

The problem is this query is returning lots of results. It should only be showing users with the last name of smith. Can someone point me in the right direction?

Thanks!

2

if that’s what you really want, then don’t look for ‘smith’ in any column other than lastname

3

But how else can I have one search field check the contents of multiple columns? And can you explain why my query above is not working?

Thanks!

4

Do you need to be able to search all fields from only one search box, or would it work to have it where the user can select which field they want to search on from a selection/drop-down box, and then have your query search that field for the string they enter in a text box?