Multiple query with ID from first one

Question

I'm working on a project but I'm stuck. I'm making a system to change the answer and question of aq quesion from the FAQ-page on a website. For that I use 2 tables in SQL. One who contains the answers and one that contains the question which contains a column for the number of which ID is connected for the answer. Bit I can't run those 2 SQL's in once, can someone help me fix the problem. I need to update my answers-table first to get the ID from the new answer to connect it to the questions-table.

Thanks in advance.

Table Answers

Table Questions

Code for the updates

<?php
include('../inc/connection.php');

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

if (isset($_POST['fields'])) {
    foreach ($_POST['fields'] as $item) {
        $values[] = $db->real_escape_string($item);
    }

    var_dump($values);

    if ($stmt = $db->prepare("INSERT INTO AntwoordenFAQ (nl_antwoord,fr_antwoord,en_antwoord) VALUES (?,?,?);")) {

        $stmt->bind_param('sss', $values[0], $values[1], $values[2]);
        if ($stmt->execute()) {
            if ($stmt->affected_rows == 1) {
                $IDinsert = $db->insert_id;
                if ($stmt = $db->prepare("INSERT INTO VragenFAQ (nl_vraag, fr_vraag, en_vraag) VALUES (?,?,?);")) {
                    $stmt->bind_param('sss', $values[3], $values[4], $values[5]);
                    if ($stmt->execute()) {
                        if ($stmt->affected_rows == 1) {
                            echo TRUE;
                        }
                        else {
                            echo $IDinsert;
                        }
                    }
                    $stmt->close();
                }
            }
            else {
                echo FALSE;
            }
        }
        $stmt->close();
    }
    $db->close();
}
}
exit();
?>

what is the problem ? what `But I can't run those 2 SQL's in once` mean? Show us sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Feb 17 '17 at 14:36
`help me fix the problem` read more here http://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time#175138 — JustOnUnderMillions, Feb 17 '17 at 14:38
And read this for `LAST_INSERT_ID()` http://stackoverflow.com/questions/3837990/last-insert-id-mysql#3838011 — JustOnUnderMillions, Feb 17 '17 at 14:39
You can do two statements logically at once, it is called a transaction. This is how banks move money from one account to another. — Hogan, Feb 17 '17 at 15:09

score 1 · Accepted Answer · answered Feb 17 '17 at 15:13

Saying you can't do two statements at once seems obvious but is not actually true. SQL does give a way to do two statements logically at once. Them means any other queries against the data base will see nothing or the results of both queries. You do this with a transaction. It works like this :

BEGIN TRANSACTION;

-- On systems you might need to lock tables here on others it 
-- happens automatically

-- QUERY 1
INSERT -- ...
;

-- QUERY 2
INSERT -- ...
; 

COMMIT TRANSACTION;

This is how (as an example) a bank might move cash from one account to another -- there is never a moment where two accounts have the cash or no accounts have the cash. In the bank example the two (or more) queries would be UPDATE queries.

Multiple query with ID from first one

1 Answers1