Omitting where clause for empty criterion

[eluser]cyberjunkie[/eluser]
I'm using a form on my website to filter database records. For example I'm filtering posts by category tags and author.

Code:

SELECT * FROM (`posts`) WHERE `category` = 'technolgy' AND `author` = 'lila' AND `tags` = 'ebook'

Filling all filtering option is not required so you can search posts just by author regardless of category or tags.

that creates this query

Code:

SELECT * FROM (`posts`) WHERE `category` = '' AND `author` = 'lila' AND `tags` = ''

The empty AND clause produces no results. According to the CI documentation, empty inputs are set to FALSE. Based on that, can omit empty clauses or add them only for filled inputs?

In my model I'm using my input array for the where() function.

Code:

function filter($form_values)
    {                      
        $query = $this->db->get('posts');          
        $this->db->where($form_values); //adds clause to all array items
        return $query->result();        
    }

View

Code:

$form_values = array (                  
                'category' => $this->input->post('category'),
                'author'   => $this->input->post('author'),
                'tags'     => $this->input->post('tags')
            );

$this->Records_model->filter($form_values);

[eluser]ontguy[/eluser]
Are you asking for a way this could be done?
Something like this should work.

Code:

$form_values = array();

if ( ! empty( $this->input->post('category'))
{
$form_values['category'] = $this->input->post('category');tags
}
elseif ( ! empty( $this->input->post('tags'))
{
$form_values['tags'] = $this->input->post('tags');
}

[eluser]cyberjunkie[/eluser]
Thanks ontguy! That put me on the right track For anyone wanting to do this, use the function

Code:

$query = array_filter($your_form_input_array)

and

Code:

$this->db->where($query)

It will omit AND clauses if criteria is empty.