Issue with query for search bar

Question

I've built a simple search bar for my website, and if my query looks like this, it works great:

$sql = query("SELECT id, firstname, lastname, username, location FROM 
users WHERE firstname LIKE '%" . $search_query . "%' LIMIT 20");

but if i write it like that, it echoes a SQL Syntax error :

$sql = query("SELECT id, firstname, lastname, username, location FROM 
users WHERE firstname, lastname, username, location  LIKE '%" . $search_query . "%'   
LIMIT 20");

The difference between the 2 queries is that the 2nd one will search through multiple columns which is what i need since my users can search either for a name or a city.

How should I re-write it ?

You need a `LIKE` for each column and `OR` them together. – go-oleg Jul 07 '13 at 23:16 — go-oleg, Jul 07 '13 at 23:16

score 1 · Answer 1 · answered Jul 07 '13 at 23:18

You have to repeat the LIKE statement for each field, unfortunately.

SELECT id, firstname, lastname, username, location FROM users 
WHERE firstname LIKE '%" . $search_query . "%'
OR lastname LIKE '%" . $search_query . "%' 
OR username LIKE '%" . $search_query . "%'
OR location LIKE '%" . $search_query . "%'
LIMIT 20

score 1 · Answer 2 · edited May 23 '17 at 12:21

The keyword LIKE does not work like that. Try this:

$like_string = "'%$search_query%'";

$query = "SELECT id, firstname, lastname, username, location
FROM users
WHERE firstname LIKE $like_string OR
lastname LIKE $like_string OR
username LIKE $like_string OR
location LIKE $like_string
LIMIT 20";

$sql = query($query);

As others will tell you, it is smart to account for SQL injection and perform sanity and validity checks before accepting any user data. Take a look at this question on how to prevent SQL injection.

The PDO extension or the mysqli extension is preferred for MySQL and mysql_ functions have been deprecated (as can be seen throughout the documentation).

score 0 · Accepted Answer · answered Jul 07 '13 at 23:19

You need to specify each column's search condition separately:

$sql = query("SELECT id, firstname, lastname, username, location
FROM users
WHERE firstname LIKE '%$search_query%'
OR lastname LIKE '%$search_query%'
OR username LIKE '%$search_query%'
OR location LIKE '%$search_query%'
LIMIT 20");

As you can see, I've removed the concatenation - since you're using double quotes, variables can go directly into the query.

Also, I hope $search_query has been santised, or you're opening yourself upto SQL injection attacks; really, you should be using parameterised queries by now.

Issue with query for search bar

3 Answers3