data validation - only unique pairs are saved

[Tinka]

Hi

I want to perform data validation and only allow unique combinations of two values in a table to be saved. I can achieve this in mysql by both setting a primary key on these two columns (Col1,col2) and add a unique index (col 1, col2). But then Nubuilder just stalls when Save is clicked. I know that NuBuilder can't use the db constraints but has to use its own validation/joins.

I can perform the check by using NuOnSave in the Javascript tab, but I already use this function for something else? Can I combine several checks?
Or do I have to use the php function NuValidate Record on the Before Save tab?

How can I check if any combination already exists as a record in the db? (col1, col2) or (col2, col1)
Any help is appreciated, I have not written any code like this before. I hope it's easy

Tinka

[admin]

Tinka,

This is a part of the PHP code nuBuilder uses itsself (in Before Save) to check that if the Object is an iFrame, one of two lookups must be populated

Code: Select all

if('#sob_all_type#' == 'iframe'){

    if('#sob_iframe_zzzsys_php_id#' == '' and '#sob_iframe_zzzsys_report_id#' == ''){

        $MESS = "Both PHP to Run and Report to Run cannot be left blank..  (TAB : iFrame)"; 
        nuDisplayError($MESS);


    }

}

The hash variables relate to the Objects on the screen and you can add as many error messages you like using nuDisplayError().

In your case you would need to query the database to check if your hash variables were going to create a duplicate.


Steven

[Tinka]

Solved!

Steven, thank you for your general hint, I figured out how to use db_num_rows php function to check if the sql query was empty. Also, I had to modify the sql query to get the right selection, maybe there is an easier statement that works as well.

Before Save:

Code: Select all

$sql= "
SELECT Count(*) AS c
FROM db_table
WHERE col1 = '#field1#' AND col2 ='#field2#' OR
col1= '#field2#' AND col2= '#field1#'
HAVING c >= 1;
";

$qry = nuRunQuery($sql);
$cnt = db_num_rows($qry);


if ($cnt > 0) {
$MESS = "This combination already exists in the database! "; 
        nuDisplayError($MESS);
    }

BR, Tinka

[admin]

Tinka,

You did it the way I would have.

Steven