How to link different tables and write to them in mysql

Question

0.00/5 (No votes)

See more:

I have a database with different tables, and I am currently writing to all of these tables. I am currently managing the ID of each table with a counter, but the thing is that I was told that each Id should auto increment and all the tables should be linked together with the id of the previous table.

What I have tried:

My table structure is this:
Activities: idactivity, idResult, Activity
Results: idResult, idObjective, Results
Objectives: idObjective, idProject
Projects: idProject, project

Iam able to write to all the tables, but I don't know how to link all the tables with the id from the previous table.

Posted 24-Jan-18 10:28am

Member 10850253

Updated 26-Jan-18 4:11am

Add a Solution

Comments

Dileep CV 25-Jan-18 17:24pm

If i am not wrong, your tables might have Primary key and Foreign key relationship with common field from previous table. Also you can do this using a stored procedure.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rossall82 · Answer 1 · 2018-01-26T03:33:00

Personally, I would use the built in

LAST_INSERT_ID()

function. [^] This will return the most recently inserted Id within the scope of the connection.

In doing so, you will need to get your hierarchy of table dependencies correct. From the look of it, the sequence of inserts should go like this:

SQL

Insert into Projects (your values)
SET @last_id_from_insert = LAST_INSERT_ID(); --which will return the Id for the new row you have inserted into the Projects table

Insert into Objectives (idProject)
Values (@last_id_from_insert)

SET @last_id_from_insert = LAST_INSERT_ID(); --update our variable to now be the Id of the row we have just inserted into the Objectives table

Insert into Results(idObjective)
Values (@last_id_from_insert)

SET @last_id_from_insert = LAST_INSERT_ID(); --update our variable to now be the Id of the row we have just insObjectives to the Results table

Insert into Activities(idResult)
Values (@last_id_from_insert)

As Dileep says, it would be good practice to do this within a stored procedure so that the connection scope is the same for each insert and retrieval of the latest Id.

Hope that helps.