CODEWITHSUNDEEP
phpmysqlsqlsearch
andreaem
andreaem

Reputation: 1655

PHP MySQL: Search query work only with existing full term

I got a search query fully work but only if i use the correct term (i.e. 'iPhone 6 Plus' but if i search only 'iPhone' it will return nothing.

My search query is: SELECT * FROM products WHERE name LIKE '" . $term . "' OR brand LIKE '" . $term . "' LIMIT 0 , 30"

So how I can create a better query that can search better?

Thanks a Lot to all who can help.

Upvotes: 1

Views: 68

Answers (1)

Jay Blanchard
Jay Blanchard

Reputation: 34416

In order to do matches on partial search terms you have to add some wildcards to your queries, the %:

SELECT * FROM products WHERE name COLLATE UTF8_GENERAL_CI LIKE '%" . $term . "%' OR brand COLLATE UTF8_GENERAL_CI LIKE '%" . $term . "%' LIMIT 0 , 30"

You could just add COLLATE UTF8_GENERAL_CI to your column definitions and then you would not have to modify your queries. For example:

ALTER TABLE products MODIFY COLUMN name VARCHAR(...) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI.

If you need something more complex you will want to look a fulltext searches.

The Latin collation (LATIN1_GENERAL_CS)is one of those which is known to work well with case insensitive searches. If the one I specified (I try to always use UTF-8) doesn't work, substitute the Latin collation.

Upvotes: 2

Related Questions