MySQL - How to search for exact word match using L

Question

I'm using this query to select data:

mysql_query("SELECT * FROM products WHERE product_name LIKE '%".$search."%'");

The only problem is, that it sometimes selects more, than I would like.

For example, I would like to select product "BLA", but my query select product "BLABLA" as well. To be clear, if i wanted to select "Product 1", I don't want the query to select "Product 11".

Does anybody know how to manage that?

Thanks.

Answer 1

Then don't use LIKE, but search for equality.

ie.

mysql_query("SELECT * FROM products WHERE product_name = '".$search."'");

BTW I hope you sanitize/escape $search before using it in a query.

Answer 2

you can use select query like this ,i also use in cakePHP and it's helpful.

Select * from `users` where username COLLATE latin1_general_cs LIKE '%$email%'

Answer 3

Try using regular expressions:

SELECT 
    *
FROM
    `products`
WHERE
    product_name regexp '(^|[[:space:]])BLA([[:space:]]|$)';

Answer 4

SELECT  *
FROM    products
WHERE   product_name = 'BLA'

will select exact BLA

SELECT  *
FROM    products
WHERE   product_name LIKE 'BLA%'

will select BLADDER and BLACKBERRY but not REBLAND

To select BLA as the first word of the string, use:

SELECT  *
FROM    products
WHERE   product_name RLIKE '^Bla[[:>::]]'
        AND product_name LIKE 'Bla%'

The second condition may improve your query performance if you have an index on product_name.

Answer 5

Do you just want to search on word boundaries? If so a crude version might be:

SELECT * FROM products WHERE product_name LIKE "% foo %";

Or you could be a bit cleverer and look for word boundaries with the following REGEXP

SELECT * FROM products WHERE product_name RLIKE "[[:<:]]foo[[:>:]]";

Answer 6

Remove LIKE keyword and use = for exact match

EDIT

do not forgot to escape user input using mysql_real_escape_string otherwise your query will fail if some one enter quotes inside the input box.

$search=mysql_real_escape_string($search);
mysql_query("SELECT * FROM products WHERE product_name='".$search."'");