CODEWITHSUNDEEP
phpmysqlcodeigniter
Daniel Fein
Daniel Fein

Reputation: 327

Can I set multiple Codeigniter Where criteria from an array?

I am trying to get data from a database that meets multiple criteria of an array.

The array is something like this: 

Array ([21] => 1,[23] => 0,[19] => 1);

With the key being a Question ID and the values being either yes or no.

I need to find the movie where the value for question_id = 21 is 1, value for question_id = 23 is 0 and value for question_id = 19 is 1. The way I have them stored is like this:

enter image description here

So my first thought was get the data for each and then put them in a bigger array. If the movie shows up the same amount of times as the number of elements in the array, then I consider it a good match. But this seems inefficient. I would rather just find the movies that match the criteria.

Since there are movie_id records with the same value, is there a way to write something like this?:

foreach($array as $key=>$value){
$i++;
$this->db->where('question_id', $key);
$this->db->where('value', $value);

}
$this->db->from('movies_values');
    $query = $this->db->get();
    $res = $query->result();
    array_push($main,$res);

The thought behind this is to create a loop of all the WHEREs. And then run the query using those where values. This doesn't seem to work, is there something else I can do?

Upvotes: 1

Views: 1448

Answers (5)

PinoyPal
PinoyPal

Reputation: 388

This can be done easily without loop:

$filter = array(21 => 1,23 => 0,19 => 1);

$values = implode(',',array_unique(array_values($filter))); // results into 0,1...
$keys   = implode(',',array_unique(array_keys($filter))); // results into 19,21,23...

$result = $this->db
              ->query("select * from movies_values
                         where 
                            question_id in(".$keys.")
                            and value in(".$values.")")
              ->result();

Happy coding ---> :)

Upvotes: 0

dennisbot
dennisbot

Reputation: 948

I think this is the right way to go, you should take care to use "or" instead to use full "ands" that way would not return any row due to a logic problem (I mean question_id = 1 and value = 1 and question_id = 2 and value = 0 we're being inconsistent due to telling that we want question_id = 1 and question_id = 2 won't match nothing!, the same applies to "values").

        $array = array(21 => 1,23 => 0,19 => 1);
        $where = array();
        foreach($array as $key => $value) {
            $where[] = "(question_id=$key and value=$value)";
        }
        var_dump($where);
        foreach ($where as $value) {
            $this->db->or_where($value);
        }
        $q = $this->db->get('movies_values')->result();
        var_dump($q);
        echo $this->db->last_query();exit;

Upvotes: 0

Repox
Repox

Reputation: 15476

Use the where_in method for lists:

$this->db->where_in('value', $array);

Upvotes: 1

Stenerson
Stenerson

Reputation: 1002

How about using WHERE IN (array())?

From the CI User Guide:

$names = array('Frank', 'Todd', 'James');
$this->db->where_in('username', $names);
// Produces: WHERE username IN ('Frank', 'Todd', 'James')

Upvotes: 1

Rick Calder
Rick Calder

Reputation: 18695

Try doing it like this.

$where = WHERE 1
foreach($array as $key=>$value){
   $where .= " AND(question_id = $key AND value = $value)";
}

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

PS. What is the $i++ doing in your loop exactly?

Upvotes: 0

Related Questions