Search code examples
phpmysqlsearchsql-like

PHP MySQL Search Combined Columns

I had just finished my search functionality for a users system when I found out that it didn't search the way I wanted it to.

If I have a datebase table with 2 columns called 'fname' and 'lname'.

In one row, 'fname' has a value of 'Ashley' and 'lname' has a value of 'Staggs'.

I can search for either 'Ashley' or 'Staggs' and I will get the results correctly, but if I search for 'Ashley Staggs', no results are displayed.

How would I do this properly?

My SELECT query is as follows:

SELECT * FROM `users` WHERE fname LIKE '%" . protect($_GET['s']) . "%' OR lname LIKE '%" . protect($_GET['s']) . "%'

I knew something like this would happen, but this time I can't figure it out.

Thanks, Ashley

Solution

  • 'Ashley Staggs' is neither in fname, nor in lname, so your request doesn't return anything. You could try to concatenate your MySQL fields:

    SELECT * FROM `users` WHERE fname LIKE '%" . $_GET['s'] . "%' OR lname LIKE '%" . $_GET['s'] . "%' OR CONCAT(fname, ' ', lname) LIKE '%" . $_GET['s'] . "%'

    [EDIT] Even better:

    SELECT * FROM `users`
WHERE REPLACE(CONCAT(fname, lname, fname), ' ', '')
LIKE '%" . str_replace(' ', '', protect($_GET['s'])) . "%'