Filtering Results with CodeIgniter

I have two tables books and authors. Using CodeIgniter I would like users to be able to Filter results - for example, show results where author_id = 'x' and book_lang = 'y' and publication_year = '2000'. Filter criteria values come from user via drop-downs.

What's the best way to generate the query using codeigniter helper and binding etc?

For example, the query below will give me everything with author_id= 1. what if filters are a mix, for example; author_id = 1; language = 'all' (don’t put where cla开发者_开发技巧use on language) and publication year = 'all' also don’t put where clause.. Do I manually need to check for values and code or is there a codigniter helper method that allows filter to be removed from where clause if dropdown value is 'show all'?

$sql = "select * from AUTHORs a , BOOKS b where 
        a.AUTH_ID = b.BOOK_AUTH_ID 
        and b.BOOK_AUTH_ID = ? 
        and b.BOOK_LANGUAGE = ?
        and b.PUB_YEAR = ? ";

$query =  $this->db->query($sql, array ($author_id, $book_lang, $pub_year));

You should use Codeigniter's Active Record class to construct your query:

$this->db->select('*');
$this->db->from('AUTHORs a , BOOKS b');
$this->db->where('a.AUTH_ID', 'b.BOOK_AUTH_ID');

if ($author_id != 'all') $this->db->where('b.BOOK_AUTH_ID', $author_id);
if ($book_lang != 'all') $this->db->where('b.BOOK_LANGUAGE', $book_lang);
if ($pub_year != 'all') $this->db->where('b.PUB_YEAR', $pub_year);

$query = $this->db->get();

To read more about Codeigniter's Active Record class, visit the Docs:
http://codeigniter.com/user_guide/database/active_record.html