Multiple WHERE Clause's

[james182]

I don't seem to be having much luck with this any help would be great.

 

Trying to select multiple colours.

 

$query = "SELECT * FROM diamonds WHERE colour IN ('D', 'E', 'F')";
...

Not working

 

My exact Code

public function get_diamond_by_shape($shape,$scat_id,$colour,$clarity,$certification,$cut,$polish,$symmetry,$fluorescence,$min_carat,$max_carat,$min_price,$max_price)
{

	//$colours = "'". implode("','", explode('-', substr_replace($colour ,"",-1))) ."'";

	$colours = explode('-', substr_replace($colour ,"",-1));
	$clarities = explode('-', substr_replace($clarity ,"",-1));
	$certifications = explode('-', substr_replace($certification ,"",-1));		

	print_r($shape);

	$this->db->where('shape', $shape);
	$this->db->or_where_in('colour', $colours);
	$this->db->or_where_in('clarity', $clarities);
	$this->db->or_where_in('certification_type', $certifications);
	/*
	$this->db->where('cut', $cut);
	$this->db->where('polish', $polish);
	$this->db->where('symmetry', $symmetry);
	$this->db->where('fluorescence', $fluorescence);				
	*/
	$this->db->where('carat >=', $min_carat);
	$this->db->where('carat <=', $max_carat) ;
	$this->db->where('cost BETWEEN ' . $min_price . ' AND ' . $max_price);

	$diamonds = $this->db->get('products_diamonds');

	return $diamonds->result_array();

}

Post data is:

certi all,

clarity all,

colour = D-E-F-G-,

cut = all,

flou = all,

maxprice = 100000,

maxval = 5,

minprice = 100,

minval = 0.01,

pg = 1,

polish = all,

scat_id = null,

shape = Princess,

sym = all

 

Using CodeIgniter Framework

[Mahngiel]

Which part is "not working" ?  According to CI manual:

$this->db->where_in();

Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate

 

To perform the action you code-blocked, you'd:

<?php
->where_in('colour', $colours')

 

 

And your query could be completely optimized and debugged:

controller

<?php
// build an array from the post values
$data = array(
'shape' => $shape, 
'scat_id' => $scat_id, 
'cut' =>$cut, 
'polish' => $polish, 
'symmetry' => $symmetry, 
'flourescence' => $fluorescence, 	
);

//build combos 
$colours = explode('-', substr_replace($colour ,"",-1));
$clarities = explode('-', substr_replace($clarity ,"",-1));
$certifications = explode('-', substr_replace($certification ,"",-1));

// send all to model
$results = $this->model->get_diamond_by_shape( $data, $colours, $clarities, $certifications, $min_carat, $max_carat,$min_price,$max_price );

 

model

<?php
public function get_diamond_by_shape( $data = array(), $colours = array() ,$clarities = array() ,$certifications = array(), $min_carat, $max_carat,$min_price,$max_price )
{

$query = $this->db
		->where( $data )
		->where_in('colour', $colours)
		->where_in('clarity', $clarities);
		->where_in('certification_type', $certifications);
		->where('carat >=', $min_carat);
		->where('carat <=', $max_carat) ;
		->where('cost BETWEEN ' . $min_price . ' AND ' . $max_price);
		->get('products_diamonds');

// Check if query result exists
if($query->result()) {
	// Query result exists, return query result
	return $query->result();
} else {
// Query result doesn't exist, return FALSE
return FALSE;
}
}

[sKunKbad]

You could also do something like this:

$sql = "SELECT * FROM diamonds WHERE colour IN ( ?, ?, ? )";

$query = $this->db->query( $sql, array( "D", "E", "F" ) );

 

I find this much easier for complex queries than using active record, which ends up being harder than just writing the query out by hand.