Wednesday, December 18, 2013

How To Generate Dynamic LIKE Clause With Parentheses Using Codeigniter Active Record?

If you want to make dynamic and custom LIKE clause using Codeigniter active record, following function will be useful.
For Example
The number of tags and values is unknown. Arrays are created dynamically. This function can help you to accomplish what you want to it.
//To add parentheses around the LIKE clauses
function make_like_conditions (array $fields, $search_text) {
    $likes = array();
    foreach ($fields as $field) {
        $likes[] = "$field LIKE '%$search_text%'";
    }
    return '('.implode(' || ', $likes).')';
}

function do_something()
{
   $search_fields = array(
    'field_1',
    'field_2',
    'field_3',
  );

  $search_text = "apple";

  $like_conditions = $this->make_like_conditions($search_fields, $search_text);
  $query = $this->db->from('sometable')->where($like_conditions)->get();
  return $query->result();
}


Source: http://stackoverflow.com/a/16925449

2 comments:

  1. If you want mulitiple LIKE clause on array of search terms, you can use following function
    //To add parentheses around the LIKE clauses
    function make_like_conditions (array $search_texts, $field) {
    $likes = array();
    foreach ($search_texts as $search_text) {
    $likes[] = "$field LIKE '%$search_text%'";
    }
    return '('.implode(' && ', $likes).')';
    }

    function do_something()
    {
    $search_texts = array(
    'apple', 'orange', 'grapes' );

    $search_field = "fruit_name";

    $like_conditions = $this->make_like_conditions($search_texts, $search_field);
    $query = $this->db->from('sometable')->where($like_conditions)->get();
    return $query->result();
    }

    ReplyDelete
  2. Output:
    (tbl_offer. fruit_name LIKE '%apple%' && tbl_offer. fruit_name LIKE '%orange%')

    ReplyDelete