Getting related fields inside afterInsert trigger

Archived from the Xataface Users forum.

gthorne — Thu Sep 13, 2012 3:07 pm

Hey Steve, me again. How’s the weather?

I’m writing an email notification routine in the afterInsert trigger. Basically, I need to send everything in the record just inserted to an email address in a related table. So, I’m doing this:

Code: Select all

`` function afterInsert (&$record) {
        $app =& Dataface_Application::getInstance();

        $result = mysql_query (“SELECT event_start_time , event_end_time , short_desc ,”.
               ”contact.name AS name, contact.email_addr AS email_addr, “.
                                        “city , states.postal_abbr AS state, submitted_by , submitted_date “.
                                        “FROM events “.
                                        “LEFT JOIN contact ON ( events.contact_id = contact.contact_id ) “.
                                        “LEFT JOIN states ON ( events.state_id = states.state_id ) “.
               ”WHERE id = “ $record->getValueAsString(‘id’)
                                        , $app->db());
         while ($row = mysql_fetch_assoc($result))
         {
                         // format email
         }
         // send email
} ``

Now, I’m reading your code, and I’m wondering if this isn’t overkill:

Code: Select all

/**          * <p>Returns a the value of a field in a meaningful state so that it can be displayed.          * This method is similar to getValueAsString() except that this goes a step further and resolves          * references. For example, some fields may store an integer that represents the id for a related          * record in another table.  If a vocabulary is assigned to that field that defines the meanings for          * the integers, then this method will return the resolved vocabulary rather than the integer itself.</p>          * <p>Eg:</p>          * <code>          * <pre>          * // Column definitions:          * // Table Unit_plans (id INT(11), name VARCHR(255) )          * // Table Lessons ( unit_id INT(11) )          * // Lessons.unit_id.vocabulary = "select id,name from Unit_plans"          * $record = new Dataface_Record('Lessons', array('unit_id'=>3));          * $record->getValueAsString('unit_id'); // returns 3          * $record->display('unit_id'); // returns "Good Unit Plan"          */

EDIT: OK, I just verifed that this works – for the most part. However, how do I get the email address for the contact? It’s in the ‘contact’ table, but ‘$record->display (‘contact_id’)’ returns the name, as expected. I wish to use both the name and the email address.

---

shannah — Fri Sep 14, 2012 9:35 am

Your first instinct is reasonable. If you need to load from another table then you will either need to load a record via the Xataface API (e.g. df_get_record() or df_get_records_array()) or you’ll need to load them via an SQL query, which you have done in your example.