CODEWITHSUNDEEP
phpmysqlsqlselectin-operator
Levent Tulun
Levent Tulun

Reputation: 711

MySQL query ERROR 12

public function getAllSharedCases($uid) {
        $sql = "SELECT * FROM cases 
                INNER JOIN share_pathologist AS sp ON cases.case_id = sp.case_id 
                INNER JOIN share_group AS sg ON cases.case_id = sg.case_id 
                WHERE sp.pathologist_id = ? OR sg.group_id = (SELECT group_id FROM user_group WHERE state = ? AND user_id = ?)";
        $query = $this->db->prepare($sql);
        $query->execute(array($uid, "joined", $uid));
        if($query->rowCount()) {
            $cases = $query->fetchAll();
            for ($i = 0; $i < count($cases); $i++) {
                $shared = $this->getSharedWithGroups($cases[$i]["case_id"]);
                $cases[$i]["shared_group"] = $shared;
                $shared = $this->getSharedWithPathologists($cases[$i]["case_id"]);
                $cases[$i]["shared_pathologist"] = $shared;
            }
            return $cases;
        }
        return false;
    }

share_pathologist: 

id
case_id
pathologist_id

share_group: 

id
case_id
group_id

user_group: 

id
user_id
group_id
state

This query returns false always.

Upvotes: 2

Views: 332

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Use IN operator instead of EQUALS TO('=') before sub query.

Try this: 

public function getAllSharedCases($uid) {
        $sql = "SELECT * FROM cases 
                INNER JOIN share_pathologist AS sp ON cases.case_id = sp.case_id 
                INNER JOIN share_group AS sg ON cases.case_id = sg.case_id 
                WHERE sp.pathologist_id = ? OR sg.group_id IN (SELECT group_id FROM user_group WHERE state = ? AND user_id = ?)";
        $query = $this->db->prepare($sql);
        $query->execute(array($uid, "joined", $uid));
        if($query->rowCount()) {
            $cases = $query->fetchAll();
            for ($i = 0; $i < count($cases); $i++) {
                $shared = $this->getSharedWithGroups($cases[$i]["case_id"]);
                $cases[$i]["shared_group"] = $shared;
                $shared = $this->getSharedWithPathologists($cases[$i]["case_id"]);
                $cases[$i]["shared_pathologist"] = $shared;
            }
            return $cases;
        }
        return false;
    }

Upvotes: 2

Related Questions