Ensuring uniqueness in your tables

Posted by Tim Koschützki, on Mar 26, 2007 - in PHP & CakePHP » DataSources, Models & Behaviors

Deprecated post

The authors of this post have marked it as deprecated. This means the information displayed is most likely outdated, inaccurate, boring or a combination of all three.

Policy: We never delete deprecated posts, but they are not listed in our categories or show up in the search anymore.

Comments: You can continue to leave comments on this post, but please consult Google or our search first if you want to get an answer ; ).

Imagine that you want to make sure that a username that is already recorded in the database is not used again. Such a feature is often used for login systems, where both email and user names are tracked down and refused, if there is already a user in the database with that name or email.

With this handy function in this can get very easy. Simply copy the source code to your AppModel class, which is found in your /cake/ folder. Then move on to the next steps to make this work.

php
  1. function isUnique($field, $value, $id='') {
  2.         $fields[$this->name.'.'.$field] = $value;
  3.         if (empty($id))
  4.             // add
  5.             $fields[$this->name.'.id'] = "<> NULL";
  6.         else
  7.             // edit
  8.             $fields[$this->name.'.id'] = "<> $id";
  9.        
  10.         $this->recursive = -1;
  11.         if ($this->hasAny($fields))
  12.         {
  13.             $this->invalidate('unique_'.$field);
  14.             return false;
  15.         }
  16.         else
  17.             return true;
  18.     }

You need to pass the form field name, its value and an optional id to it, with which you can exclude a given database row from the search. This is important to make editing work. Say for example the user with name "Peter" changes his email. If you preserve the name "Peter" in the username form field, using this function without passing it the id of Peter's row would not work. It would always reject taking on Peter's name, as there is already one "Peter" in the database.

What does your view look like?

You have to provide a tag error message for the form field with the form field's name prefixed by 'unique_':

html
  1. <p>
  2.   <label>First Name</label>
  3.   <?php echo $html->input('Member/first_name',array('class'=>'txt'));?>
  4.   <?php echo $html->tagErrorMsg('Member/unique_first_name','First Name is already taken!');?>
  5. </p>

Making adding and editing rows work in the controller

Adding rows

php
  1. if(!empty($this->data['Member'])) {
  2.   if($this->Member->isUnique('first_name',$this->data['Member']['first_name'])) {
  3.     $this->Member->save($this->data['Member']);
  4.   }
  5. }

Simple, isn't it?

Editing rows

Note that you have to provide the row id here as we discussed:

php
  1. if(!empty($this->data['Member'])) {
  2.   if($this->Member->isUnique('first_name',$this->data['Member']['first_name'],$this->data['Member']['id'])) {
  3.     $this->Member->save($this->data['Member']);
  4.   }
  5. }

The id must be stored in a hidden field on the edit form, as you would normally do anyway to make editing work:

html
  1. <p>
  2.   <?php echo $html->hidden('Member/id');?>
  3.  
  4.   <label>First Name</label>
  5.   <?php echo $html->input('Member/first_name',array('class'=>'txt'));?>
  6.   <?php echo $html->tagErrorMsg('Member/unique_first_name','First Name is already taken!');?>
  7. </p>

Happy coding! :)