Using last_insert_id for multi-table inserts with a new PK
Posted: Thu May 11, 2017 11:42 pm
Hi
New to nuBuilder but moderately competent with MySQL. I've got a schema that's pretty close to 3NF, so lots of FK relationships. Present challenge is trying to create a brand new volunteer record and also their contact records from a single form/subform, which uses separate tables but relies on the volunteer.v_ID PK.
I've got sub-forms set up appropriately, but obviously I cannot insert into volunteerContact without first knowing volunteers.v_ID. I've found the last_insert_id function but I'm lost as to how to make use of it with nuBuilder. PHP has an accompanying function mysql_insert_id, but still not sure where I would apply that. Do I need to code up a replacement to the Save button? Or is there a way of setting up a transaction to modify the Save button's behaviour?
I know I could avoid this by having a separate form to add contact details once the volunteer is set up, but it's more intuitive to create the volunteer and their contact details (there's also a volunteerAddress table, for example) out of a single "new record" action.
New to nuBuilder but moderately competent with MySQL. I've got a schema that's pretty close to 3NF, so lots of FK relationships. Present challenge is trying to create a brand new volunteer record and also their contact records from a single form/subform, which uses separate tables but relies on the volunteer.v_ID PK.
Code: Select all
describe volunteers;
+--------------------------------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------------+------------------------------+------+-----+---------+----------------+
| v_ID | mediumint(10) unsigned | NO | PRI | NULL | auto_increment |
...
Code: Select all
describe volunteerContact;
+-------------------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------------+------+-----+---------+-------+
| volunteers_v_ID | mediumint(10) unsigned | NO | PRI | NULL | |
| vC_ContactDetail | varchar(100) | NO | PRI | NULL | |
| contactType_cT_ID | mediumint(10) unsigned | NO | MUL | NULL | |
+-------------------+------------------------+------+-----+---------+-------+
I know I could avoid this by having a separate form to add contact details once the volunteer is set up, but it's more intuitive to create the volunteer and their contact details (there's also a volunteerAddress table, for example) out of a single "new record" action.