title: Optimal way to insert records in MySQL master-detail table pair
body: <p>I have a unit in my project that performs message exchange between users. I use two MySQL tables for this purpose:</p>
  
  <pre><code>CREATE TABLE `tmessages` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `header` varchar(100) DEFAULT NULL,
    `body` text,
    ...
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>that is master. It contains information on sender, header, body of the message etc. and</p>
  
  <pre><code>CREATE TABLE `tmessage_recipients` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `idmessage` int(10) unsigned NOT NULL DEFAULT '0',
    `idstuff_recipient` int(10) unsigned NOT NULL DEFAULT '0',
    ...,
    PRIMARY KEY (`id`,`idmessage`,`idstuff_recipient`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>as detail with inforation on users whom the message was sent to, date/time of opening the message etc. (the rest of the information is set by triggers) The following code works properly and the messages are sent.</p>
  
  <pre><code>var
    s: string;
    idmessage: longword;
    i: Word;
  ...    
        s := 'INSERT INTO `tmessages` SET ' + '`header`=' + QuotedStr(Edit12.Text) +
          ', ' + '`body`=' + QuotedStr(Memo3.Text);
        Hdm1.FDConnection1.ExecSQL(s);
        idmessage := Hdm1.FDConnection1.ExecSQLScalar('SELECT `id` FROM `tmessages` '
          + 'WHERE `header`=' + QuotedStr(Edit12.Text) + 'AND `body`=' +
          QuotedStr(Memo3.Text) + ' ORDER BY `id` DESC LIMIT 1');
        for i := 0 to ListBox3.Count - 1 do
        begin
          s := 'INSERT INTO `tmessage_recipients` SET ' + '`idmessage`=' +
            inttostr(idmessage) + ', ' + '`idstuff_recipient`=' +
            inttostr(ListBox3.ItemByIndex(i).Tag);
          Hdm1.FDConnection1.ExecSQL(s);
        end;
  </code></pre>
  
  <p>But I suppose that it is not optimal as i have to fetch idmessage first. How can I optimize the procedure and make it more reliable? Can insert be done in one call of <code>Hdm1.FDConnection1.ExecSQL()</code>?</p>
  
  <p><strong>Edit</strong> </p>
  
  <p>Using the comments of @Alex Tartan and @whosrdaddy I came to something like this. Is it the optimal way? Or I need to change something?</p>
  
  <pre><code>  s:= 'INSERT INTO `tmessages` (`header`,`body`) VALUES(:p1, :p2);'#13#10 +
      'SET @last_id := (SELECT LAST_INSERT_ID());';
    for i := 0 to ListBox3.Count - 1 do
      s := s + #13#10'INSERT INTO `tmessage_recipients` (`idmessage`,`idstuff_recipient`)'
        + ' VALUES(@last_id,' + inttostr(ListBox3.ItemByIndex(i).Tag) + ');';
    Hdm1.FDConnection1.ExecSQL(s, [Edit12.Text, Memo3.Text]);
  </code></pre>
  
  <p><strong>Edit 2</strong></p>
  
  <p>With the proposal of @kobik I came to the following:</p>
  
  <pre><code>  s:= 'INSERT INTO `tmessages` (`header`,`body`) VALUES(:p1, :p2);'#13#10 +
      'SET @last_id := (SELECT LAST_INSERT_ID());';
    for i := 0 to ListBox3.Count - 1 do
      s := s + #13#10'INSERT INTO `tmessage_recipients` (`idmessage`,`idstuff_recipient`)'
        + ' VALUES(@last_id,' + inttostr(ListBox3.ItemByIndex(i).Tag) + ');';
  
    HDM1.FDQViaFDTransaction.Transaction.StartTransaction;
    try
      HDM1.FDQViaFDTransaction.ExecSQL(s, [Edit12.Text, Memo3.Text]);
      HDM1.FDQViaFDTransaction.Transaction.Commit;
    except
      HDM1.FDQViaFDTransaction.Transaction.Rollback;
    end;
  </code></pre>
  
  <p><strong>Edit 3</strong></p>
  
  <p>With the valued advice of @Rick-James the code was transformed to the following...</p>
  
  <pre><code>  s := 'INSERT INTO `tmessages` (`header`,`body`) VALUES(:p1, :p2);'#13#10 +
      'SET @last_id := (SELECT LAST_INSERT_ID());';
    s := s + #13#10'INSERT INTO `tmessage_recipients` ' +
      '(`idmessage`,`idstuff_recipient`)'#13#10'VALUES';
    for i := 0 to ListBox3.Count - 1 do
      s := s + #13#10'(@last_id,' + inttostr(ListBox3.ItemByIndex(i).Tag) + '),';
    s[High(s)] := ';';
  
    Hdm1.FDQViaFDTransaction.Transaction.StartTransaction;
    try
      Hdm1.FDQViaFDTransaction.ExecSQL(s, [Edit12.Text, Memo3.Text]);
      Hdm1.FDQViaFDTransaction.Transaction.Commit;
      ShowMessage('Message sent.');
      Button10Click(self);
    except
      Hdm1.FDQViaFDTransaction.Transaction.Rollback;
    end;
  </code></pre>