Rui,
happy to share my work, hoping to be helpful (and someone can improve it).
This is what I've done:
1) create an html object called
htmlResponse on the form with this in html tab:
2) add this code in On Blur field of the object to control:
Code: Select all
checkField("client", "cli_phone"); // change these according to your needs
3) add this code in Custom Code > Javascript:
Code: Select all
/*
* Check field value in database
*
* @param string tableName Table name to search in
* @param string fieldName Field name to search in
*
* @return string Text message is displayed in 'htmlResponse' html object
*/
function checkField(tableName, fieldName)
{
var fieldValue = $("#"+fieldName).val();
if (fieldValue.length == 0) {
$("#htmlResponse").html("");
} else {
// Search DB via AJAX call
$.ajax({
type: "GET",
url: "/db/yourDBName/checkField.php",
data: "t="+tableName+"&f="+fieldName+"&v="+fieldValue,
dataType: "html",
success: function(msg){
var offset = $("#"+fieldName).offset();
var ot = offset.top;
var ol = offset.left;
$("#htmlResponse").html(msg);
$("#htmlResponse").css({
// set some styles for returning text message
"position" :"absolute",
"top" :ot - 90,
"left" :ol + 150,
"color" :"red",
"font-weight" :"bold"
});
},
error: function(){
$("htmlResponse").html("No AJAX call!");
}
});
}
}
4) create a file checkField.php in db/yourDBName folder whit this code:
Code: Select all
<?php
/**
* Check if field value already exist in database
*
* @param string $t Table name to search in
* @param string $f Field name to search in
* @param string $v Value to search
*
* @return string Text message if value is found
*/
// Text message to return if value is found
define('MSG_ON_FOUND', 'This value is already in database.');
$msg = '';
$table = isset($_GET['t']) ? filter_var($_GET['t'], FILTER_SANITIZE_STRING) : '';
$field = isset($_GET['f']) ? filter_var($_GET['f'], FILTER_SANITIZE_STRING) : '';
$value = isset($_GET['v']) ? filter_var($_GET['v'], FILTER_SANITIZE_STRING) : '';
if ($table != '' && $field != '' && $value != '') {
// Connect to database
require 'config.php';
$DB = new mysqli($DBHost, $DBUser, $DBPassword, $DBName);
if ($DB->connect_errno) {
$msg = 'Connection error (' . $DB->connect_errno . ') '
. $DB->connect_error;
} else {
// Search field value in DB
$query = "SELECT COUNT($field) FROM $table WHERE $field = '$value' ";
$res = $DB->query($query);
$num = $res->fetch_row();
$num = $num[0];
if (!is_null($num) && $num > 0) {
$msg = MSG_ON_FOUND;
}
/* Free result set and close connection */
$res->close();
$DB->close();
}
}
print $msg;
?>
If a duplicate value is found in database, user is immediately alert when he leaves (blur) the field.
- Message shown if the value is found in database
- result.png (5.23 KiB) Viewed 3918 times
That's all!
But if you want to stop duplicates
before save a better and simpler way is to check all values in JavaScript > beforeSave() function.
In this case, insert this in Costom Code > BeforeSave (change the two parameters according to your needs):
Code: Select all
function nuBeforeSave()
{
// change this two parameters according to your needs
if ( !isDuplicateEntry('your_subform_name', 'your_field_name') ) {
return true;
}
}
/*
* Check duplicate field in a subform
*
* @param string pSubformName The name of the subform
* @param string fieldName The field name
*
* @return boolean True if a duplicate value is found
*/
function isDuplicateEntry(pSubformName, fieldName)
{
// Creates an array of the prefixes used for each row of the subform
var rowID = nuSubformRowArray(pSubformName, true);
// check if there is a duplicate value in subform field
tempArr = Array();
for (var i = 0 ; i < rowID.length; i++) {
// get the value of the field
fieldValue = $("#"+rowID[i]+fieldName).val();
if (tempArr.indexOf(fieldValue) >= 0) {
alert('Duplicate entry!');
return true;
} else {
tempArr.push($("#"+rowID[i]+fieldName).val());
}
}
return false;
}
Hope this helps.
Max