Check for duplicated entry

[FRW]

A record wich have two fields: a category-number (f.e.:100) and a descendig number (1 or 2 or 3 aso...) (unique only for this category). So the combination of category-number and descending number will be unique.
I want to check, if there are duplicate records in the DB, but how?

I thought about calculated field (category-number + descending number => 100 + 1 = 101) and check the calculated field. But I saw,
Any suggestions how to do this?

 

[eugenio]

Hello,
you can add a unique constraint on your db (composed by the two fields), in this way you are sure that you cannot have two records having same category+number.
In addition to that, you can create a custom validation function for one of the two fields, that checks if there are duplication before insert/update

Best,

 

[FRW]

Thank you, DBMS are miracles....

The first thing (unique in DB) works, but leads to an error message in dadabik-App (of course), so if I want to check this with a custom validation function, I have to connect and check the DB within a validation function, right?

 

[eugenio]

Exactly, but I would also keep the unique constraint, that is an additionally layer of security that guarantees that you will never have duplications.

Best,

 

[FRW]

Tried this, but no effect:

table = Erklaerung
(unique) field= bestmaid_intern
function as formatting function for field bestmaid_intern

[pre]
function dadabik_duplikat($value){

global $conn;

$sql = "'SELECT count(*) FROM Erklaerung WHERE bestmaid_intern = '.$value.'";
$res_prepare = prepare_db($conn, $sql);
$res_bind = bind_param_db($res_prepare, 'bestmaid_intern', $value);
$res = execute_prepared_db($res_prepare,0);

if ($res >1){
return '<span style="color:red">'.$value.'</span>';
}
else{
return $value;
}
}
[/pre]

 

[eugenio]

Hello,
this seems a custom formatting function, you should create a custom validation function that checks if a record with same value for field A and field B is already available.

Best,

 

[FRW]

I do a formatting function for testing purpose, because I want to avoid the record lock, if the function has errors after clicking on insert.

 

[FRW]

I updated my trying, but still no success:

No matter what I have as value for $vergleich, $res returns = 1 and the validation fails (as it should...)
But even if the value for $vergleich is not in the DB (looked after this), $res = 1...????

btw.: echo-statements just for testing....

f.e.:
bestmaid = 100
bew_bestmaid = 1
bestmaid_intern (written to DB from a calculated field) = 101

Validation bound at field "bew_bestmaid"; even if "bew_bestmaid" = 2 and there is no 102 in column "bestmaid_intern", $res = 1...

[pre]
function dadabik_duplikat($parameters_ar){

$vergleich = $parameters_ar['bestmaid'] + $parameters_ar['bew_bestmaid'];

global $conn;

$sql = "SELECT count(*) FROM Erklaerung WHERE bestmaid_intern = :vergleich";
$res_prepare = prepare_db($conn, $sql);
$res_bind = bind_param_db($res_prepare, ':vergleich', $vergleich);
$res = execute_prepared_db($res_prepare,0);

echo $vergleich;
echo '-';
echo $res;

if ($res >=1){
return false;
}
else{
return true;
}
}
[/pre]

 

[eugenio]

Hello,
the code is wrong, you should fetch the row to get the result of the query.
Check dadabik_send_notice_after_accounts_insert in custom_functions.php, that's a complete example of query execution and fetching.

Best,

 

[FRW]

Okay, got it:

[pre]
function dadabik_duplikat($parameters_ar){

$vergleich = $parameters_ar['bestmaid'] + $parameters_ar['bew_bestmaid'];

global $conn;

$sql = "SELECT count(*) AS anzahl FROM Erklaerung WHERE bestmaid_intern = :vergleich";
$res_prepare = prepare_db($conn, $sql);
$res_bind = bind_param_db($res_prepare, ':vergleich', $vergleich);
$res = execute_prepared_db($res_prepare,0);
$row = fetch_row_db($res_prepare);

$anzahl = $row[anzahl];

if ($anzahl >=1){
return false;
}
else{
return true;
}
}
[/pre]

but now I have a more logical problem:
If I do an insert, the code above is right, because it triggers an error, when the number is in the DB
But, if I'm editing the record, it also throws an error, because the number is in the DB

Is there a possibility to ask wether it is "insert" or "edit"?

 

[eugenio]

You should check if $_GET['function'] === 'insert'


Happy coding,

 

[eugenio]

But you should have both the fields as edit disabled, otherwise via update you can bypass the duplication check

 

[FRW]

You're right, so I am thinking about a duplication check with a JS onchange:event.
But then I have to change from php to javascript in this function, right?

Is there a way to do this in php with hooks (before update) and prevent updating, wenn error accurs?

To explain:
In the workflow the record is inserted without the fields "bestmaid" and "bew_bestmaid". These information has to be filled in later, so we are in edit mode. I have to check if there is a duplication, when changing this fields - if other fields are changed (and these two not), so the form shall do the update without duplicatecheck.

In other words:
edit mode, fields first time updated with data = duplicatecheck =1 shall give the error
edit mode, these fields with data, other fields updated = duplicatecheck >1 shall give the error

btw.:

I'm soooo happy...:S

 

[eugenio]

You have to write the php code that covers all the cases, there isn't any other way.
- if you are in edit and the fields changed respect to what you have in the db - > check duplication
- if you are in edit and the fields don't change respect to what you have in the db - > don't check duplication
- if you are in insert -> check duplication

if you are not confident with coding, you can avoid check in update and leave only the db constraint ... users might get a ugly message from time to time but the consistency of your data will be safe.

 

[FRW]

So far so good...

Is there an easy way to check during the validation function, if the fields had changed?

 

[eugenio]

The only way is retrieve what you have in the DB and check if the values have changed respect to what you got from the form.


Best,

 

[FRW]

I'm proud, it works:

[pre]
function dadabik_duplikat($parameters_ar){

$vergleich = $parameters_ar['bestmaid'] + $parameters_ar['bew_bestmaid'];
$id = $_GET['where_value'];

global $conn;

$sql = "SELECT count(*) AS anzahl FROM Erklaerung WHERE bestmaid_intern = :vergleich";
$res_prepare = prepare_db($conn, $sql);
$res_bind = bind_param_db($res_prepare, ':vergleich', $vergleich);
$res = execute_prepared_db($res_prepare,0);
$row = fetch_row_db($res_prepare);
$anzahl = $row[anzahl];

//Holt alten Wert aus der DB für den Abgleich, im Updatefall, ob BestMa-ID geändert
$sql2 = "SELECT * FROM Erklaerung WHERE idErklaerung = :id";
$res_prepare = prepare_db($conn, $sql2);
$res_bind = bind_param_db($res_prepare, ':id', $id);
$res = execute_prepared_db($res_prepare,0);
$row = fetch_row_db($res_prepare);
$bestmaid_DB = $row[bestmaid_intern];


//Hier die Unterscheidung ob 1=Neuer Eintrag oder 2=Update mit neuen oder 3=Update mit vorhandenen Werten

if ($_GET['function'] === 'insert' && $anzahl >=1){
return false;
}
elseif ($_GET['function'] === 'update' && $bestmaid_DB != $vergleich && $anzahl >=1){
return false;
}
elseif ($_GET['function'] === 'update' && $bestmaid_DB == $vergleich ){
return true;
}
else
{
return false;
}
}
[/pre]