data validation - only unique pairs are saved

data validation - only unique pairs are saved

Unread postby Tinka » Mon Jun 30, 2014 6:36 pm

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
Tinka
 
Posts: 73
Joined: Mon Feb 24, 2014 11:28 pm

Re: data validation - only unique pairs are saved

Unread postby admin » Tue Jul 01, 2014 8:17 am

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
admin
Site Admin
 
Posts: 3406
Joined: Mon Jun 15, 2009 9:53 am
nuBuilder Version: 4.5

Re: data validation - only unique pairs are saved

Unread postby Tinka » Mon Jul 07, 2014 10:13 pm

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
Tinka
 
Posts: 73
Joined: Mon Feb 24, 2014 11:28 pm

Re: data validation - only unique pairs are saved

Unread postby admin » Tue Jul 08, 2014 8:07 am

Tinka,

You did it the way I would have.

Steven
admin
Site Admin
 
Posts: 3406
Joined: Mon Jun 15, 2009 9:53 am
nuBuilder Version: 4.5


Return to Custom Code

Who is online

Users browsing this forum: No registered users and 2 guests