Searchbox, multiple columns and LIKE

[eluser]billmce[/eluser]
I may be going about this completely wrong -- any suggestions appreciated.

I have a view with which shows something like:

Code:

ID        LastName        FirstName    OtherStuff
---     --------        ---------      ----------
11      Smith           Bill        Stuff for Bill
12      Jones           Sam         All about Sam

I have a searchbox on the view.

I want people to get results when they search for
a)Smith
b)Bill
c)Stuff

All of the above searches should show record ID 11 -- and I've got that working.

But I recently got a request that the searchbox should return a result on
d) Smith, Bill
e) Bill Smith

My existing search related code is

Code:

if (!empty($searchbox)) {
            
            $searcharray = array(
                'LastName' => $searchbox,
                'FirstName' => $searchbox,
            );

            $this->db->like('ID', $searchbox);
            $this->db->or_like($searcharray, $searchbox);
            
        }

How do I go about this?

Idea's I've tried and got no where on.
i) aliasing both columns into one column and adding the new column, FullName, into $searcharray. I could not get two columns to alias into one
ie) select('FirstName LastName' AS FullName')
ii) doing the same kind of concatination in the like.

How can I search for records containing both FirstName and LastName in the searchbox string? (and keep what I've already got of course).

TIA.

[eluser]pickupman[/eluser]
If you are wanting multiple or_like() you only have to use it. CI, will figure where to put the like and or like. Also you either pass ($array), or you do (string,string). Looks like you only need:

Code:

$searcharray = array(
                'LastName' => $searchbox,
                'FirstName' => $searchbox,
                'ID'        => $searchbox
            );

            $this->db->or_like($searcharray);

Try writing the query in MySQL using first to find the data you need, and then write the CI syntax for it. Sometimes with search queries, you have to use subqueries, and subqueries don't play well with active record.

You can use either

Code:

echo '<pre>'.$this->db->last_query().'</pre>'; //Show me last query run
//or
$this->output->enable_profiler(TRUE); //The best CI function ever (gives you all queries, timer, $_REQUEST)

[eluser]ChimpWorks[/eluser]
You could try something like this...

Code:

if (!empty($searchbox)) {

            $searchbox = explode(' ', $searchbox);
            $where = array();
            foreach($searchbox as $word)
            {
              $where[] = "(LastName LIKE '%".$word."%' OR FirstName LIKE '%".$word."%')";
            }

            $this->db->where(implode(' AND ', $where));
            $q = $this->db->get('peeps');
            return $q->result();
            
        }

[eluser]Higher Ground Studio[/eluser]
A great way to make your table look great, have pagination, and ajax search function is to use the jquery plugin, Datatables (http://www.datatables.net/). I use it for stuff like this for the reasons that it does all of the above is themeroller ready (jquery ui) and it works great the only thing you need to do is if you are using ci tables to create the table you need to follow the datatable structure and use the templates on ci tables. If you have a question how to do this I have an example but not on my laptop. Hope this helps.

[eluser]ChimpWorks[/eluser]
Datatables = Bookmarked!

You could also try using a MySQL fulltext search. First you'll need to set your fulltext keys by doing...

Code:

ALTER TABLE `people` ADD FULLTEXT (`FirstName` ,`LastName`)

then you can run a query like...

Code:

$sql = "SELECT * FROM people WHERE MATCH(`FirstName`, `LastName`) AGAINST('".$searchbox."')";
$q = $this->db->query($sql);
return $q->result();

[eluser]ChimpWorks[/eluser]
[quote author="pickupman" date="1279177767"]

Code:

$this->output->enable_profiler(TRUE); //The best CI function ever (gives you all queries, timer, $_REQUEST)

[/quote]

Thank you, I hadn't come across that before it has helped me debug problems today!