doufei3152 2019-01-18 09:57
浏览 50

在codeigniter里面使用Mysql Interval

I have used MySQL interval in my query. In raw type query its working fine but in PHP style, there was some wrong data also returned. I have found the problem that was coming due to the interval in where. I was tried a lot but it can't solve.

Number 1 is my raw query(working fine), Number 2 is my CodeIgniter code, Number 3 is my actual query for CodeIgniter code,

NO:1 SELECT str_to_date(concat(yearweek(s.creationdate), 'tuesday'), '%X%V %W') as week, `b`.`name` as `branchname`, `g`.`name` as `groupname`, `s`.`streetName`, DATE(s.creationdate) as start_date, DATE(s.maturity_date) as maturity_date, (DATE(s.maturity_date) + INTERVAL 4 WEEK) as nippu_date, `customerName`, `s`.`balance` FROM `super_sales` `s` LEFT JOIN `branch` `b` ON `s`.`branchID`=`b`.`id` LEFT JOIN `group` `g` ON `s`.`groupID`=`g`.`id` LEFT JOIN `super_customer` `c` ON `c`.`customerid`=`s`.`customerid` WHERE DATE(c.lastAttendance) > (DATE(s.maturity_date) + INTERVAL 4 WEEK) AND `c`.`balance` <>0 ORDER BY `week` DESC LIMIT 50

NO 2: $this->db->select("str_to_date(concat(yearweek(s.creationdate), 'tuesday'), '%X%V %W') as week,b.name as branchname,g.name as groupname,s.streetName,DATE(s.creationdate) as start_date,DATE(s.maturity_date) as maturity_date,(DATE(s.maturity_date) + INTERVAL 4 WEEK) as nippu_date,customerName,s.balance");
     if($parameters['branch']!=NULL){
       $this->db->like('s.branchID',$parameters['branch']);
    }
    // $dat = array('e_date' =>  ;
    // $dat=DATE_ADD(DATE(s.maturity_date) + INTERVAL 4 DAY);
    $this->db->where('DATE(c.lastAttendance) >','(DATE(s.maturity_date) + INTERVAL 4 WEEK)');
     $this->db->where('c.balance <>',0);
    if($parameters['cdatefrom']!=NULL){
       $this->db->where('yearweek(s.creationdate)',date('YW',strtotime($parameters['cdatefrom'])));
    }
    // $this->db->group_by(array("s.branchID", "str_to_date(concat(yearweek(s.creationdate), 'tuesday'), '%X%V %W')"));
    if(empty($order_column) || empty($order_type)){
        $this->db->order_by('yearweek(s.creationdate)','asc');
    }else{
        $this->db->order_by($order_column,$order_type);
    }
    if($limit!=0){
        $this->db->limit($limit,$offset);
    }
    $this->db->join('branch b','s.branchID=b.id ','left');
    $this->db->join('group g','s.groupID=g.id ','left');
    $this->db->join('super_customer c','c.customerid=s.customerid ','left');
    $query = $this->db->get('super_sales s');
    echo $this->db->last_query();
    if($query->num_rows()>0){
        return $query->result_array();
    }else{
        return FALSE;
    }

    NO:3 SELECT str_to_date(concat(yearweek(s.creationdate), 'tuesday'), '%X%V %W') as week, `b`.`name` as `branchname`, `g`.`name` as `groupname`, `s`.`streetName`, DATE(s.creationdate) as start_date, DATE(s.maturity_date) as maturity_date, (DATE(s.maturity_date) + INTERVAL 4 WEEK) as nippu_date, `customerName`, `s`.`balance` FROM `super_sales` `s` LEFT JOIN `branch` `b` ON `s`.`branchID`=`b`.`id` LEFT JOIN `group` `g` ON `s`.`groupID`=`g`.`id` LEFT JOIN `super_customer` `c` ON `c`.`customerid`=`s`.`customerid` WHERE DATE(c.lastAttendance) > '(DATE(s.maturity_date) + INTERVAL 4 WEEK)' AND `c`.`balance` <>0 ORDER BY `week` DESC LIMIT 50

I have found a problem with comparing 2 queries if I removed upper single quotes covered by (DATE(s.maturity_date) + INTERVAL 4 WEEK) its working fine but I don't know how?

  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
    • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
    • ¥15 Arcgis相交分析无法绘制一个或多个图形
    • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
    • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
    • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
    • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
    • ¥30 3天&7天&&15天&销量如何统计同一行
    • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
    • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型