设计参考:https://www.tuicool.com/articles/QJrAfa
1,翻译人、审核人、发布完成人
select t_user_role.iUserID,t_user.sAccount,t_user_role.sRoleID
from t_user
left join t_user_role on t_user_role.iUserID = t_user.iUserID
group by t_user.iUserID;
========================================================================================================
//根据用户查出角色
select t_user.*,t_role.sRoleID,t_role.sRoleName
from t_user
left join t_user_role on t_user_role.iUserID = t_user.iUserID
left join t_role on t_role.sRoleID = t_user_role.sRoleID
where t_user.iStatus = 0
group by t_user.iUserID;
//根据角色查权限
select t_role.sRoleID,t_role.sRoleName,t_privilege.sPrivilegeID,t_privilege.sPrivilegeName
from t_role
left join t_role_privilege on t_role_privilege.sRoleID = t_role.sRoleID
left join t_privilege on t_privilege.sPrivilegeID = t_role_privilege.sPrivilegeID
group by t_role.sRoleID;
//根据权限查模块
select t_privilege.sPrivilegeID,t_privilege.sPrivilegeName,t_module.sModuleID,t_module.sModuleName,t_module.sParentModuleID
from t_privilege
left join t_privilege_module on t_privilege_module.sPrivilegeID = t_privilege.sPrivilegeID
left join t_module on t_module.sModuleID = t_privilege_module.sModuleID
group by t_privilege.sPrivilegeID;
<?php
// 获取用户的权限
$CI->load->database();
$CI->db->select('A.iUserID,B.sRoleID,B.quanxian_id,C.sModuleID');
$CI->db->from('`xxx`.`t_user_role` A');
$CI->db->join('`xxx`.`t_role_privilege` B', 'B.sRoleID = A.sRoleID', 'left');
$CI->db->join('`xxx`.`t_privilege_module` C', 'C.quanxian_id = B.quanxian_id', 'left');
$CI->db->where('A.iUserID', $iUserId);
if ($moduleId) {
$CI->db->where('C.sModuleID', $moduleId);
}
$res = $CI->db->get()->result_array();
// 测试sql语句:
$CI->db->get()->result_array();
echo $CI->db->last_query();
$result = array();
foreach ($res as $key => $val) {
$result['quanxian_id'][$key] = $val['quanxian_id'];
}
$result = implode(',', $result['quanxian_id']);
$result = explode(',', $result);
<?php
//最后:CI框架 权限,增加到用户表中: GROUP_CONCAT(iUserID) as UId (核心语句)
$sql = "select * from " . self::xxxx . " where iStatus=1 " . $where . " limit $start,$pageSize"; $rs = $this->base_query($sql);
$roles = $this->get_user_role_list();
$nowRole = [];
foreach ($roles as $key => $val) {
$roles[$key]['UId'] = explode(',', $val['UId']);
foreach ($rs as $k => $v) {
if (in_array($v['iUserID'], $roles[$key]['UId'])) {
$rs[$k]['sRoleID'] = $roles[$key]['sRoleID'];
}
if ($v['iUserID'] == $uid && $v['sRoleID'] == 'Role_System_Manager') {
$nowRole['iUserID'] = $v['iUserID'];
$nowRole['sRoleID'] = $v['sRoleID'];
unset($rs[$k]);
}
}
}
public function get_user_role_list()
{
$sql = "select sRoleID,GROUP_CONCAT(iUserID) as UId from " . self::xxxx . " group by sRoleID";
$rs = $this->base_query($sql);
return $rs;
}