Insert into multiple tables

Hi, I might have a situation where I need to insert into multiple tables on submission of a form. Is data assist able to insert into multiple tables?

If not, am I able to create two inserts from data assist on the same page and then edit them together in some way?

Thanks.

Mat

you would need to add an insert record behavior for each table you wish to add the information to.

just make sure that only the last insert record behavior has the redirect set.

Cor, that was a quick reply!

So, in that respect, i could enter into loads of tables at once then! The more I use this stuff the more it makes sense.

It would also allow me to deal with DB relationships very easily wouldn't it?

Thanks.

yes, you could have many insert record behaviors on one page.

In each one, there is a section called Session Variable, this stores the ID of the inserted record into a session named by the Store as setting. See the attached screen shot.

Use this session to create the relationships.

in most cases, the other tables will relate to the first table using a one to many relationship, so use the session created by the first insert record behavior in the other tables.

OK, thanks Jason.

I don't fully understand what you have just said about the session variable, but I'll have a look through the gsg to see if I can figure out the help there before bothering you.

That means I shall probably be back here later tonight =D

when the record is inserted, the primarey key id value of the record is stored in a session variable. This allows you to create your relations ships when the records are stored in the other tables by using this session variable in the foreign key columns.

Right, having looked at this, do you mean that the auto incremented ID is created, then passed into the session variable, then the session variable inserts it into the relationship table (foreign key table)?

So, on that basis, I assume that any code creating the ID's needs to be placed before the insert behaviours code so the session variable insert has something to collect and therefore insert.

For example, I would have a product sector table ID and a manufacturer table ID, and it is those two ID's I insert into the relationship table.

Do I understand this correctly?

I don't need to set up foreign keys within the MySQL database do I? I can use SQL queries to create the relationships can't I?

no, you re not quite getting it.


when the record is inserted, a session is created that contains the primary key value of the record. This is done inside of the insert record behavior. You wont need to move any code around. You dont need to be concerned about the order of the code that is creating the session variable except to make sure that the record is inserted to the parent table before inserting to the child table.


on the insert that goes into the child table, use the session variable form the parent table insert in the foreign key column.



no, you do not need to set up foreign key column in the database.