Active record complex where statement troubles

[eluser]johnwbaxter[/eluser]
I would like to do this with active record. Any ideas how!?

Oh to add, the values are coming from an array so i need to loop the values in.

Brain meltdown has commenced.

Code:

SELECT john_field, bert_field, harry_field FROM chum_table WHERE (john_field = 'test1' OR john_field = 'test2') AND (bert_field = test3 OR bert_field = test4) AND (harry_field = 'test5' OR harry_field = 'test6');

Thanks for any help in advance!

[eluser]champs[/eluser]

Code:

$this->db->select('john_field, bert_field, harry_field');
$this->db->from('chum_table');
$this->db->where("(john_field = 'test1' OR john_field = 'test2')");
$this->db->where("(bert_field = test3 OR bert_field = test4)");
$this->db->where("(harry_field = 'test5' OR harry_field = 'test6')");

In this case, you should also run $this->db->escape_str() on any input variables.

[eluser]jtotheb[/eluser]
Do you know how i would do that with an array of between 0 and 20 values per where clause?!
I need to do that too!

[eluser]richard_ctv[/eluser]
[quote author="jtotheb" date="1186101381"]Do you know how i would do that with an array of between 0 and 20 values per where clause?!
I need to do that too![/quote]

Here is something based on a current project I'm working on:

Code:

$ORS = array();
foreach( $your_array as $field => $value ){
   array_push( $ORS, "field = $value" );                
}
            
$where = join( " OR ", $ORS );

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

You could have an ORS array and an ANDS array and build things up that way.

Hope that helps..

[eluser]johnwbaxter[/eluser]
Oh man that is a beautiful thing! I will admit to not knowing "array_push".

Thank you very much indeed. It works a treat.

The only thing i have to work out now is how to get the selections seperated by "OR" surrounded by brackets!

Wish me luck

I have brain issues some days as my previous posts suggest. Just ignore me, i know how to do the above, i dont know why i thought it would be an issue....

[eluser]richard_ctv[/eluser]
[quote author="audiopleb" date="1186159936"].

The only thing i have to work out now is how to get the selections seperated by "OR" surrounded by brackets!

Wish me luck
[/quote]

Something like this (not tested):

Code:

$test_array = array(
  'field1' => array( 'test1', 'test2' ),
  'field2' => array( 'test3', 'test4' ),
  'field3' => array( 'test5', 'test6' ),
);


$ANDS = array();

foreach( $test_array as $field => $data ){

  $ORS = array();
  foreach( $data as $test ){
   // takes $field from outer loop
   array_push( $ORS, "($field = $test)" );                
  }
  array_push( $ANDS, $ORS );

}
            
$where = join( " AND ", $ORS );