CodeIgniter Forums
Omitting where clause for empty criterion - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Omitting where clause for empty criterion (/showthread.php?tid=43115)

Omitting where clause for empty criterion - El Forum - 06-30-2011

[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);


Omitting where clause for empty criterion - El Forum - 06-30-2011

[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');
}


Omitting where clause for empty criterion - El Forum - 07-01-2011

[eluser]cyberjunkie[/eluser]
Thanks ontguy! That put me on the right track SmileFor 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.