Update Tables - relational databse

Ignition
1

Hi all,

Here again with a newbie basic question. I am sure I am making this much more complicated than it needs to be.

If I have multiple tables,…to keep it simple i’ll say 2 tables.

Users_Table
id_user INTEGER
username <<<<<the username will be pulled from getUserName()

Other_Data_Table
id_user INTEGER <<<<<<<<<<related to id_user in Users_Table
id_Other_Data INTEGER
other_data

I want to insert a new record. The Other_data in the Other_Data table and the username in the Users_Table.

Likewise I want to SELECT the data to display as username and other data in a table.

I am thinking it will be something like:

INSERT INTO Other_Data_Table(id_user, Other_Data) VALUES (? , :data)
WHERE id_users = username FROM Users_Table id_user

Note that I will pass the user from getUserName() as a parameter but I need to get passed the relational database issue.

Other_Data will be additional tables that I will apply the same lessons to.

Thanks for the help,

Steven

2

Hi Steven,

Read up on SQL joins, and see if that points you in the right direction. :slight_smile:

https://www.w3schools.com/sql/sql_join.asp

3

Whoops, misread your post.

But it’s still a good thing to learn.

4

Anyway, you can’t insert into two tables at the same time. I forget if you had told us what db you were using, but here’s an example on stackoverflow for MySQL it uses LAST_INSERT_ID to grab the id number used on the first insert:

That said, if you can tell us what you’re envisioning, it may help refine the answer.

5

@JordanCClark that is a LOT of information!! However, it appears I will need multiple insert statements.

My vision is to have users making notes in categories. For example, multiple Ignition Designers working on the same project, logging there work.

You would have these tables…

users_table
catagory_table
notes_table

In order to query the notes for everyone to read, the users must be identified in a table. I learned how to get the username into a parameter to pass in a query.

However, the user may or may not exist in the table already. Same with catagory but it will be the same as the user.

So it looks like I need to query the user_table to see if the username exist and get the id (primary key), assign it to a parameter, then use that parameter in the INSERT query into the notes_table.

if the username does not exist, then I need to insert it, then query and assign the username parameter.

SO, what do I actually have to do?

Please keep in mind that this is a learning exercise that will end up as a project that we may or may not use at some point. I need to learn the SQL and scripting and this is a useful way for me as I learn by doing.

Thanks for the help!!

Steven

6

@JordanCClark I forgot to add that I am using PostgreSQL.

Thanks, Steven