[SOLVED] Search Multiple Databases (less code)

[eluser]emcgfx[/eluser]
I've figured out the way to search multiple tables with help of Ci community Look here for original post > http://ellislab.com/forums/viewthread/131051/P10/

But now I'm trying to do this in much less code, any one know would I do this ? I have 14 tables, that I'm searching. Which produces lot of code. Thank you.

[eluser]emcgfx[/eluser]
ROFL, I don't know what's with me this days. Ones I post something on the forum, I figured it out right away LOL I did it again. Here is my code, don't know if its correct way to do this... But it works and fast too.

This is the code I use in my model now;

Code:

function getSearch($term)
  {
    $tables = array('table1', 'table2', 'table3');
    foreach ($tables as $table)
    {
      $this->db->from($table);
      $this->db->like('title', $term);
      $this->db->or_like('descr', $term);
      $this->db->or_like('link', $term);
      $this->db->or_like('author', $term);
      $table = $this->db->get();
      
      if ($table->num_rows() > 0)
      {
        foreach ($table->result() as $row)
        {
          $data[] = $row;
        }
        return $data;
      } // end: if
    } // end: foreach
  } // end: getSearch function

I really hope this will help some one like it did me :-) All this time, breaking my head and I finally did it. Maybe I will be PHP decent coder after all who knows. You are welcome to comment or improve and post it here, and if you know a better way of doing this please share Thank you, and Merry Christmas

[eluser]Zack Kitzmiller[/eluser]
That code is going to work, but it's going to be pretty slow. You're going to want to make sure your have your tables have the proper indexes.

[eluser]emcgfx[/eluser]
It works much faster for me then the old code, and my indexes are proper Compare to my old code, for 14 categories I had 308 lines of code. With this one its only 22 :-) I haven't noticed any problems with it yet. Works fast

[eluser]emcgfx[/eluser]
ok how would I implement pagination into my search results.

[eluser]GrimReaper[/eluser]
I prefer using sql for your function, assuming table1,table2 and table3 have the same fieldtype:

Code:

function getSearch($term='',$offset=0,$limit=10,$sort='title',$direction='asc') {
  $data = array();
  $sql=
    "Select * from TABLE1 where title like '%" . $term . "%' or descr like '%" . $term . "%' " .
    " UNION ALL " .
    "Select * from TABLE2 where title like '%" . $term . "%' or descr like '%" . $term . "%' " .
    " UNION ALL " .
    "Select * from TABLE2 where title like '%" . $term . "%' or descr like '%" . $term . "%' " .
    " ORDER BY $sort $direction LIMIT $offset, $limit ";
  $query = $this->db->query($sql);

  if ($query->num_rows() > 0){
    $data = $query->result_array();
  }
  $query->free_result();
  return $data;
}

good luck..!