当管理员用户取消选中所有用户组时，不会从数据库中清除权限，并引发错误。如果没有选择权限，并且提交了表单，则应该清除属于该id的权限列。但由于某种原因，没有吗？
让它发挥作用的最佳方法是什么？
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 'WHERE user_group_id = '10'' at line 4
UPDATE user_group SET name = 'Demonstration', permission = WHERE user_group_id = '10'
Filename: C:'Xampp'htdocs'codeigniter-project'system'database'DB_driver.php
Line Number: 330
用户组模型
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Users_group_model extends CI_Model {
public function addUserGroup($data) {
$this->db->query("INSERT INTO " . $this->db->dbprefix . "user_group SET name = " . $this->db->escape($data['name']) . ", permission = " . (isset($data['permission']) ? $this->db->escape(serialize($data['permission'])) : '') . " ");
}
public function editUserGroup($user_group_id, $data) {
$this->db->query("UPDATE " . $this->db->dbprefix . "user_group SET
name = " . $this->db->escape($data['name']) . ",
permission = " . (isset($data['permission']) ? $this->db->escape(serialize($data['permission'])) : '') . "
WHERE
user_group_id = '" . (int)$user_group_id . "'
");
}
public function getUserGroup($user_group_id) {
$query = $this->db->query("SELECT DISTINCT * FROM " . $this->db->dbprefix . "user_group WHERE user_group_id = '" . (int)$user_group_id . "' ");
$user_group = array(
'name' => $query->row('name'),
'permission' => unserialize($query->row('permission'))
);
return $user_group;
}
}
查看表单
<?php
if (trim(!$user_group_id)) {
echo form_open('admin/users_group/add', array('class' => 'form-horizontal', 'role' => 'form', 'id' => "form-user-group"));
} else {
echo form_open('admin/users_group/edit/' . $user_group_id, array('class' => 'form-horizontal', 'role' => 'form','id' => "form-user-group"));
}
;?>
<div class="form-group required">
<label class="col-sm-2 control-label" for="input-name"><?php echo $entry_name; ?></label>
<div class="col-sm-10">
<input type="text" name="name" value="<?php echo $name; ?>" placeholder="<?php echo $entry_name; ?>" id="input-name" class="form-control" />
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label"><?php echo $entry_access; ?></label>
<div class="col-sm-10">
<div class="well well-sm" style="height: 150px; overflow: auto;">
<?php foreach ($permissions as $permission) { ?>
<div class="checkbox">
<label>
<?php if (in_array($permission, $access)) { ?>
<input type="checkbox" name="permission[access][]" value="<?php echo $permission; ?>" checked="checked" />
<?php echo $permission; ?>
<?php } else { ?>
<input type="checkbox" name="permission[access][]" value="<?php echo $permission; ?>" />
<?php echo $permission; ?>
<?php } ?>
</label>
</div>
<?php } ?>
</div>
<a onclick="$(this).parent().find(':checkbox').prop('checked', true);"><?php echo $text_select_all; ?></a> / <a onclick="$(this).parent().find(':checkbox').prop('checked', false);"><?php echo $text_unselect_all; ?></a>
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label"><?php echo $entry_modify; ?></label>
<div class="col-sm-10">
<div class="well well-sm" style="height: 150px; overflow: auto;">
<?php foreach ($permissions as $permission) { ?>
<div class="checkbox">
<label>
<?php if (in_array($permission, $modify)) { ?>
<input type="checkbox" name="permission[modify][]" value="<?php echo $permission; ?>" checked="checked" />
<?php echo $permission; ?>
<?php } else { ?>
<input type="checkbox" name="permission[modify][]" value="<?php echo $permission; ?>" />
<?php echo $permission; ?>
<?php } ?>
</label>
</div>
<?php } ?>
</div>
<a onclick="$(this).parent().find(':checkbox').prop('checked', true);"><?php echo $text_select_all; ?></a> /
<a onclick="$(this).parent().find(':checkbox').prop('checked', false);"><?php echo $text_unselect_all; ?></a></div>
</div>
</form>
控制器
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Users_group extends MX_Controller {
private $error = array();
public function __construct() {
parent::__construct();
$this->lang->load('admin/user/users_groups', 'english');
$this->load->model('admin/user/users_group_model');
if(!$this->user->logged()) {
redirect('admin');
} elseif(!$this->user->hasPermissionAccess()) {
redirect('admin/error');
} else {
return true;
}
}
public function index() {
$this->document->setTitle($this->lang->line('heading_title'));
$this->getList();
}
public function add() {
if ($this->request->server['REQUEST_METHOD'] == 'POST') {
$this->load->model('admin/user/users_group_model');
$this->users_group_model->addUserGroup($this->request->post);
redirect('admin/users_group');
}
$this->getForm();
}
public function edit() {
$this->load->model('admin/user/users_group_model');
if (($this->request->server['REQUEST_METHOD'] == 'POST') && $this->validateForm()) {
$user_group_id = $this->uri->segment(4);
$this->users_group_model->editUserGroup($user_group_id, $this->request->post);
$this->session->set_flashdata('success', $this->lang->line('text_success'));
redirect('admin/users_group');
}
protected function getForm() {
$this->load->model('admin/user/users_group_model');
$this->load->library('request');
$data['heading_title'] = $this->lang->line('heading_title');
$data['text_select_all'] = $this->lang->line('text_select_all');
$data['text_unselect_all'] = $this->lang->line('text_unselect_all');
$data['entry_name'] = $this->lang->line('entry_name');
$data['entry_access'] = $this->lang->line('entry_access');
$data['entry_modify'] = $this->lang->line('entry_modify');
$data['button_save'] = $this->lang->line('button_save');
$data['button_cancel'] = $this->lang->line('button_cancel');
$data['breadcrumbs'] = array();
$data['breadcrumbs'][] = array(
'text' => $this->lang->line('text_home'),
'href' => site_url('admin/dashboard')
);
$data['breadcrumbs'][] = array(
'text' => $this->lang->line('heading_title'),
'href' => site_url('admin/users_group')
);
$data['cancel'] = site_url('admin/users_group');
if (!empty($this->error['warning'])) {
$data['error_warning'] = $this->error['warning'];
} else {
$data['error_warning'] = '';
}
if (!empty($this->session->flashdata('success'))) {
$data['success'] = $this->session->flashdata('success', $this->lang->line('text_success'));
} else {
$data['success'] = '';
}
$user_group_id = $this->uri->segment(4);
$data['user_group_id'] = $user_group_id;
if (isset($user_group_id)) {
$data['action'] = site_url('user/users_group/add');
} else {
$data['action'] = site_url('admin/users_group/edit/' . $user_group_id);
}
if (!empty($user_group_id) && $this->request->server['REQUEST_METHOD'] != 'POST') {
$user_group_info = $this->users_group_model->getUserGroup($user_group_id);
}
if (isset($this->request->post['name'])) {
$data['name'] = $this->request->post['name'];
} elseif (!empty($user_group_info)) {
$data['name'] = $user_group_info['name'];
} else {
$data['name'] = '';
}
$ignore = array(
'blank',
'error',
'register',
'dashboard',
'column_left',
'menu',
'startup',
'login',
'logout',
'forgotten',
'reset',
'not_found',
'permission',
'footer',
'header'
);
$data['permissions'] = array();
$files = glob(APPPATH . 'modules/admin/' . 'controllers/*/*.php');
foreach ($files as $file) {
$part = explode('/', dirname($file));
$permission = basename($file, '.php');
if (!in_array($permission, $ignore)) {
$data['permissions'][] = $permission;
}
}
if (isset($this->request->post['permission']['access'])) {
$data['access'] = $this->request->post['permission']['access'];
} elseif (isset($user_group_info['permission']['access'])) {
$data['access'] = $user_group_info['permission']['access'];
} else {
$data['access'] = array();
}
if (isset($this->request->post['permission']['modify'])) {
$data['modify'] = $this->request->post['permission']['modify'];
} elseif (isset($user_group_info['permission']['modify'])) {
$data['modify'] = $user_group_info['permission']['modify'];
} else {
$data['modify'] = array();
}
$this->load->view('user/users_group_form', $data);
}
权限似乎有问题，如果不设置权限，就会中断查询。我建议在这个问题上只保留CI上的活动记录。示例：
public function editUserGroup($user_group_id, $data)
{
$permission = (isset($data['permission']) ? serialize($data['permission']) : null);
$update = array(
'name' => $data['name'],
'permission' => $permission,
);
$this->db->where('user_group_id', $user_group_id);
$this->db->update($this->db->dbprefix . 'user_group', $update);
}
或者使用->set()（个人偏好）更清洁：
public function editUserGroup($user_group_id, $data)
{
$permission = isset($data['permission']) ? serialize($data['permission']) : null;
$this->db->set('permission', $permission);
$this->db->set('name', $data['name']);
$this->db->where('user_group_id', $user_group_id);
$this->db->update($this->db->dbprefix . 'user_group', $update);
}