CodeIgniter Forums
Find words form same column in Database (Search) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: Find words form same column in Database (Search) (/showthread.php?tid=63334)

Find words form same column in Database (Search) - ardavan - 10-20-2015

Hi,

im not sure my thread subject is clear or not. anyway I'm creating a search in my project.
Now I'm getting the words from search input into an array

User will key in ( php programming language ).

PHP Code:
$words = explode(' ', $search_text); 

Code:
$words = array('php', 'programming', 'language');

Now i need to find any TITLE match with 'php' or 'programming' or 'language'.

my DataBase is like this
Code:
TITLE         |   DESC   |   STATUS
====================================
php coding    |   blabh  |   LIVE
codeigniter 3 |   blaaaa |   LIVE
all language   |   blaaaa |   LIVE

I'm expecting to get the result like this
Code:
TITLE         |   DESC   |   STATUS
====================================
php coding    |   blabh  |   LIVE
all language   |   blaaaa |   LIVE
"Because we have php in the first title and we have language in the second title"

Im trying to get expected result like this
PHP Code:
$this->db->where_in('TITLE', $words); 

The Issue is i can only get one of my rows. (php coding)

How to make it?
Any idea?

Thanks a lot guys.

RE: Find words form same column in Database (Search) - pdthinh - 10-20-2015

(10-20-2015, 09:28 PM)ardavan Wrote: Hi,

im not sure my thread subject is clear or not. anyway I'm creating a search in my project.
Now I'm getting the words from search input into an array

User will key in ( php programming language ).


PHP Code:
$words = explode(' ', $search_text); 

Code:
$words = array('php', 'programming', 'language');

Now i need to find any TITLE match with 'php' or 'programming' or 'language'.

my DataBase is like this

Code:
TITLE         |   DESC   |   STATUS
====================================
php coding    |   blabh  |   LIVE
codeigniter 3 |   blaaaa |   LIVE
all language   |   blaaaa |   LIVE

I'm expecting to get the result like this

Code:
TITLE         |   DESC   |   STATUS
====================================
php coding    |   blabh  |   LIVE
all language   |   blaaaa |   LIVE
"Because we have php in the first title and we have language in the second title"

Im trying to get expected result like this

PHP Code:
$this->db->where_in('TITLE', $words); 

The Issue is i can only get one of my rows. (php coding)

How to make it?
Any idea?

Thanks a lot guys.

You could use like() and or_like()


PHP Code:
$this->db->like('TITLE', $words[0]);

for ($i = 1; $i < count($words); $i++)
{
    $this->db->or_like('TITLE', $words[$i]);
} 


RE: Find words form same column in Database (Search) - ardavan - 10-20-2015

(10-20-2015, 09:53 PM)pdthinh Wrote: You could use like() and or_like()




PHP Code:
$this->db->like('TITLE', $words[0]);

for ($i = 1; $i < count($words); $i++)
{
    $this->db->or_like('TITLE', $words[$i]);
} 

thanks for your help. i just needed to add
PHP Code:
$this->db->where('STATUS','Live'); 
before
PHP Code:
$this->db->or_like('TITLE', $words[$i]); 


RE: Find words form same column in Database (Search) - pdthinh - 10-20-2015

(10-20-2015, 10:38 PM)ardavan Wrote: thanks for your help. i just needed to add


PHP Code:
$this->db->where('STATUS','Live'); 
before


PHP Code:
$this->db->or_like('TITLE', $words[$i]); 

You could also do this:

PHP Code:
        $this->db->from('table')
            ->group_start()
                ->like('title', 'php')
                ->or_like('title', 'codeigniter')
            ->group_end()
        ->where('status', 'live');
        echo $this->db->get_compiled_select(); 


RE: Find words form same column in Database (Search) - lazyfox - 10-21-2015

For detailed information, please visit this https://www.codeigniter.com/user_guide/database/query_builder.html

RE: Find words form same column in Database (Search) - ardavan - 10-22-2015

but I'm wonder why this is not working!
PHP Code:
$this->db->where_in('TITLE', $words); 


RE: Find words form same column in Database (Search) - ignitedcms - 10-23-2015

Give more of an example.

Sample input, expected output, actual output, and of course your code.

RE: Find words form same column in Database (Search) - PaulD - 10-23-2015

where_in doesn't work because it is looking for an exact match to the array values. It means select titles that are in this array, not titles that have some bits found in this array.

So I am actually surprised you got any results at all. Unless the rest of your query gave you a result, you should have had a null result from that, since none of your titles are in that array you used as an example.

pdthinh gave the right way to do it.

The way to do what you are trying to do is with the like and or_like statements, with a loop through your search terms. So use the loop from pdthinh's solution and the groups from his later solution and that will do it.

Best wishes,

Paul.

RE: Find words form same column in Database (Search) - Kirkja - 10-26-2015

If you're not married to the active record approach, I suggest you craft the SQL using RLIKE which can take wildcards and be chained together. A simple loop could build out the clause you need from your array items.

RE: Find words form same column in Database (Search) - PaulD - 10-26-2015

@Kirkja
I never knew about RLIKE, but wow, what a fantastic operator. I did a google on it and it has some fabulous properties.

Note to self: Learn more about MySql.

Best wishes,

Paul.