Inserting and updating a new record in multiple tables joined by a foreign key?

Wappler General How To
#1

Hi all

A newby database question.

I am moving along with Wappler and now comfortable with working with a single mysql table and now I want to be able to insert a new record in a table and also a foreign key associated table.

For example:

I have a “User” table with a primary key ‘userid’ and fk = ‘accountid’ which is attached to an “Account” table with the primary key being “accountid”. Now I assume I have this around the correct way (Account table will be attached to other tables). An account can have many user but users belong to one account.

User:

userid - pk
first name
last name
some other stuff
accountid - fk

Account:

accountid - pk
account type
other stuff

My question:

So a user signs up and I create a new “User” record and I also want to create a new “Account” record for the user and relate it to a new account record.

It is easy enough to insert a new record in the “User” table, but what it the best way to insert the associated account record.

My thinking is I would need to create an account record first and then create the “User” record and some how associate using the ‘accountid’ - fk

Can anyone shed a process or tutorial where you insert a record into multiple tables that share a relationship like this.

Once I see it in the Wappler process I can work it out from there?

Thanks again!

#2

Probably not too helpful, but a quick reply before I cook dinner… There is a Multi Insert database query in Server Connect that I believe is designed for this purpose.

Failing that you could use a custom Query which is the same as any SQL query you can think of.

If you can’t find a tutorial here, a normal Google search for a SQL Multi Insert query should help.

#3

No, you should not be using multi insert.
Multi insert is for inserting multiple records in a single table.

If you want to insert records in two tables, just add two insert record steps :slight_smile: one per each table.

#4

This discussion might be helpful.

#5

thanks for correcting me @Teodor :slight_smile:

Guess i just got carried away thinking i could help someone… oops!

#6

So I create the record in the account table first and then the user table, do I need to grab the accountid and store that as variable to insert in accountid foreign key In the user table?

Sorry, trying to understand how I maintain that relationship.

#7

Maybe I am overthinking this?

#8

You will see the inserted identity from the first insert step in the data picker of the second insert step :slight_smile:

1 Like
#9

Great, so simple. Wappler surprises me all the time! Good stuff.

2 Likes
#10

Hi

A question using the same topics.

So I have been able to update the connected table which is all good and works great!

I have also been able to create a login process where it creates the account, sends an email with a link containing the email and hash and I have been able to log the user in and redirect them to the account page which is all great.

But for hours I have been stuck on an issue to try and make it work.

Question

When I log the user in after checking the hash (email salted with the password etc) I want to update the accounts table to say the user has confirmed the account. I don’t; know where I am going wrong and have tried so many different things but just can’t get it to work!

See the images

Step 1 add the Action steps:

steps
steps1142×1054 97.4 KB

Step 2 set the update query field to update, at this stage just using the4 first name and it is “varchar” to make it a simple as possible.

columns values
columns values1422×1016 72 KB

Step 3 Set the conditions which it pulls the {{account_id}} value from the query ‘temp_user_login’ (which is the “User” table) in the original topic question which pulls the foreign key for the account_id in that table.

conditions
conditions1032×976 52 KB

I must be doing something fundamentally wrong, I have tried opening a db connection first in the Repeat and try it without and just can not get it to update.

Is there anything bleeding obvious I am doing wrong?

Many thanks!

#11

Hello,
What is not working? Is not inserting any value in the database, or is there an error in the browser console?

#12

HI

No values in the database on the update query.

#13

I see - but where is the {{firstname}} value coming from then?
Have you tried adding a static value there instead - to test?

#14

HI Yep. I have added a a letter and number and you name it to see if it will update. There is no reason on the db end why it won’t update. I can insert text into it when I create the record no problem.

#15

What should this repeat step be repeating? As the expression it repeats is different than the query, are you trying to rpeat the query?

Screenshot_17
Screenshot_171044×427 80.1 KB

Your query is called temp_user_login while the repeat repeats {{temp_login}}

#16

Yeah that was just taken when I was trying to rewrite the query

This is it now.

update
update1268×366 46.5 KB

#17

Please create 2 set value steps under the login.
Add some names to them (doesn’t matter what) and select the {{account_id}} and {{firstname}} as values for them.
Enable the output options for the Repeat and the two set value steps.

Open the browser dev tools > network > XHR and run your server action. Check if in the reply you see right values in the set value steps.

#18

Thanks I will give this a try and let you know how I go.

#19

HI @Teodor I completed the steps you described and from what I can see (Firefox) dev tools XHR I can only see the name of the repeating group under the json tab like this “user_reset ” which suggests that the it is not storing the values?

#20

Steven, just out of interest. This is not a production website, right? If so you need to go for a more secure crypto to store the user password.