3 Replies Latest reply on Aug 19, 2008 5:24 AM by (Rolf_Ernst)

    How to retrieve rows in custom trigger

      Hi,

      I want to retrieve rows from an SQL query in a custom trigger. I found out that I cannot use the native php mysql interface but the tng engine. This works:

      $queryEmail = "SELECT dpccVolunteers.HomeEmail, dpccVolunteerToFunction.*, dpccFunctions.Name, dpccVolunteers.FirstName, dpccVolunteers.LastName FROM ((dpccVolunteers LEFT JOIN dpccVolunteerToFunction ON dpccVolunteerToFunction.VolunteerID=dpccVolunteers.ID) LEFT JOIN dpccFunctions ON dpccFunctions.ID=dpccVolunteerToFunction.FunctionID) WHERE dpccFunctions.Name='Volunteer Notification' ";
      $result = $tNG->connection->execute($queryEmail);
      $numberOfRecords = $result->recordCount();
      echo $numberOfRecords;

      Now how do I retrieve the individual rows and fields?
        • 1. Re: How to retrieve rows in custom trigger
          Level 1
          Ok,

          I figured it out using the KT_Recordset class. Very obscure the lack of documentation.
          • 2. Re: How to retrieve rows in custom trigger
            Level 1
            Hi Rolf

            Please could you post an example of how you did it, as this would be very helpfull for me and i suspect many others.

            Thankyou
            • 3. Re: How to retrieve rows in custom trigger
              Level 1
              Sure,

              here is how it works (I cut out other portions of the code that did other non-related logic:

              $queryEmail = "SELECT dpccVolunteers.HomeEmail, dpccVolunteerToFunction.*, dpccFunctions.Name, dpccVolunteers.FirstName, dpccVolunteers.LastName FROM ((dpccVolunteers LEFT JOIN dpccVolunteerToFunction ON dpccVolunteerToFunction.VolunteerID=dpccVolunteers.ID) LEFT JOIN dpccFunctions ON dpccFunctions.ID=dpccVolunteerToFunction.FunctionID) WHERE dpccFunctions.Name='Contact Information' ";
              $result = $tNG->connection->execute($queryEmail);
              $numberOfRecords = $result->recordCount();
              .....
              while (!$result->EOF)
              {
              $mail->AddAddress($result->Fields('HomeEmail'), $result->Fields('FirstName')." ".$result->Fields('LastName'));
              $mail->Send();
              $result->MoveNext();
              }
              }

              -----
              So the important functions are $result->EOF which indicates you perused all records. To retrieve a column from the current row use $result->Fields('>>>fieldname<<<')

              To get to the next row, use $result->MoveNext(). There is also a MoveFirst function, just in case you need to read all rows again.

              You can call Close() at the end but reading the code it does nothing so you might as well omit it.

              Hope that helps.