I am getting the following error, with the code below i am not getting any error on another db, but getting error on the first one, no clue whats happening, i am using codeigniter 1x and ver 10.1.25-MariaDB
A Database Error Occurred Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' jp.departmentid ) >0 and FIND_IN_SET( 0, jp.designation ) >0 and FIND_IN_SET( ,' at line 1
SELECT DISTINCT d.id as did, departmentname,GROUP_CONCAT(DISTINCT jp.id) as policy_ids,GROUP_CONCAT(DISTINCT policy_title) as policies FROM `pr_policy` jp INNER JOIN `pr_departments` d ON FIND_IN_SET( d.id, jp.departmentid ) >0 INNER JOIN `pr_designation` des ON FIND_IN_SET( des.id, jp.designation ) >0 LEFT OUTER JOIN `pr_office_branches` o ON FIND_IN_SET( o.id, jp.branchid ) >0 WHERE FIND_IN_SET( , jp.departmentid ) >0 and FIND_IN_SET( 0, jp.designation ) >0 and FIND_IN_SET( , jp.branchid ) >0 group by jp.id,d.id,des.id
Filename: /var/www/html/login/models/mod_common.php
Line Number: 7582
The model - mod_common.php function is as below
function get_full_policies_user() { $sql = "SELECT jp.*,jp.id as id,GROUP_CONCAT(DISTINCT d.departmentname) as department,GROUP_CONCAT(DISTINCT des.designation) as designation,jp.create_date,GROUP_CONCAT(DISTINCT o.branchname) as branch,jp.policy_title,jp.create_date
FROM `".$this->myTables['policy']."` jp INNER JOIN `".$this->myTables['department']."` d ON FIND_IN_SET( d.id, jp.departmentid ) >0 INNER JOIN `".$this->myTables['designation']."` des ON FIND_IN_SET( des.id, jp.designation ) >0 LEFT OUTER JOIN `".$this->myTables['office']."`
o ON FIND_IN_SET( o.id, jp.branchid ) >0 "; if($jdid > 0) { $sql .= " where jp.id=".$jdid; } $sql .=" group by jp.id,d.id,des.id"; //echo $sql;die(); $query=$this->db->query($sql); if($query->num_rows() > 0){ $rows = $query->result(); } return $rows;
}
The database tables are as attached - pr_policy, pr_departments, pr_designation