Can someone help me figure out how to insert a record in another table?
My scenario … upon successful creation of a record in a table called group, I want to add a new record to my_groups to add the newly created group to my list of groups. group has fields name, description and an id (where the id is the key that is generated by auto increment in the DB).
I want to create a new record in my_groups ( based on who is logged in) when a new group record is added. I built a form application for adding a single new group record, and I am trying to add code to the onAfterInsert event to then add a new record to my_groups. I see no way to auto-build this code. I go to onAfterInsert event and select ‘insert record in another table’, but there are many blanks with no text on how to get at the data I want.
I get some code like the following …… I see how to plug in the table name. I do not know how to reference the login (which is the id of the person logged in and to whom I want to add a record in my_groups. The global variable for login is usr_login (I think, but I do not know where to go to validate that assumption based on starting with building a control/login app)
/**
- Insert a record on another table
*/
// SQL statement parameters
$insert_table = ‘my_groups’; // Table name – create new record in mygroups so that login person will have the newly added group listed in my_goups
$insert_fields = array( // Field list for my-groups (use global variable for logged in person for ‘login’ and use if go newly added group for ‘group’ reference on my_groups)
‘login’ => XXX,
‘group’ => YYY,
);
// Insert record
$insert_sql = ‘INSERT INTO ’ . $insert_table
. ’ (’ . implode(’, ‘, array_keys($insert_fields)) . ‘)’
. ’ VALUES (’ . implode(’, ', array_values($insert_fields)) . ‘)’;
sc_exec_sql($insert_sql);
Questions
– What is the syntax to use for the login value? That is, what do I put as stand in for XXX below to attribute the record key to the person logged in?What is XXX supposed to be if the global variable is usr_login
– How do I get the value for the id of the group that was just created. Recall I am on the onAfterUpdate for table group where id has been auto generated by the db for the insert in to group. How do I reference the group table id field (group.id) in order to use that where I have YYY … what is syntax for YYY. Do I have to do a select first to read back the record just created in order to get the id that was auto generated by the database? IF so then what does the SELECT and then INSERT INTO sequence look like in order to pass the id form the SELECT group to the INSERT for my_groups?
Thanks