8 Replies Latest reply on Aug 6, 2008 7:15 AM by Günter Schenk

    Insert validation BEFORE not working

    Level 1
      I have an insert/update form where I've added a Build Condition to the Validation Form server behavior. For reason its not working.

      What I need to happen is that when I click the submit button, if there are 2 or less recordsets with the same time (a field in the test table), then an error should appear and the form should not submit.

      On this form, I have this recordset:
      SELECT COUNT(*) as cnt
      FROM test

      So, in the Validation Form server behavior Build Condition I have {Recordset1.cnt} >= 2

      Regardless what I do, the form submits. Am I missing something obvious?
      I've tried changing the >= to <= and I've added a WHERE test.time = time variable but nothing has worked so far with the Build Condition.

      Please help!
        • 1. Re: Insert validation BEFORE not working
          Günter Schenk Level 4
          Hi Peter,

          ------
          SELECT COUNT(*) as cnt
          ------

          I *think* you could as well use the $totalRows_queryname variable

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: Insert validation BEFORE not working
            Level 1
            Thanks Gunter.

            My question is where would I use this ($totalRows_queryname variable)?

            I've added this to my recordset code but its not being recognized. I hope I'm missing something obvious. Below is my recordest.

            $numtest_Recordset1 = "-1";
            if (isset($_POST['num_test'])) {
            $numtest_Recordset1 = $_POST['num_test'];
            }
            mysql_select_db($database_sailing, $sailing);
            $query_Recordset1 = sprintf("SELECT COUNT(*) as cnt FROM test WHERE test.num_test = %s", GetSQLValueString($numtest_Recordset1, "int"));
            $Recordset1 = mysql_query($query_Recordset1, $sailing) or die(mysql_error());
            $row_Recordset1 = mysql_fetch_assoc($Recordset1);
            $totalRows_Recordset1 = mysql_num_rows($Recordset1);
            if($totalRows_Recordset1 == 4) {
            $error = new tNG_error("Timeslot is filled!",array(),array());
            return $error;
            }
            • 3. Re: Insert validation BEFORE not working
              Günter Schenk Level 4
              Peter,

              is this an additional recordset which is supposed to "capture" the result of ADDT´s multiple insert/update transaction recordset ? If so, I fear you won´t get any results, because your current variable $_POST['num_test'] would have to capture an array of values (from the transaction recordset) rather than one single value -- and I fear that this additional recordset lacks some sort of "connection" to the transaction recordset, which is the one you would have to examine.

              I don´t have any other answer right now, but it could be helpful to see the whole page code posted as .txt file on your server.

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: Insert validation BEFORE not working
                Level 1
                The array definitely sounds like the way I should go. How, I still need some guidance. As far as the recordset that captures the $_POST and compares it to the recordset above, all the data is in the same db table. <br /> <br />Here's the code from my page. Thanks for your help so far! I think we're close! <br /><?php require_once('../Connections/sailing.php'); ?> <br /><?php<br />// Load the common classes<br />require_once('../includes/common/KT_common.php');<br /><br />// Require the MXI classes<br />require_once ('../includes/mxi/MXI.php');<br /><br />// Load the tNG classes<br />require_once('../includes/tng/tNG.inc.php');<br /><br />// Load the KT_back class<br />require_once('../includes/nxt/KT_back.php');<br /><br />// Make a transaction dispatcher instance<br />$tNGs = new tNG_dispatcher("../");<br /><br />// Make unified connection variable<br />$conn_sailing = new KT_connection($sailing, $database_sailing);<br /><br />// Start trigger<br />$formValidation = new tNG_FormValidation();<br />$tNGs->prepareValidation($formValidation);<br />// End trigger<br /><br />if (!function_exists("GetSQLValueString")) {<br />function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") <br />{<br />  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;<br /><br />  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);<br /><br />  switch ($theType) {<br />    case "text":<br />      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";<br />      break;    <br />    case "long":<br />    case "int":<br />      $theValue = ($theValue != "") ? intval($theValue) : "NULL";<br />      break;<br />    case "double":<br />      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";<br />      break;<br />    case "date":<br />      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";<br />      break;<br />    case "defined":<br />      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;<br />      break;<br />  }<br />  return $theValue;<br />}<br />}<br /><br />$numtest_Recordset1 = "-1";<br />if (isset($_POST['num_test'])) {<br />  $numtest_Recordset1 = $_POST['num_test'];<br />}<br />mysql_select_db($database_sailing, $sailing);<br />$query_Recordset1 = sprintf("SELECT COUNT(*) as cnt FROM test  WHERE test.num_test = %s", GetSQLValueString($numtest_Recordset1, "int"));<br />$Recordset1 = mysql_query($query_Recordset1, $sailing) or die(mysql_error());<br />$row_Recordset1 = mysql_fetch_assoc($Recordset1);<br />$totalRows_Recordset1 = mysql_num_rows($Recordset1);<br />if($totalRows_Recordset1 == 4) {<br />$error = new tNG_error("Timeslot is filled!",array(),array());<br />return $error;<br />}<br /><br />// Make an insert transaction instance<br />$ins_test = new tNG_multipleInsert($conn_sailing);<br />$tNGs->addTransaction($ins_test);<br />// Register triggers<br />$ins_test->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Insert1");<br />$ins_test->registerConditionalTrigger("{Recordset1.cnt} >= 2\n", "BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);<br />$ins_test->registerTrigger("END", "Trigger_Default_Redirect", 99, "_test1.php");<br />// Add columns<br />$ins_test->setTable("test");<br />$ins_test->addColumn("name_test", "STRING_TYPE", "POST", "name_test");<br />$ins_test->addColumn("num_test", "NUMERIC_TYPE", "POST", "num_test");<br />$ins_test->setPrimaryKey("id_test", "NUMERIC_TYPE");<br /><br />// Make an update transaction instance<br />$upd_test = new tNG_multipleUpdate($conn_sailing);<br />$tNGs->addTransaction($upd_test);<br />// Register triggers<br />$upd_test->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Update1");<br />$upd_test->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);<br />$upd_test->registerTrigger("END", "Trigger_Default_Redirect", 99, "../includes/nxt/back.php");<br />// Add columns<br />$upd_test->setTable("test");<br />$upd_test->addColumn("name_test", "STRING_TYPE", "POST", "name_test");<br />$upd_test->addColumn("num_test", "NUMERIC_TYPE", "POST", "num_test");<br />$upd_test->setPrimaryKey("id_test", "NUMERIC_TYPE", "GET", "id_test");<br /><br />// Make an instance of the transaction object<br />$del_test = new tNG_multipleDelete($conn_sailing);<br />$tNGs->addTransaction($del_test);<br />// Register triggers<br />$del_test->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Delete1");<br />$del_test->registerTrigger("END", "Trigger_Default_Redirect", 99, "../includes/nxt/back.php");<br />// Add columns<br />$del_test->setTable("test");<br />$del_test->setPrimaryKey("id_test", "NUMERIC_TYPE", "GET", "id_test");<br /><br />// Execute all the registered transactions<br />$tNGs->executeTransactions();<br /><br />// Get the transaction recordset<br />$rstest = $tNGs->getRecordset("test");<br />$row_rstest = mysql_fetch_assoc($rstest);<br />$totalRows_rstest = mysql_num_rows($rstest);<br />?> <br /> <!-- InstanceBegin template="/Templates/start3col.dwt" codeOutsideHTMLIsLocked="false" --> <br /> <br /> <br /> <!-- InstanceBeginEditable name="doctitle" --> <br /> <br /> <!-- InstanceEndEditable --> <br /> <!-- IE6 dropdown menu fix --> <br /> <script src="/js/nav_main.js" type="text/javascript"></script> <br /> <br /> <!-- InstanceBeginEditable name="head" --> <br /> <!-- InstanceEditableHeadTag --> <br /> <br /> <script src="../includes/common/js/base.js" type="text/javascript"></script> <br /> <script src="../includes/common/js/utility.js" type="text/javascript"></script> <br /> <script src="../includes/skins/style.js" type="text/javascript"></script> <br /> <!-- InstanceEndEditable --> <br /> <br /> <br /> <br /> <br /> <div id="page_wrapper"> <br /> <div id="header_bg"> <br /> <div id="header"> <br /> <div id="logo"> <img src="/images/spacer.gif" alt="Umass Boston" width="68" height="79" border="0" /> </div> <br /> <div id="nav-site"> <br /> <?php include "/home/httpd/html/php/googlesearch_home.php" ?> <br /></div> <br /> <div id="global_nav"> <br /> <?php include "/home/httpd/html/inc/global.html" ?> <br /></div> <!--end global_nav--> <br /></div> <br /> <!--end header_bg--> <br /></div> <br /> <br /> <br /> <div id="main_bg3col"> <br /> <div id="audience_nav"> <br /> <div id="second_nav"> <br /> <?php include "../inc/_sitenav_sailing.html" ?> <br /></div> <br /> <!--end second_nav--> <br /></div> <!--end audience_nav--> <br /> <div id="titlecontainer"> <br /> <div id="title_bar"> <!-- InstanceBeginEditable name="titebar" --> <?php echo $tNGs->displayValidationRules();?> <br /> <script src="../includes/nxt/scripts/form.js" type="text/javascript"></script> <br /> <script src="../includes/nxt/scripts/form.js.php" type="text/javascript"></script> <br /> <script type="text/javascript"></script>$NXT_FORM_SETTINGS = { <br /> duplicate_buttons: false, <br /> show_as_grid: true, <br /> merge_down_value: true <br />} <br /> <br /> <a href="../index.html">Marine Operations</a> <!-- InstanceEndEditable --> </div> <br /></div> <br /> <br /> <div id="main"> <br /> <div id="left_col"> <br /> <div id="navcontainer"> <!-- InstanceBeginEditable name="menu" --> <br /> <?php<br />  mxi_includes_start("_menu.html");<br />  require(basename("_menu.html"));<br />  mxi_includes_end();<br />?> <!-- InstanceEndEditable --> <br /></div> <br /> <!-- end nav_container --> <br /></div> <!-- end left_col --> <br /> <br /> <div id="right_col"> <!-- InstanceBeginEditable name="menuRight" --> <br /> <?php<br />  mxi_includes_start("_menu_right.php");<br />  require(basename("_menu_right.php"));<br />  mxi_includes_end();<br />?> <!-- InstanceEndEditable --> <br /></div> <br /> <!-- end right_col --> <br /> <br /> <div id="main_col"> <!-- InstanceBeginEditable name="title" --> <br /> <div id="titlesection">:: Sailing Program</div> <br /> <h2 class="subheadBlue">Test</h2> <br /> <!-- InstanceEndEditable --> <br /> <br /> <!-- InstanceBeginEditable name="content" --> <br /> <br /> <p class="txt_bluebold">Header</p> <br /> <p>&#160; <br /> <?php<br /> echo $tNGs->getErrorMsg();<br />?> <br /> <br /></p> <div class="KT_tng"> <br /> <h1> <br /> </h1></div></div></div></div></div>
                • 5. Re: Insert validation BEFORE not working
                  Level 1
                  I forgot to mention, in this form, I'm capturing the name_test and num_test text fields. If the same name and number are entered 4 times, then an error should show up, forcing the person to change one or both items and re-submit the form.

                  Thanks again for your help!
                  So close!
                  • 6. Re: Insert validation BEFORE not working
                    Günter Schenk Level 4
                    -----
                    As far as the recordset that captures the $_POST and compares it to the recordset above, all the data is in the same db table.
                    -----

                    Wouldn´t ADDT´s "check unique" trigger be of help in your "if there are 2 or less recordsets with the same time" case ? After all, this trigger is made to compare (definable) transaction values against (definable) table columns.

                    Cheers,
                    Günter Schenk
                    Adobe Community Expert, Dreamweaver
                    • 7. Re: Insert validation BEFORE not working
                      Level 1
                      Doesn't "check unique key" only work to verify if 1 record with the same data exists?

                      I just tried using checked unique key and put in the two fields and then in the advanced tab put buiilt this condition {Recordset1.cnt} == 2

                      My thinking was that this Build Condition would kick in and when there were two of the same records in the table, when I attempted to insert a third, an error message would show. But it didn't and let a third record in instead of showing an error message.

                      I appreciate your help and know I'm getting closer to making this work!

                      So close!
                      • 8. Re: Insert validation BEFORE not working
                        Günter Schenk Level 4
                        ------
                        Doesn't "check unique key" only work to verify if 1 record with the same data exists?
                        ------

                        can be one or more matches, doesn´t matter

                        -----
                        I just tried using checked unique key and put in the two fields and then in the advanced tab put buiilt this condition {Recordset1.cnt} == 2
                        -----

                        what about $totalRows_Recordset1 == 2 ?

                        However, your Recordset1 most probably doesn´t pick up the insert/update transaction values, as it maybe doesn´t even grab the $_POST['num_test'] values you think it should, if any -- please remember, the transaction recordset generates an array of values with a variable amount of entries, and $_POST['num_test'] would just be able to grab one value. That said, the whole Recordset1 could be wrong as such.

                        Cheers,
                        Günter Schenk
                        Adobe Community Expert, Dreamweaver