Checking for duplicate records (unique record)
This article is also available in the following languages:
Expanded tutorial from CakePHPWiki: http://wiki.cakephp.org/tutorials:duplicate_record_validation
- validate a form field (such as a user name field), both in add and edit form and make sure that the selected user name does not already exist in the database
- function repeated only once (in app/app_model.php)
In app/app_model.php:
Example of select (the result of the above function):
SELECT COUNT(*) AS count FROM `users` AS `User` WHERE (`User`.`username` = 'por') AND (`User`.`id` <> 1)
Model Class:
<?php
class AppModel extends Model {
function isUnique($field, $value, $id)
{
$fields[$this->name.'.'.$field] = $value;
if (empty($id))
// add
$fields[$this->name.'.id'] = "<> NULL";
else
// edit
$fields[$this->name.'.id'] = "<> $id";
$this->recursive = -1;
if ($this->hasAny($fields))
{
$this->invalidate('unique_'.$field);
return false;
}
else
return true;
}
?>
isUnique($field, $value, $id) - $field: name of field (e.g. 'username'); $value: value of field (e.g. this->data['User']['username']); $id - id line which is edited, when "add form" then $id = null;Example of select (the result of the above function):
SELECT COUNT(*) AS count FROM `users` AS `User` WHERE (`User`.`username` = 'por') AND (`User`.`id` <> 1)
Controller Class:
<?php
if ($this->User->isUnique('username', $this->data['User']['username'], $user_id))
{
if ($this->User->save($this->data))
{
$this->flash('User has been saved.','/users/index');
}
}
?>
View Template:
<?php echo $html->tagErrorMsg('User/unique_username', 'Enter another username, this is already used.'); ?>
<?php echo $html->input('User/username', array('size' => '30'))?
The message with name Model/unique_fieldname is display, when isUnique returns false.
/app/app_mode.php
Model Class:
<?php
class AppModel extends Model
{
/**
* Checks to see if multiple values exist in the same row in the database,
* this is used when we have multiple fields making up the unique
* contraint in MySQL
*
* @param array $params Array consisting of "field" => "value"
* @param int $id PK of record being editited (optional)
*/
function isUnique($params, $id="")
{
if (!is_array($params)) {
trigger_error(__METHOD__ . ' - $params must be an array', E_USER_ERROR);
}
// @var array $query Array to $this->hasAny() against
$query = array();
// Set Recursive Seach mode.
$this->recursive = -1;
// Loop array of params building our our query array.
foreach ($params as $field => $value)
{
$query[$this->name . '.' . $field] = $value;
}
// Check to see if we need to query against an id
if (empty($id))
$fields[$this->name.'.id'] = "!= NULL";
else
$fields[$this->name.'.id'] = "!= {$id}";
// Run the query.
if ($this->hasAny($query)) {
// $this->invalidate('unique_'.$field);
return false;
}
else
return true;
}
}
?>
You can then perform the check with beforeValidate() in your model as so:
/app/models/yourmodel.php
Model Class:
<?php
function beforeValidate()
{
// MySQL Unique Constraint Checks
$unique_check = array(
'first_name' => $this->data[$this->name]["first_name"],
'last_name' => $this->data[$this->name]["last_name"],
'gender' => $this->data[$this->name]["gender"]
);
if (!$this->isUnique($unique_check))
$this->invalidate('unique');
}
?>
Since I had to deal with legacy primary keys, I modified your code in the AppModel this way:
if (empty($id))
// add
$fields[$this->name. '.' . $this->primaryKey] = "<> NULL";
else
// edit
$fields[$this->name. '.' . $this->primaryKey] = "<> $id";
Thank you very much!
if (empty($id)){
// add
$condition = $this->name.".".$field." = '".$value."'";
}else{
// edit
$fields[$this->name.'.id'] = "<> $id";
$conditions = '('.$this->name.".".$field." = '".$value."') AND (".$this->name.".id <> $id)";
}
$this->recursive = -1;
if ($this->hasAny($conditions))
It seems that my installation did not like the "==" and "<>" in the conditions array.
May thanks for the Tutorial!
Still saved me MANY hours!
and I've learned a bit more about this wonderfull product!
Cheers :)
function beforeValidate(){
...
$this->invalidate('title_unique');
return true;
}
if (empty($id))
// add
$fields[$this->name.'.id'] = "<> NULL";
else
// edit
$fields[$this->name.'.id'] = "<> $id";
The correct form:
if (empty($id))
// add
$fields[$this->name.'.id'] = "!= NULL";
else
// edit
$fields[$this->name.'.id'] = "!= $id";
Comments are closed for articles over a year old