Multiple Keyword Search

[eluser]rich.a.coy[/eluser]
Hello,

I'm working on a search feature and it works great as long as someone only enters one word. I have a complex SQL query that joins two tables and searches 6 columns using LIKE and also checking a "show" field for each column to remove nonpublic viewable data.

If someone types in two or more words it returns no results. I understand why but don't understand how to fix t. I assume I need to explode the search string but unsure how to actually run the query for each word. I looked in the forums and saw some examples using Active Record but my SQL statement is a little more complex and would prefer to not use Active Record on this query.

Also, where do I need to tell the results to be distinct records only?

Thanks for any help you can offer. I've only been using CI, and MVC, for about a month and these the CI community has been very friendly and helpful.

Here is my Model:

Code:

function search_results() {
        $search_term = $this->input->post('searchbox');
        if ($search_term != "") {
        $sql = "SELECT * FROM user_profiles INNER JOIN users ON user_profiles.user_id = users.id WHERE fname LIKE '%".$this->db->escape_like_str($search_term)."%' AND show_fname = 'e'
                        OR user_profiles.lname LIKE '%".$this->db->escape_like_str($search_term)."%' AND show_lname = 'e'
                        OR user_profiles.country LIKE '%".$this->db->escape_like_str($search_term)."%' AND show_country = 'e'
                        OR user_profiles.state LIKE '%".$this->db->escape_like_str($search_term)."%' AND show_state = 'e'
                        OR user_profiles.city LIKE '%".$this->db->escape_like_str($search_term)."%' AND show_city = 'e'
                        OR users.username LIKE '%".$this->db->escape_like_str($search_term)."%'
                        ";
        $query = $this->db->query($sql);
        return $query->result();
        }
     }

Thanks

Rich

[eluser]Rob Gordijn[/eluser]
when searching, think about 'all', 'any' and 'exact' just like the search option within thsi forum.

I'm not a search query expert, but: you can replace spaces by '%', sure it works, and sure it will kill your performance...

[eluser]rich.a.coy[/eluser]
Thanks Rob,

I understand what you're saying but I want to keep the search super simple on the user side since they are just looking up other users to make friend requests.

Rich

[eluser]Rob Gordijn[/eluser]
ok, my thoughts:

- replacing spaces by a wildcard % is an option, not a very smart due to performance...

- explode on spaces, comma's or whatever, and foreach() the output so you can create one HUGE querie with multiple " ( OR column LIKE %search% AND show_column = 'e' ) "
[for every column you want to search offcourse]

- give the user more power by letting them select what to search (i.e. city or firstname)

cheers

[eluser]rich.a.coy[/eluser]
Rob,

Your second option is what I am envisioning. Can you point me to an example of how that would be coded? Even a simplified example would help. I saw an example on the forum but it used Active Record so it looked too different from my structure to be helpful to me and my limited experience.

I think your third idea would be a good "advanced search" feature and I may add that to this project in the future.

Thanks.

Rich

[eluser]Rob Gordijn[/eluser]
ok, fast reply, untested.

Code:

<?php
$query = 'foo barrrr';
$explode = explode(' ', $search);

$sql = array();
foreach($explode as $word)
{
    $sql[] = " ( `column1` LIKE %".$word."% AND `column1_show = 'e' ) ";
    $sql[] = " ( `column2` LIKE %".$word."% AND `column2_show = 'e' ) ";
}
$query = "SELECT * FROM `table` WHERE ".implode(' OR ', $sql)." ORDER BY `column` ASC";

keep in mind your escapes, check for count() for $explode and $sql, etc etc


[edit]
maybe you can perform the column_show = 'e' part outside the foreach (-:

[eluser]rich.a.coy[/eluser]
Thanks for the guidance.

I think I'm close but I'm getting the following error. (For simplicity I shortened it to just check for first and last name right now.)

The Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''user_profiles' WHERE ( 'fname' LIKE %Rich% AND 'fname_show' = 'e' ) OR ( 'ln' at line 1

SELECT * FROM 'user_profiles' WHERE ( 'fname' LIKE %Rich% AND 'fname_show' = 'e' ) OR ( 'lname' LIKE %Rich% AND 'lname_show' = 'e' ) OR ( 'fname' LIKE %Coy% AND 'fname_show' = 'e' ) OR ( 'lname' LIKE %Coy% AND 'lname_show' = 'e' ) ORDER BY 'fname' ASC

Here is my code:

Code:

function search_results() {    
        $search_term = $this->input->post('searchbox');
        $explode = explode(' ', $search_term);

        $sql = array();
        foreach($explode as $word)
            {
                $sql[] = " ( 'fname' LIKE %".$word."% AND 'fname_show' = 'e' ) ";
                $sql[] = " ( 'lname' LIKE %".$word."% AND 'lname_show' = 'e' ) ";
            }
            $query = "SELECT * FROM 'user_profiles' WHERE ".implode(' OR ', $sql)." ORDER BY 'fname' ASC";
            $results = $this->db->query($query);
            return $results->result();
}

Anything stand out?

[edit]

Mysql Error Number : 1064 refers to using a reserved word incorrectly but everything looks correct to me.

[/edit]

[eluser]danmontgomery[/eluser]
you're using ' when you should be using ` (backtick, on the key with ~)

[eluser]rich.a.coy[/eluser]
I had the back ticks at first and had the same error.

The only thing I added to your code, besides the correct table names and such, was the closing back tick after show_fname and show_lname.

Code:

function search_results() {    
        $search_term = $this->input->post('searchbox');
        $explode = explode(' ', $search_term);

        $sql = array();
        foreach($explode as $word)
            {
                $sql[] = " ( `fname` LIKE %".$word."% AND `show_fname` = 'e' ) ";
                $sql[] = " ( `lname` LIKE %".$word."% AND `show_lname` = 'e' ) ";
            }
            $query = "SELECT * FROM `user_profiles` WHERE ".implode(' OR ', $sql)." ORDER BY `fname` ASC";
            $results = $this->db->query($query);
            return $results->result();
}

[eluser]danmontgomery[/eluser]
Also need to surround like searches with quotes

Code:

WHERE `fname` LIKE '%RICH%'