+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Databases: searching for a word in entries containing multiple words using 'like' (/showthread.php?tid=79217)
Pages:
1
2
Databases: searching for a word in entries containing multiple words using 'like' - JernauGurgeh - 05-13-2021
I'm trying to set up a search using like to find words in a table column 'title'.
At first, I was using the following...
Code:
$this->db->like('title',$search);It worked fine, but I wanted to limit searches to the beginnings of words, so I'm using the following...
Code:
$this->db->like('title',$search,'after');However, some entries might contain multiple words, so I want it to search for all words, not just the first word in an entry.
For example, say I have the following entries...
Art
Art Therapy
Driving Karts
Participation
Theory Of Art
Writing Articles
...and I want the search term of 'art' just to return the following...
Art
Art Therapy
Theory Of Art
Writing Articles
How do I achieve that? Will I have to resort to using a query with REGEXP?
Unfortunately, I'm ashamed to say that regular expressions are something that I've never really got my head around!
RE: Databases: searching for a word in entries containing multiple words using 'like' - includebeer - 05-13-2021
I think you won't have a choice to use regex. I found this website a long time ago and it saved my life more than once, because I too I find regex complicated: https://regex101.com
You can test you regex, and it can even generate code for you (php, js, java...)
RE: Databases: searching for a word in entries containing multiple words using 'like' - wdeda - 05-13-2021
In the example below I have a 'names' table and I want to find all names that contain' Taylor':
PHP Code:
$sql = ("SELECT * FROM `names` WHERE` name` LIKE '%Taylor%' ORDER BY `id` ASC");
The percentage symbol '%' indicates that the search must be carried out in both directions, that is: from left to right and the reverse.
RE: Databases: searching for a word in entries containing multiple words using 'like' - InsiteFX - 05-13-2021
MySQL also has an rlike which is regex.
RLIKE operator in MySQL
RE: Databases: searching for a word in entries containing multiple words using 'like' - John_Betong - 05-13-2021
@JernauGurgeh
Why not use an AJAX Search?
https://DasaBookCafe.tk
Tutorial here:
https://johns-jokes.com/downloads/sp-e/jb-ajax-search/pdo/
RE: Databases: searching for a word in entries containing multiple words using 'like' - paulkd - 05-14-2021
Hi,
raw SQL for you to evaluate..
Code:
SELECT * FROM test
WHERE LOWER(words) LIKE '% art%' OR LOWER(words) LIKE 'art%'RE: Databases: searching for a word in entries containing multiple words using 'like' - JernauGurgeh - 05-14-2021
(05-13-2021, 04:02 PM)includebeer Wrote: I think you won't have a choice to use regex. I found this website a long time ago and it saved my life more than once, because I too I find regex complicated: https://regex101.com
You can test you regex, and it can even generate code for you (php, js, java...)
Thanks for the pointer, that looks handy!
RE: Databases: searching for a word in entries containing multiple words using 'like' - JernauGurgeh - 05-14-2021
(05-13-2021, 05:13 PM)wdeda Wrote: In the example below I have a 'names' table and I want to find all names that contain' Taylor':
PHP Code:$sql = ("SELECT * FROM `names` WHERE` name` LIKE '%Taylor%' ORDER BY `id` ASC");
The percentage symbol '%' indicates that the search must be carried out in both directions, that is: from left to right and the reverse.
Thanks, but that’s basically the SQL equivalent of the first bit of CI code I posted, isn’t it? It will output all entries that contain the search term, even if it’s in the middle of a word.
RE: Databases: searching for a word in entries containing multiple words using 'like' - JernauGurgeh - 05-14-2021
(05-14-2021, 02:40 AM)paulkd Wrote: Hi,
raw SQL for you to evaluate..
Code:SELECT * FROM test
WHERE LOWER(words) LIKE '% art%' OR LOWER(words) LIKE 'art%'
That looks like it might do the job! Pretty simple and pretty obvious really. I really need to brush up on my SQL as well as regex.
I’m not at the office at the mo, so I’ll check it out after the weekend and report back.
Many thanks!
RE: Databases: searching for a word in entries containing multiple words using 'like' - wdeda - 05-14-2021
(05-14-2021, 12:21 PM)JernauGurgeh Wrote:(05-13-2021, 05:13 PM)wdeda Wrote: In the example below I have a 'names' table and I want to find all names that contain' Taylor':
PHP Code:$sql = ("SELECT * FROM `names` WHERE` name` LIKE '%Taylor%' ORDER BY `id` ASC");
The percentage symbol '%' indicates that the search must be carried out in both directions, that is: from left to right and the reverse.
Thanks, but that’s basically the SQL equivalent of the first bit of CI code I posted, isn’t it? It will output all entries that contain the search term, even if it’s in the middle of a word.
Sorry, you are right, for the result you intended:
PHP Code:
$sql = ("SELECT * FROM `names`
WHERE 'name'='art'
OR 'name' LIKE '% art'
OR `name` LIKE 'art %'
OR 'name' LIKE '% art %'
ORDER BY `id` ASC");