Page 1 of 1

Using last_insert_id for multi-table inserts with a new PK

PostPosted: Fri May 12, 2017 7:12 am
by mpnz

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.

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

PostPosted: Thu Jun 01, 2017 5:03 am
by admin

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