phpcodeigniteractiverecordwhere-clausequery-builder

SELECT rows WHERE a single value is found in one of multiple columns using CodeIgniter's query builder methods


I have to compare one values with multiple fields in single query using CodeIgniter. I have one value $search_value and it should match either of these two table columns field1 or field2. Additionally, qualifying rows should have $reference_number in the reference_number column.

My query:

$this->db->where(array(
    'reference_number' => $reference_number,
    'field1' => $search_value
));
$this->db->or_where(array(
    'reference_number' => $reference_number,
    'field2' => $search_value
));

Solution

  • You can use like query to match your $search_value in your field1 and filed2 and write reference_number in your where condition.

    $this->db->like('field1', $search_value);
    $this->db->or_like('field2', $search_value);
    $this->db->where('reference_number', $reference_number);
    $res = $this->db->get('TABLE_NAME');
    

    UPDATED

     $this->db->where("reference_number=1234 AND field1='$search_value'");
     $this->db->or_where("reference_number=1234 AND field2='$search_value'");
     $res = $this->db->get('TABLE_NAME');