check and avoid duplicate fields and show custom error message
Posted: 2019-10-12 08:39
Hello to all
I have a "staff_progetto" table.
In the table there are a field "nome" (= name), a field "cognome" (= surname) and a field "cognome_nome".
The "cognome_nome" field automatically concatenate (on change) the "cognome" field and the "nome" field.
I need to prevent that in my database there are duplicates of the COUPLE "cognome" and "nome" fields (for this I created the "cognome_nome" field and I thought I would create a code that prevents the presence of two lines with the same "cognome_nome" text in the database).
I also need to make sure that if someone enters a "nome" and "cognome" so to the concatenated "cognome_nome" field result already present in my table a custom error message appears and the saving of the record with duplicate "cognome_nome" is inhibited.
To do all this, thanks to another user of the forum that I contacted privately, I have a code, but it does not work: no error message appears in the case of a surname / duplicate name and, if I do not set the "unique" feature directly in appgini the field this field is saved normally also if duplicated. If instead I set the "unique" value in the fields the field is not saved but the normal appgini error message appears.
I insert below the code present in my files:
- HOOK -> staff_progetto.php
- HOOK -> staff_progetto-dv.js
- ROOT -> full_name_check.php
I hope someone can help me! Thanks!
Fabiano
1) code in HOOK -> staff_progetto.php ###################################
<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks
function staff_progetto_init(&$options, $memberInfo, &$args){
/* ATTENZIONE: ho tolto questa istruzione perchè consentiva l'inserimento di una concatenazione cognome_nome bypassando la definizione "unique" del campo e generando la successiva inaccessibilità della tabella a causa di un errore
sql("UPDATE staff_progetto SET cognome_nome = CONCAT_WS('', cognome, ' ', nome)", $eo); */
return TRUE;
}
function staff_progetto_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function staff_progetto_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function staff_progetto_before_insert(&$data, $memberInfo, &$args){
$data["cognome_nome"] = $data["cognome"] . " " . $data["nome"];
return TRUE;
}
function staff_progetto_after_insert($data, $memberInfo, &$args){
update_staff_progetto_list();
return TRUE;
}
function staff_progetto_before_update(&$data, $memberInfo, &$args){
$data["cognome_nome"] = $data["cognome"] . " " . $data["nome"];
return TRUE;
}
function staff_progetto_after_update($data, $memberInfo, &$args){
update_staff_progetto_list();
return TRUE;
}
function staff_progetto_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function staff_progetto_after_delete($selectedID, $memberInfo, &$args){
update_staff_progetto_list();
return TRUE;
}
function staff_progetto_dv($selectedID, $memberInfo, &$html, &$args){
if(!in_array($memberInfo['group'], ['Admins'])) {
ob_start();
?>
<script>
$j(function() {
if($j('[name=SelectedID]').val().length) {
$j('#nome').prop('readonly', true);
}
})
</script>
<?php
$html .= ob_get_clean();
}
if(!in_array($memberInfo['group'], ['Admins'])) {
ob_start();
?>
<script>
$j(function() {
if($j('[name=SelectedID]').val().length) {
$j('#cognome').prop('readonly', true);
}
})
</script>
<?php
$html .= ob_get_clean();
}
}
function staff_progetto_csv($query, $memberInfo, &$args){
return $query;
}
function staff_progetto_batch_actions(&$args){
return array();
}
function update_staff_progetto_list() {
// retrieve existing cognome and nome
$staff_progetto = array();
$res = sql("select * from staff_progetto order by cognome", $eo);
while($row = db_fetch_assoc($res))
$staff_progetto[] = "{$row['cognome']} {$row['nome']}";
// save the tags to the options list file
$list_file = dirname(__FILE__) . '/consuntivo_eventi_incontri.enti_operatori_coinvolti.csv';
@file_put_contents($list_file, implode(';;', $staff_progetto));
}
2) code in staff_progetto-dv.js ###############################################
$j(document).ready(function(){
$j('#cognome_nome').attr('type','hidden');
var cogname_val=$j('#cognome_nome').val();
$j('#cognome_nome').before('<span id="cognome_nome_label">'+cogname_val+'</span>');
function updateName()
{
var nome = $j( '#nome' ).val();
var cognome = $j( '#cognome' ).val();
$j( '#cognome_nome_label' ).text(cognome+' '+nome);
$j( '#cognome_nome' ).val(cognome+' '+nome);
var SelectedID=$j('[name=SelectedID]').val();
$j.ajax({
url:'full_name_check.php',
type:'post',
dataType:'json',
data:{'full_name':cognome+' '+nome,'SelectedID':SelectedID},
success:function(resp)
{
if(resp['availability']==1)
{
// alert('Il nome completo esiste già ');
$j('#cognome_nome-uniqueness-note').show();
setTimeout(function(){
$j('#cognome_nome-uniqueness-note').hide('slow');
},1500);
}
},
error:function(err)
{
console.log(err);
}
});
}
$j('#cognome,#nome').on('change', function() {
updateName();
});
});
3) code in ROOT -> full_name_check.php ###################################
<?php
include('lib.php');
$full_name=makesafe($_POST['full_name']);
$SelectedID=makesafe($_POST['SelectedID']);
$q="SELECT COUNT(*) FROM staff_progetto WHERE LOWER(cognome_nome)='".strtolower($full_name)."'";
if(!empty($SelectedID) && is_numeric($SelectedID))
$q.=" AND pri_key!='".$SelectedID."'";
$res_count=sqlValue($q);
if($res_count>0)
$data['availability']=1;
else
$data['availability']=2;
echo json_encode($data);
?>
THANK YOU TO ALL FOR HELP!
Fabiano
I have a "staff_progetto" table.
In the table there are a field "nome" (= name), a field "cognome" (= surname) and a field "cognome_nome".
The "cognome_nome" field automatically concatenate (on change) the "cognome" field and the "nome" field.
I need to prevent that in my database there are duplicates of the COUPLE "cognome" and "nome" fields (for this I created the "cognome_nome" field and I thought I would create a code that prevents the presence of two lines with the same "cognome_nome" text in the database).
I also need to make sure that if someone enters a "nome" and "cognome" so to the concatenated "cognome_nome" field result already present in my table a custom error message appears and the saving of the record with duplicate "cognome_nome" is inhibited.
To do all this, thanks to another user of the forum that I contacted privately, I have a code, but it does not work: no error message appears in the case of a surname / duplicate name and, if I do not set the "unique" feature directly in appgini the field this field is saved normally also if duplicated. If instead I set the "unique" value in the fields the field is not saved but the normal appgini error message appears.
I insert below the code present in my files:
- HOOK -> staff_progetto.php
- HOOK -> staff_progetto-dv.js
- ROOT -> full_name_check.php
I hope someone can help me! Thanks!
Fabiano
1) code in HOOK -> staff_progetto.php ###################################
<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks
function staff_progetto_init(&$options, $memberInfo, &$args){
/* ATTENZIONE: ho tolto questa istruzione perchè consentiva l'inserimento di una concatenazione cognome_nome bypassando la definizione "unique" del campo e generando la successiva inaccessibilità della tabella a causa di un errore
sql("UPDATE staff_progetto SET cognome_nome = CONCAT_WS('', cognome, ' ', nome)", $eo); */
return TRUE;
}
function staff_progetto_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function staff_progetto_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function staff_progetto_before_insert(&$data, $memberInfo, &$args){
$data["cognome_nome"] = $data["cognome"] . " " . $data["nome"];
return TRUE;
}
function staff_progetto_after_insert($data, $memberInfo, &$args){
update_staff_progetto_list();
return TRUE;
}
function staff_progetto_before_update(&$data, $memberInfo, &$args){
$data["cognome_nome"] = $data["cognome"] . " " . $data["nome"];
return TRUE;
}
function staff_progetto_after_update($data, $memberInfo, &$args){
update_staff_progetto_list();
return TRUE;
}
function staff_progetto_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function staff_progetto_after_delete($selectedID, $memberInfo, &$args){
update_staff_progetto_list();
return TRUE;
}
function staff_progetto_dv($selectedID, $memberInfo, &$html, &$args){
if(!in_array($memberInfo['group'], ['Admins'])) {
ob_start();
?>
<script>
$j(function() {
if($j('[name=SelectedID]').val().length) {
$j('#nome').prop('readonly', true);
}
})
</script>
<?php
$html .= ob_get_clean();
}
if(!in_array($memberInfo['group'], ['Admins'])) {
ob_start();
?>
<script>
$j(function() {
if($j('[name=SelectedID]').val().length) {
$j('#cognome').prop('readonly', true);
}
})
</script>
<?php
$html .= ob_get_clean();
}
}
function staff_progetto_csv($query, $memberInfo, &$args){
return $query;
}
function staff_progetto_batch_actions(&$args){
return array();
}
function update_staff_progetto_list() {
// retrieve existing cognome and nome
$staff_progetto = array();
$res = sql("select * from staff_progetto order by cognome", $eo);
while($row = db_fetch_assoc($res))
$staff_progetto[] = "{$row['cognome']} {$row['nome']}";
// save the tags to the options list file
$list_file = dirname(__FILE__) . '/consuntivo_eventi_incontri.enti_operatori_coinvolti.csv';
@file_put_contents($list_file, implode(';;', $staff_progetto));
}
2) code in staff_progetto-dv.js ###############################################
$j(document).ready(function(){
$j('#cognome_nome').attr('type','hidden');
var cogname_val=$j('#cognome_nome').val();
$j('#cognome_nome').before('<span id="cognome_nome_label">'+cogname_val+'</span>');
function updateName()
{
var nome = $j( '#nome' ).val();
var cognome = $j( '#cognome' ).val();
$j( '#cognome_nome_label' ).text(cognome+' '+nome);
$j( '#cognome_nome' ).val(cognome+' '+nome);
var SelectedID=$j('[name=SelectedID]').val();
$j.ajax({
url:'full_name_check.php',
type:'post',
dataType:'json',
data:{'full_name':cognome+' '+nome,'SelectedID':SelectedID},
success:function(resp)
{
if(resp['availability']==1)
{
// alert('Il nome completo esiste già ');
$j('#cognome_nome-uniqueness-note').show();
setTimeout(function(){
$j('#cognome_nome-uniqueness-note').hide('slow');
},1500);
}
},
error:function(err)
{
console.log(err);
}
});
}
$j('#cognome,#nome').on('change', function() {
updateName();
});
});
3) code in ROOT -> full_name_check.php ###################################
<?php
include('lib.php');
$full_name=makesafe($_POST['full_name']);
$SelectedID=makesafe($_POST['SelectedID']);
$q="SELECT COUNT(*) FROM staff_progetto WHERE LOWER(cognome_nome)='".strtolower($full_name)."'";
if(!empty($SelectedID) && is_numeric($SelectedID))
$q.=" AND pri_key!='".$SelectedID."'";
$res_count=sqlValue($q);
if($res_count>0)
$data['availability']=1;
else
$data['availability']=2;
echo json_encode($data);
?>
THANK YOU TO ALL FOR HELP!
Fabiano