0

I am writing an audit platform and I can see myself falling into the same trap I usually do.

The Answers table:

SitePeriodID
QuestionID
Answer
CommentID

The Comments table:

SitePeriodID
QuestionID
CommentID
Comment

All questions have answers, but not all questions have comments. Specifically, a user only needs to enter a comment if they select a certain answer. I want to keep answers and comments separate, as I may in the future implement the ability to retrieve all the comments for an area separately from the answers. If it was all on one table I'd have to look through many thousands of rows for the questions that have comments that also match the Site and Period.

So, what I would typically do is something like this, at the point of storing data. At this point, the SitePeriodID would be known: 

Insert into comments (CommentID, Comment) Values (NULL, Comment)
commentid = Get from comments where SitePeriodID = X and QuestionID = Y
Insert into answers (SitePeriodID, QuestionID, Answer, CommentID) Values (SitePeriodID, QuestionID, Answer, commentid['CommentID'])

What I've basically done is stored something in a database only to call it again immediately.

I'm fairly sure what I've got here is an XY problem where I'm overthinking my database set up, but I wanted to know if there was a way to avoid the above problem.

SGR
  • 403
  • 5
  • 16
  • 1
    By "call it again immediately" do you just mean that all you need is the dynamic identifier which was generated by the insert statement? `LAST_INSERT_ID()` could be what you're looking for. This may help: https://stackoverflow.com/questions/3837990/last-insert-id-mysql, as well as many other questions and examples about that. – David Jun 26 '17 at 13:17

1 Answers1

0

There is a MySQL function which is call LAST_INSERT_ID() that recovers the ID of the last item that has been inserted in the base. You can do

SELECT LAST_INSERT_ID() FROM mytable;

and you'll get a simple integer.

FYI you should be in a TRANSACTION to use it, so you can be sure about the fact it is YOUR last_insert_id() you get and not the one that has been inserted by another user.

V. Courtois
  • 315
  • 7
  • 20
  • `last_insert_id()` in MySQL is actually per client, so unless you disconnect from the server and connect again (which you shouldn't do in the same request anyway), you will always get _your_ last id. – M. Eriksson Jun 26 '17 at 13:23
  • Sorry but your answer does not match with a previous problem I encountered while working last week. If you're not in transaction (auto-commit mode disabled) and if someone connects and apply his changes without being in transaction himself, your selection of the `last_insert_id` can occur _after_ the other guy inserted, and so you get the wrong ID. The problem is the influence **you** have on the other one's queries. – V. Courtois Jun 26 '17 at 13:28
  • 1
    _"The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client."_ – M. Eriksson Jun 26 '17 at 13:31
  • 1
    From: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id <= If that's wrong, you should submit a ticket over at MySQL. – M. Eriksson Jun 26 '17 at 13:32
  • I hear you (I trust what you said first, you don't have to tell me the original quote). But I'm saying that does not match our db's behavior for some unknown reason. EDIT : I know now : our applications connect every time with the same login ; that might be the reason. – V. Courtois Jun 26 '17 at 13:33