Duplicate check - multiple fields Topic is solved

[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?

[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...');
}

[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

[kev1n]

Can you post your code?

[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

[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...');
}

[chrisfch]

what if some ibans are empty - do they need to be null or can they also be ' ' to have the code working

[kev1n]

My code should still work as I'm excluding blank values in the sql.

[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

[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...');
}