User search multiple values with sql like query

Question

Using PHP and Databases, I have a simple search query statement :

SELECT * FROM products WHERE LOWER(product_title) LIKE '%$search%';

E.g product title: 'Red Apples Tree'

e.g search 1: '

red apples

outputs the title successfully

e.g search 2:

'red tree'

does not output anything, but the words are in the title.

Where is the problem in this?

`LIKE` looks for exactly matches. It sounds like you want a full-text search. Look in the documentation for that functionality in the database you are using. — Gordon Linoff, Feb 09 '21 at 12:22
Yes I searched for full-text search but there is so much about this. Im specifically using phpMyAdmin — mikey1091, Feb 09 '21 at 12:23
phpMyAdmin is not a DB, it is a UI for accessing mysql. Start with https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html — user3783243, Feb 09 '21 at 12:24
of course, they are in the table, but '%Red Apples Tree%' means that in the begging and after the word 'tree' other words can be present,so it will work for 'gold Red Apples Tree' or 'Red Apples Tree small', but not for the 'Red Orange Apple Tree' — Sergey, Feb 09 '21 at 12:25
_“I searched for full-text search but there is so much about this”_ - and? Just because a topic might be a bit complex, does not absolve you from your responsibility to go and try to properly read up on it first of all. — CBroe, Feb 09 '21 at 12:27
You could of course also split `red tree` into individual words, and then add more than one LIKE comparison to your WHERE clause, connected with AND (if you want matches that contain all those words), or OR (if you want to find records that contain either one.) — CBroe, Feb 09 '21 at 12:28
@CBroe Haha okay that cheered me up, oh my. And to your second comment, I tried to put search query as an array to get individual words, using implode etc. — mikey1091, Feb 09 '21 at 12:44
And, trouble with that? Then you’ll need to show us what exactly you tried, not just give a vague and ambiguous verbal description. — CBroe, Feb 09 '21 at 12:45
@CBroe not at all, but I am very happy that you gave me some tips, i will look into splitting the searches into individual words. — mikey1091, Feb 09 '21 at 12:48

score 0 · Answer 1 · answered Feb 09 '21 at 12:27

0

What are you looking for is "Full-Text Search".

Here is an example for your question for MySQL:

Example Query

SELECT * FROM products WHERE MATCH(product_title)
AGAINST("Red Apples Tree" IN NATURAL LANGUAGE MODE)

User search multiple values with sql like query

1 Answers1