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

Question

I'm running CodeIgniter for a project of mine ... I have the standard form validation checks for my form, except I'm 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 'rolekey' column in the database to see if it exists, if it does I need to shoot back an error.

Stack Programmer · Accepted Answer · 2018-11-09 14:45:50Z

57

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

edited Nov 9, 2018 at 14:45

Stack Programmer

6796 silver badges18 bronze badges

answered Nov 28, 2010 at 13:47

dvcolgan

7,77810 gold badges62 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Chris Over a year ago

Thanks for the comment ... thats perfect ... Could you give me an example of the code i should use for the function? ... im still trying to get the hang of writing everything in functions. thanks

Chris Over a year ago

Thats perfect ... Thank you so much ... Just one more question, From that how does it return the error message 'rolekey already exists in the database'

Ross Over a year ago

within your role_exists function, add this: $this->validation->set_message('role_exists', 'The %s already exists'); - put it just before the return true. also i think your returns might be confused - (might not matter but looking at CI docs). return FALSE if the validation fails, return TRUE if validation succeeds. I believe the code above will return true if there is a duplicate role. (meaning it will fail when a value DOESN'T exist. more @ codeigniter.com/user_guide/libraries/validation.html

Chris Over a year ago

Thanks for that ... Im getting an error in regards to the set_message in my model Call to a member function set_message() on a non-object

Steven McConnon Over a year ago

This is very good but I think ideally you'd move the if statement to the controller and not have any logic in the model.

mos fetish · Accepted Answer · 2010-12-02 08:38:54Z

14

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.

answered Dec 2, 2010 at 8:38

mos fetish

5062 silver badges13 bronze badges

1 Comment

Andrew Liu Over a year ago

In you query you have missed a '

laaposto · Accepted Answer · 2014-07-29 11:36:46Z

11

You just edit your validation rule

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

edited Jul 29, 2014 at 11:36

laaposto

12.2k15 gold badges60 silver badges73 bronze badges

answered Jul 29, 2014 at 11:19

prash.patil

6878 silver badges7 bronze badges

2 Comments

Dr Marble Over a year ago

I agree, this would be the easiest method now. At the time this question was originally asked, however, the is_unique validation rule did not yet exist (it was added in CodeIgniter version 2.1, released November 14, 2011).

Ask Warvin Over a year ago

it worked for me, I am using codeigniter 4

CMPS · Accepted Answer · 2015-08-21 04:44:53Z

6

you can always just add

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

where user.username is the table.column

edited Aug 21, 2015 at 4:44

CMPS

7,7874 gold badges32 silver badges53 bronze badges

answered Jan 2, 2014 at 12:19

guest

611 silver badge1 bronze badge

Comments

B.K · Accepted Answer · 2017-12-20 10:57:08Z

1

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]');

answered Dec 20, 2017 at 10:57

B.K

90711 silver badges6 bronze badges

Comments

Deepak · Accepted Answer · 2018-07-20 10:35:08Z

1

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

answered Jul 20, 2018 at 10:35

Deepak

6341 gold badge7 silver badges21 bronze badges

Comments

Vanny LaRoche · Accepted Answer · 2021-02-17 18:07:18Z

1

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!

answered Feb 17, 2021 at 18:07

Vanny LaRoche

464 bronze badges

Comments

Anan Bahrul Khoir · Accepted Answer · 2020-01-18 02:13:24Z

0

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.

answered Jan 18, 2020 at 2:13

Anan Bahrul Khoir

33 bronze badges

Comments

relo80 · Accepted Answer · 2021-02-17 03:50:52Z

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]');

answered Feb 17, 2021 at 3:50

relo80

2851 gold badge4 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

9 Answers 9

5 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

5 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related