Using last_insert_id for multi-table inserts with a new PK

Using last_insert_id for multi-table inserts with a new PK

Postby mpnz » Fri May 12, 2017 7:12 am

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.

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'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.
mpnz
 
Posts: 1
Joined: Fri May 12, 2017 6:22 am

Re: Using last_insert_id for multi-table inserts with a new

Postby admin » Thu Jun 01, 2017 5:03 am

mpnz,

Sorry but, if you have constraints in your db it won't work with nuBuilder.

Steven
admin
Site Admin
 
Posts: 3123
Joined: Mon Jun 15, 2009 9:53 am


Return to Custom Code