Welcome Guest, Not a member yet? Register   Sign In
Databases: searching for a word in entries containing multiple words using 'like'
#1

(This post was last modified: 05-13-2021, 09:28 AM by JernauGurgeh.)

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!
Reply
#2

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...)
CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/
Reply
#3

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.
Reply
#4

MySQL also has an rlike which is regex.

RLIKE operator in MySQL
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#5

@JernauGurgeh

Why not use an AJAX Search?

https://DasaBookCafe.tk

Tutorial here:

https://johns-jokes.com/downloads/sp-e/j...earch/pdo/
Reply
#6

Hi,

raw SQL for you to evaluate..  Smile

Code:
SELECT * FROM test
WHERE LOWER(words) LIKE '% art%' OR LOWER(words) LIKE 'art%'
Reply
#7

(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!
Reply
#8

(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.
Reply
#9

(05-14-2021, 02:40 AM)paulkd Wrote: Hi,

raw SQL for you to evaluate..  Smile

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!
Reply
#10

(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"
); 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB