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
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
data validation - only unique pairs are saved
-
- Site Admin
- Posts: 2781
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 1 time
Re: data validation - only unique pairs are saved
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
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
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);
}
}
In your case you would need to query the database to check if your hash variables were going to create a duplicate.
Steven
-
- Posts: 73
- Joined: Mon Feb 24, 2014 2:58 pm
Re: data validation - only unique pairs are saved
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:
BR, Tinka
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);
}