CodeIgniter - Checking to see if a value already exists in the database

Im running CodeIgniter for a project of mine ... Im fairly new to this, i have the standard form validation checks for my form, except im unsure of how to check if the value already exists in the database.

This is what i have for my form validation rules so far

$this->form_validation->set_rules('rolename', 'Role Name', 'trim|required|xss_clean');
$this->form_validation->set_rules('rolekey', 'Role Key', 'trim|required|xss_clean');

The 'rolekey' is what i need to check against the 'rolek开发者_开发技巧ey' column in the database to see if it exists, if it does i need to shoot back an error.

Any help would be grand.

Cheers,

There is not a built-in form validation check for whether or not a value is in the database, but you can create your own validation checks.

In your controller, create a method similar to this:

function rolekey_exists($key)
{
    $this->roles_model->role_exists($key);
}

And in your model that handles roles, add something like this:

function role_exists($key)
{
    $this->db->where('rolekey',$key);
    $query = $this->db->get('roles');
    if ($query->num_rows() > 0){
        return true;
    }
    else{
        return false;
    }
}

And then you can write a form validation check like this:

$this->form_validation->set_rules('username', 'Username', 'callback_rolekey_exists');

See this page for more information:

https://www.codeigniter.com/user_guide/libraries/form_validation.html#callbacks-your-own-validation-methods

If you want to make your validation function available in more than one controller, you should include the rule in a MY_Form_validation library.

I do this in MY_Form_validation:

function exist($str, $value){       

  list($table, $column) = explode('.', $value, 2);    
  $query = $this->CI->db->query("SELECT COUNT(*) AS count FROM $table WHERE $column = $str'");
  $row = $query->row();

  return ($row->count > 0) ? FALSE : TRUE;

}

Then in your model (or controller) when you set your rules:

$this->form_validation->set_rules('username','username','exist[users.user_name]');

The rule calls the exist function.

The exist function explodes the string users.user_name at the dot and then queries the database to see if a record is returned. If it does, return false, else true. The rule will fail on a false return.

You just edit your validation rule

$this->form_validation->set_rules('rolename', 'Role Name', 'trim|required|xss_clean|is_unique[table_name.rolename]'); 

you can always just add

$this->form_validation->set_rules('username','Username','is_unique[users.username]');

where user.username is the table.column

Add the is_unique rule to your form validation as below

$this->form_validation->set_rules('rolename', 'Role Name', 'trim|required|xss_clean|is_unique[your_database_table_name.rolename]');
$this->form_validation->set_rules('rolekey', 'Role Key', 'trim|required|xss_clean|is_unique[your_database_table_name.rolekey]');

This works for me might work for someone

In your controller, create a method similar to this:

function rolekey_exists('table','field','value')
{
  $this->roles_model->role_exists($key);
}

And in your model that handles roles, add something like this:

function role_exists($table,$field,$value)
{
    $this->db->where($field,$value);
    $query = $this->db->get($table);
    if (!empty($query->result_array())){
        return 1;
    }
    else{
        return 0;
    }
}

If you are using the new Codeigniter4 you can actually achieve that without need to add any method in your controller: with the is_not_unique rule you can check if a value is found in the database and return an error if it doesn't...basically doing the opposite of is_unique!

Yes, if you want to check whether a value already exist in the database, it's correct to add is_unique in the validation side. So, you don't need to create a function in your controller to do it.

$this->form_validation->set_rules('rolename', 'Role Name', 'trim|required|xss_clean|is_unique[table_name.table_field]');

$this->form_validation->set_rules('rolekey', 'Role Key', 'trim|required|xss_clean|is_unique[table_name.table_field]');

Then, you will see an error message in the input area. For example: The username field must contain a unique value.

I have tested it in Codeigniter 3.0.0.

Little update for Codeigniter4

In CI4 you can use the validation rule "is_not_unique[table.field]" so do it in this way

$validation->setRule('email', 'Email', 'trim|required|valid_email|is_not_unique[users.email]');