-1

When i was learning search function with PHP and MySQL there was a query code : 

SELECT id, category, location
FROM table
WHERE
(
    category LIKE '%keyword%'
    OR location LIKE '%keyword%'
)

I couldn't understand by % is used in the query and with the escape characters, is the %keyword% has to changed with %$variable%.

Mihir Ujjainwal
  • 140
  • 2
  • 13

1 Answers1

2

% is a placeholder for an arbitrary string.

So if you have something WHERE category LIKE '%keyword%' this will return all categories that contain the word 'keyword'.

I guess what you are looking for is to find something that contains a given keyword, and the keyword is in your variable, so that would be WHERE category LIKE '%$variable%', with $variable being the variable that holds the keyword. There are some issues of course with this, like the keyword cannot contain % itself, otherwise it will have to be escaped and there is the issue of possible SQL injection, which should be approached by using prepared statements, but those issues seem a bit outside of the scope of the question.

Community
  • 1
  • 1
Janick Bernet
  • 20,544
  • 2
  • 29
  • 55
  • and what's with the variable (see question). – Mihir Ujjainwal Feb 14 '15 at 20:18
  • so, then should i use % for variables or not ? – Mihir Ujjainwal Feb 14 '15 at 20:33
  • I don't understand what you meant by that, sorry. If you are worried about escaping, then you probably should use prepared statements. – Janick Bernet Feb 14 '15 at 20:35
  • i mean then i should use `WHERE category LIKE '%$variable%'` or `WHERE category LIKE '$variable'` – Mihir Ujjainwal Feb 14 '15 at 20:39
  • That depends on what you want. But at the end, the resulting query will be the content of your variable put in the string, so if your variable contains `bunny` then `WHERE category LIKE '%$variable%'` will become `WHERE category LIKE '%bunny%'`. You could also add the `%` to the string in the $variable first and leave it out in the WHERE and have the same resulting string – Janick Bernet Feb 14 '15 at 20:42