dynamic table selection using foreach

I use kohana and i have this model to retrieve search results from database using PDO:

class Model_Crud extends Model_Database {

  private $tables=array('articles','comments','pages');

  public function get_search_results()
  {
    $query = DB::query(Database::SELECT, 'SELECT * FROM :table WHERE ( title LIKE :search OR body LIKE :search OR tag LIKE :search)');
    $query->param(':search', $_POST['search'] );
    $query->bind(':table', $table );

    foreach($this->tables as $table)
    {
       //echo $query;
       $result[] = $query->execute();
    }

    return $result;
  }
} 

This wont wo开发者_开发百科rk cause the sql statement will be like this in its final form:

SELECT * FROM 'articles' WHERE ( title LIKE 'a random string' OR body LIKE 'a random string' OR tag LIKE 'a random string')

and naturally it fails since articles should be out of '

Can something like this done? or i need to write 3 different queries, one for each table?

Taking a look at the Database_Query class, it doesn't seem that what you are after can be done without creating separate queries for each table.

You could extend the Database_Query class with a set_table method that translates :table.

Better yet, you could abstract the concept a little and add new methods for translating parameters that are not to be sanitized. Take a look at Database_Query::compile to get an idea of how it's done. (It's not difficult at all.)

Yes, just put the table name into the string directly instead of as a parameter:

class Model_Crud extends Model_Database {

  private $tables=array('articles','comments','pages');

  public function get_search_results()
  {
    foreach($this->tables as $table)
    {
       $query = DB::query(Database::SELECT, 'SELECT * FROM ' . $table . ' WHERE ( title LIKE :search OR body LIKE :search OR tag LIKE :search)');
       $query->param(':search', $_POST['search'] );

       //echo $query;
       $result[] = $query->execute();
    }

    return $result;
  }
} 

Normally this isn't a good idea because of SQL injection, but since the list of tables is coded into your program, you don't really have to worry about that in this case.