0

If two individual records are added to a database such as two patients being added to the table 'Paitents' and when they are added the Primary_Key such as Paitient_ID is created automatically and given to each new account. .(Auto Incremented) That bit is quite straight forward and understand I can just use an 'INSERT INTO SONGS' statement. But what if the two patients are related and I have another table called "Relations" Where by I need it to pull in the two Paitent_ID's and create a relation from the same insert query. Can this be done?

Improve this question
2
  • Yes. You would need to insert into that table in the same way, as your database cannot automatically know when people are related Commented Sep 18, 2015 at 12:50
  • How are you adding the patients? One-by-one in a form, POSTing the form for each patient? Or do you have a form on which more than one patient can be entered, and then the details of both posted together in one POST? Commented Sep 18, 2015 at 12:51

1 Answer 1

Reset to default
1

ID generated by MySQL automatically in auto_increment column can be obtained using the LAST_INSERT_ID() function. Languages/libraries often offer a function for this (e.g. in PHP PDO you call PDO::lastInsertId() instead of making another query).

How to solve this exactly depends on how you are inserting the values into a database, but the basics can be:

  1. Insert patient one
  2. Get the ID
  3. Insert patient two
  4. Get the ID
  5. Create the relation
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you that makes sense. At the stage I am just working purely in Mysql and not using GUI for the frontend. Just trying to get my head around what I can or can not do . Is it possible to use the LAST_INSERT_ID() in Mysql ? How would this query look?
Sure thing! You can simply use SELECT LAST_INSERT_ID(). If you're working in MySQL only, I guess it would be best to use variables, like so: after first Patient insert, you SELECT LAST_INSERT_ID() INTO @patientOne, after second insert, you SELECT LAST_INSERT_ID() INTO @patientTwo, and then just INSERT INTO Relation VALUES @patientOne, @patientTwo.