CodeIgniter Forums

Full Version: Gets too much data
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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.
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`;
(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.
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.