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

try to use regular expression in query

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

Answer 2

Found this question on Google, so I figure that some people may still stumble upon this so here's my pretty inelegant attempt:

SELECT * FROM products
WHERE product_name LIKE 'BLA %' #First word proceeded by more words
OR WHERE product_name LIKE '% BLA' #Last word preceded by other words
OR WHERE product_name LIKE '% BLA %' #Word in between other words
OR WHERE product_name = 'BLA'; #Just the word itself

Not sure about the efficiency or if this covers all cases, so feel free to downvote if this is really inefficient or too inelegant.

Answer 3

Use equals (=)?

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

If you are looking to match EXACT words don't use LIKE.

EDIT: That clears things up a bit then. Just add a space after the search term. Or even add the hyphen (-) if that is always in the search term.

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

Answer 4

To exact match you should use:

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