+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: CodeIgniter 2.x (https://forum.codeigniter.com/forumdisplay.php?fid=18)
+--- Thread: Gets too much data (/showthread.php?tid=61551)
Gets too much data - Toddles - 04-24-2015
Hello all I have this database query but I get all dates back to 2010 and not in the range selected..
SELECT* FROM ( `morning_qa` )
WHERE`qa_date`>='2014-04-24'
AND`qa_date`<='2015-04-24'
AND`qa_type`='iris'
OR`qa_type`='cones'
ORDERBY`qa_date`;
PHP Code:
function morningQA_getall($range_type, $range_num, $qa_year, $qa_type){
$date_last = date("Y-m-d");
if ($range_type == "year"){
$num = "-".$range_num." year";
$date_start = date("Y-m-d", strtotime($num));
}
if ($range_type == "month"){
$num = "-".$range_num." month";
$date_start = date("Y-m-d", strtotime($num));
}
if ($qa_year != NULL){
$date_start = date($qa_year."-01-01");
$date_last = date($qa_year."-12-31");
}
$this->db->where('qa_date >=', $date_start);
$this->db->where('qa_date <=', $date_last);
if ($qa_type == "iris"){
$this->db->where('qa_type', $qa_type);
}
if ($qa_type == "cones"){
$this->db->where('qa_type', $qa_type);
}
if ($qa_type == "both"){
$this->db->where('qa_type', "iris");
$this->db->or_where('qa_type', "cones");
}
$this->db->order_by("qa_date");
$query=$this->db->get('morning_qa');
return $query->result_array();
}
Not sure why. It doesn't like the both clause. Works fine for either cones or iris on their own.
RE: Gets too much data - CroNiX - 04-24-2015
I think you'd want to use parens around the (and or) so it will use one OR the other, but not both:
SELECT* FROM ( `morning_qa` )
WHERE`qa_date`>='2014-04-24'
AND`qa_date`<='2015-04-24'
AND (`qa_type`='iris' OR `qa_type`='cones')
ORDERBY`qa_date`;
RE: Gets too much data - Toddles - 04-24-2015
(04-24-2015, 01:13 PM)CroNiX Wrote: I think you'd want to use parens around the (and or) so it will use one OR the other, but not both:
SELECT* FROM ( `morning_qa` )
WHERE`qa_date`>='2014-04-24'
AND`qa_date`<='2015-04-24'
AND (`qa_type`='iris' OR `qa_type`='cones')
ORDERBY`qa_date`;
PHP Code:
if ($qa_type == "iris"){
$this->db->where('qa_type', $qa_type);
}
if ($qa_type == "cones"){
$this->db->where('qa_type', $qa_type);
}
if ($qa_type == "both"){
$this->db->where('qa_type', "iris");
$this->db->or_where('qa_type', "cones");
}
How do you add the parens.
RE: Gets too much data - CroNiX - 04-24-2015
CI2 doesn't have a way to automatically add them and only produces basic queries, not the more complex ones. CI3 is a bit better with that.
I just usually use $this->db->query(raw_sql) for more complex queries.
but you can also use a string in active record for WHEREs, like:
PHP Code:
$this->db
->where('something', 'any')
->where('(something_else = cond2 OR something_new = cond3)');
Would produce:
WHERE something = 'any'
AND (something_else = cond2 OR something_new = cond3)
If cond2 or cond3 are user input, you'd have to db::escape() them manually.