Page 1 of 2
Duplicate check - multiple fields
Posted: Mon Feb 21, 2022 3:45 pm
by chrisfch
I have 3 IBAN fields / columns in one table / form.
Is there a way to have a duplicate-check over all 3 fields / columns before save?
Re: Duplicate check - multiple fields
Posted: Mon Feb 21, 2022 3:55 pm
by kev1n
Hi,
You can add a check in the BS (Before Save) PHP event like:
Code: Select all
$q = "SELECT COUNT(*) FROM your_table WHERE iban1 = ? AND iban2 = ? AND iban3 = ?";
$t = nuRunQuery($q, array("#iban1#","iban2#","iban3#"));
$row = db_fetch_row($t);
if ($row[0] != 0) {
nuDisplayError('Duplicates found...');
}
Re: Duplicate check - multiple fields
Posted: Mon Feb 21, 2022 8:48 pm
by chrisfch
It seems not to work.
The goal is to allow the user to input one, two or all three ibans.
The sysetm should then check if any of these ibans is already presen in the table
Re: Duplicate check - multiple fields
Posted: Tue Feb 22, 2022 2:21 am
by kev1n
Can you post your code?
Re: Duplicate check - multiple fields
Posted: Tue Feb 22, 2022 8:24 am
by chrisfch
iban.png
currently i have no working code
Sometimes only one iban is present sometimes 2 or 3.
I just want to check if one of the filled ibans is already existing within the same or one of the two other columns of the table
Re: Duplicate check - multiple fields
Posted: Tue Feb 22, 2022 8:52 am
by kev1n
In this case, compare iban1 with any iban1, iban2, iban3 columns:
Replace
your_table with your table name
Replace iban1 with your IBAN 1 object Id (db column name)
Replace iban2 with your IBAN 2 object Id (db column name)
Replace iban3 with your IBAN 3 object Id (db column name)
Do not rename piban1, piban2, piban3
Code: Select all
$sql = "
SELECT COUNT(*) FROM your_table
WHERE ((iban1 = :piban1 OR iban2 = :piban1 OR iban3 = :piban1) AND :piban1 <> '') OR
((iban1 = :piban2 OR iban2 = :piban2 OR iban3 = :piban2) AND :piban2 <> '') OR
((iban1 = :piban3 OR iban2 = :piban3 OR iban3 = :piban3) AND :piban3 <> '')
";
$params = array(
"piban1" => "#iban1#",
"piban2" => "#iban2#",
"piban3" => "#iban3#"
);
$t = nuRunQuery($sql, $params);
$row = db_fetch_row($t);
if ($row[0] != 0) {
nuDisplayError('Duplicates found...');
}
Re: Duplicate check - multiple fields
Posted: Tue Feb 22, 2022 9:01 am
by chrisfch
what if some ibans are empty - do they need to be null or can they also be ' ' to have the code working
Re: Duplicate check - multiple fields
Posted: Tue Feb 22, 2022 9:06 am
by kev1n
My code should still work as I'm excluding blank values in the sql.
Re: Duplicate check - multiple fields
Posted: Tue Feb 22, 2022 9:14 am
by chrisfch
it seems to work 99%
If i reopen the same entry with the iban already present it can not be saved - even with no changes.
It seems to check the duplicate to its own entry
Re: Duplicate check - multiple fields
Posted: Tue Feb 22, 2022 9:20 am
by kev1n
Exclude the opened record like:
(your_table_id = primary key)
Code: Select all
$sql = "
SELECT COUNT(*) FROM your_table
WHERE ((iban1 = :piban1 OR iban2 = :piban1 OR iban3 = :piban1) AND :piban1 <> '' AND your_table_id <> :record_id) OR
((iban1 = :piban2 OR iban2 = :piban2 OR iban3 = :piban2) AND :piban2 <> '' AND your_table_id <> :record_id) OR
((iban1 = :piban3 OR iban2 = :piban3 OR iban3 = :piban3) AND :piban3 <> '' AND your_table_id <> :record_id)
";
$params = array(
"piban1" => "#iban1#",
"piban2" => "#iban2#",
"piban3" => "#iban3#",
"record_id" => "#RECORD_ID#"
);
$t = nuRunQuery($sql, $params);
$row = db_fetch_row($t);
if ($row[0] != 0) {
nuDisplayError('Duplicates found...');
}