MySQL using LIKE in query

Question

I am trying to come up with a query that will generate results based on letter patterns of a string contained in the database.

Basically this is what I am trying to do:

Test 1: "T" => mysql returns: toys, tofu, telephones, turkeys, televisions, topaz
Test 2: "To" => mysql returns: toys, tofu, topaz
Test 3: "Toy" => mysql returns: toys
Test 4: "Toys" => mysql returns: toys

This is what i tried so far ($searchword corresponds to test case string):

$results = mysql_query("SELECT * FROM products WHERE prod_name LIKE %".$searchword."%");

Your term is a string and needs to be wrapped in single quotes. '%".$searchword."%' - additionally, % is a directional wildcard, so %T% would also match the word "match" since it has a T in it - plus, switch to PDO. mysql_ is deprecated — Kai Qing
– Kai Qing, Commented Dec 4, 2012 at 20:51

jcho360 · Accepted Answer · 2012-12-04 21:06:01Z

3

Add the quotes ('%text%") to your query

$results = mysql_query("SELECT * FROM products 
WHERE prod_name LIKE '%".$searchword."%'");

@KaiQing comments that you could use this instead,and he's right if you just want the words that start with the 'searchword':

$results = mysql_query("SELECT * FROM products 
WHERE prod_name LIKE '".$searchword."%'")

I.E: if searchword=TOY, this query WHERE prod_name LIKE '".$searchword."%' will display TOYS,TOYZ,TOYXXX, etc but wont display XTOY, TITOY,ZTOY, etc.

If you want to find out more about how to use like take a look here: http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html

edited Dec 4, 2012 at 21:06

answered Dec 4, 2012 at 20:51

jcho360

3,7491 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kai Qing Over a year ago

I think he wants forward direction only. That would be '".$searchword."%' unless he wants %T% to match anything left and right of T.

Kai Qing Over a year ago

Exactly - and in a more real world application, %T% would return "Toys" and "Hot Tub"

Collectives™ on Stack Overflow

MySQL using LIKE in query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related