CODEWITHSUNDEEP
phpmysqlcodeigniterquery-builderwhere-in
Dan
Dan

Reputation: 2174

CodeIgniter query to return rows where a column value matches one of two qualifying values

I want to write a CodeIgniter active record query for the following raw SQL:

select *
from bookdetails
where editionId=$edid1
      or editionId=$edid2

Below is my CodeIgniter attempt.

public function get_rare_outofprintBooks($edid1,$edid2) 
{
    $this->load->database();   
    $query = $this->db
        ->get_where(
            'bookdetails',
            array('edition_id' => $edid1),
            array('edition_id' => $edid2)
    );  // I tried like this but its NOT Working  
         
    if ($query->num_rows() > 0) {
        foreach ($query->result() as $row) {
            $data[] = $row;
        }
        return $data;
    }
    return false;
}

Upvotes: 0

Views: 1547

Answers (3)

mickmackusa
mickmackusa

Reputation: 47864

  • You are checking if one of two values is found at a particular column. This is most sensibly done with an IN condition. The query builder method is called where_in().

  • All of the post query processing is completely omittable. Just return the generated array of zero or more objects. I never return false from a model method which otherwise returns a 2d array.

  • If your $edid variables are strings, type hint both parameters with string; if they are integers, type hint them as int as a matter of best practice.

  • You don't actually need to limit yourself to two parameters. Using the spread/splat operator, your method can accept a variable number of parameters and those values will be accessible as a flat array of string for convenience/utility.

  • A guard condition is added to the method below because if no parameters are passed in, then where_in() will produce an invalid query that contains IN () (an empty IN condition which will break the query).

public function getRareOutOfPrintBooks(int ...$editionIds): array
{
    if (!$editionIds) {
        return [];
    }
    return $this->db
        ->where_in('edition_id', $editionIds)
        ->get('bookdetails')
        ->result();
}

The above method can be called with a variable number of parameters such as:

  • $results = $this->YourModelName->get_rare_outofprintBooks($id1);
  • $results = $this->YourModelName->get_rare_outofprintBooks($id1, $id2);
  • $results = $this->YourModelName->get_rare_outofprintBooks($id1, $id2, $id3);
  • and so on.

All that said, though, if you want to pass in a variable number of edition ids, it probably makes better sense for the model method to directly accept an array instead of potentially multiple parameters.

public function getRareOutOfPrintBooks(array $editionIds): array
{
    if (!$editionIds) {
        return [];
    }
    return $this->db
        ->where_in('edition_id', $editionIds)
        ->get('bookdetails')
        ->result();
}

Upvotes: 0

Rick Calder
Rick Calder

Reputation: 18685

$this->db->where('editionId', $edid1);
$this->db->or_where('editionId', $edid2);

It's right in the documentation http://ellislab.com/codeigniter/user-guide/database/active_record.html

Upvotes: 4

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

$this->db->where('id',3);
$this->db->or_where('id',5);

Reference Here

Upvotes: 3

Related Questions