Validating UNIQUE fields in Codeigniter with 2 indexes

Question

In the Codeigniter Framework, I can validate a unique field in a MYSQL database using the "form validation class". Example:

$this->form_validation->set_rules('form_field', 'form_label', 'is_unique[table.field]'); 

Work fine, but I need to check a field from a table with index 2. Exemple:

 UNIQUE INDEX `id_aluno` (`id_aluno`, `ano`), 

Can the Codeigniter Framework do this natively?

Answer 1

I don’t think CI has a built-in case for combo PK , but I would use callback _ like this: but note that you need to send the second PK as optional, and the rule should be applied to the first $ PK, see callbacks for more information about this

 $this->form_validation->set_rules('form_field', 'form_label', 'callback_combpk[$pk2]'); public function combpk($pk1, $pk2) { $this->db->where('field1', $pk1); $this->db->where('field2', $pk2); $result = $this->db->get('table'); if($result->num_rows() > 0) { $this->form_validation->set_message('combpk','something'); // set your message return false; } else{ return true;} } 

Answer 2

Could not find a description of the native support for this function in CodeIgniter. You can check the database error number after an INSERT request. For instance:

 $last_id = $this->model->set(); if ($last_id === FALSE) if ($this->db->_error_number() == 1062) $this->data['message_error'] = 'Not unique.'; else $this->data['message_error'] = 'Database error.'; 

This method has drawbacks, but certainly has the advantage of not using an extra SELECT query.

PS If there are several different composite unique indices, then of course you can use preg_match(<pattern_with_index_name>, $this->db->_error_message()); .

Answer 3

 $this->form_validation->set_rules( 'form_field', 'form_label', 'trima|is_unique[table1.field1],trim|required|is_unique[table.field2]'); 

use

 'trima|is_unique[table1.field1],trim|required|is_unique[table.field2]' 

in one code

Answer 4

Maybe you are interested in my custom function is_unique. here it is.

You can use it in two ways:

 1. is_unique[table_name.field_to_check.id_field_name.id_field_value] //<-- unique 1 field only 2. is_unique[table_name.field_to_check.id_field_name != 'id_field_value' and anotherIdName='theValue'] //<-- custom where 

Just save this code in a file, name it MY_Form_Validation.php and put it in the library directory. Then you can put these is_unique

  public function is_unique($str, $field) { $result = true; try { if ($str) {//validate only if there a value submitted $is_query = 0; $x = substr_count($field, '.'); //count of dots //ex: is_unique[is_unique[$table_name.$field_name.$field_id!='$id' and 1=1]] //ex: is_unique[$table_name.$field_name.id!='2' and name!='simson'] if($x == 2) { list($table, $field, $where) = explode('.', $field); $is_unique = 0; if ($where) { $logos = "select * from $table where $field =? and $where "; } else { $logos = "select * from $table where $field =? "; } $data = array($str); $qq = $this->CI->db->query($logos, $data); $is_query = 1; $row = $qq->row(); $is_unique = !(bool)$row; //is_unique = (row == empty) $result = (bool)$is_unique; } else { if ($x >= 3) { list($table, $field, $id_field, $id_val) = explode('.', $field); $is_unique = 0; if ($id_field && $id_val) { $logos = "select * from $table where $field =? and $id_field != '$id_val' "; } else { $logos = "select * from $table where $field =? "; } $data = array($str); $qq = $this->CI->db->query($logos, $data); $is_query = 1; $row = $qq->row(); if ($row) { if ($row->id) { if ($row->$id_field == $id_val) { $is_unique = 1; //means editing it self, allow it } else { //already exists with different id } } else { //used for left join table } } else { $is_unique = 1; //does not exists } $result = (bool)$is_unique; } else if ($x == 1) { list($table, $field) = explode('.', $field); $query = $this->CI->db->limit(1)->get_where($table, array($field => $str)); $is_query = 1; $result = $query->num_rows() === 0; } } if (is_log_query() && $is_query) { $logos = "logos is_unique x==$x: " . $this->CI->db->last_query(); log_to_file($logos); } else { $logos = "logos is_unique x==$x: NOT EXECUTED"; log_to_file($logos); } } }catch (Exception $e) { die($e->getTraceAsString()); } return $result; }