질문 Q #9841

DB 관련 조언 좀 부탁드립니다.

다니엘SEO 2012-04-27 10:49:25

//검색실행
  public function act_project_list($data){
    
   $this->db->select('
    Project.projectCode,
    Project.projectState,
    Project.projectName,
    Project.projectStartDate,
    Project.projectEndDate,
    Project.projectAllTerm,
    ifnull(Member_2D.memberPosition, "") AS memberPosition_2D,
    ifnull(Member_2D.memberName, "") AS memberName_2D,
    ifnull(Member_3D.memberPosition, "") AS memberPosition_3D,
    ifnull(Member_3D.memberName, "") AS memberName_3D
   ');
   
   $this->db->from('Project');
   
   $this->db->join('Member AS Member_2D','Project.projectPM_2D = Member_2D.memberCode', 'left');
   $this->db->join('Member AS Member_3D','Project.projectPM_3D = Member_3D.memberCode', 'left');
   
   if($data['project_title'] && $data['project_title'] != '프로젝트명 입력') {
    $this->db->like('Project.projectName', $data['project_title']);
   }
   
   if($data['project_state']) {
    $this->db->where('Project.projectState', $data['project_state']);
   }
   
   if($data['start_date']) {
    $this->db->where('Project.projectStartDate >=', $data['start_date']); 
   }
   
   $this->db->order_by('Project.projectEndDate', 'ASC');
   $this->db->order_by('Project.projectStartDate', 'DESC');
   
   $query = $this->db->get();
   
   return $query;
  }

위와 같이 만들었는데요, 아래와 같은 오류가 나옵니다...어디가 문제인지 조언 좀 부탁드려요 ㅠ.ㅠ

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (`Project`) LEFT JOIN `Member` AS Member_2D ON `Project`.`projectPM_2D` = `' at line 2

SELECT `Project`.`projectCode`, `Project`.`projectState`, `Project`.`projectName`, `Project`.`projectStartDate`, `Project`.`projectEndDate`, `Project`.`projectAllTerm`, ifnull(Member_2D.memberPosition, `"")` AS memberPosition_2D, ifnull(Member_2D.memberName, `"")` AS memberName_2D, ifnull(Member_3D.memberPosition, `"")` AS memberPosition_3D, ifnull(Member_3D.memberName, `"")` AS memberName_3D FROM (`Project`) LEFT JOIN `Member` AS Member_2D ON `Project`.`projectPM_2D` = `Member_2D`.`memberCode` LEFT JOIN `Member` AS Member_3D ON `Project`.`projectPM_3D` = `Member_3D`.`memberCode` WHERE `Project`.`projectStartDate` >= '2011-10-27 00:00:00' ORDER BY `Project`.`projectEndDate` ASC, `Project`.`projectStartDate` DESC

Filename: /home/intranet/www/models/project_model.php

Line Number: 67

#1 한대승(불의회상) 2012-04-27 10:59:42

$this->db->select('
Project.projectCode,
Project.projectState,
Project.projectName,
Project.projectStartDate,
Project.projectEndDate,
Project.projectAllTerm,
ifnull(Member_2D.memberPosition, "") AS memberPosition_2D,
ifnull(Member_2D.memberName, "") AS memberName_2D,
ifnull(Member_3D.memberPosition, "") AS memberPosition_3D,
ifnull(Member_3D.memberName, "") AS memberName_3D
');

이 부분에 에러가 있군요.

1. SQL에서는 "(더불쿼테이션)을 쓰면 에러가 납니다.
2. 내장 함수가 들어 있어 문제가 되는군요.

이 두문제를 해결하면 아래와 같이 됩니다.

$this->db->select("
Project.projectCode,
Project.projectState,
Project.projectName,
Project.projectStartDate,
Project.projectEndDate,
Project.projectAllTerm,
ifnull(Member_2D.memberPosition, '') AS memberPosition_2D,
ifnull(Member_2D.memberName, '') AS memberName_2D,
ifnull(Member_3D.memberPosition, '') AS memberPosition_3D,
ifnull(Member_3D.memberName, '') AS memberName_3D
", FALSE);