如果所有Unchecked权限都不更新db

Question

When the admin user unchecks all the user groups does not clear the permissions from db and throws error. If no permissions are selected and the form is submitted it should clear the permissions column belonging to that id. But does not for some reason?

What is best way to make it work?

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

User Group Model

<?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;
}
}

View Form

<?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>

Controller

<?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);

}

Answer 1

There seems to the problem on the permissions when its not being set it breaks up the query. I suggest just the active record on CI on this one. Example:

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);
}

Or much cleaner using ->set() (personal preference):

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);
}