Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Using last_insert_id for multi-table inserts with a new PK

Post Reply
mpnz
Posts: 1
Joined: Thu May 11, 2017 10:52 pm

Using last_insert_id for multi-table inserts with a new PK

Unread post by mpnz »

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.
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

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

Unread post by admin »

mpnz,

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

Steven
Post Reply