MySQL Last_Insert_ID Syntax

Question

I have this code:

//insert user input into db
$query = "INSERT INTO test_details (test_title, user_id, likes)
VALUES ('$title', '$user_id', '0')";
$query .= "INSERT INTO test_descriptions (test_id, description)
VALUES (LAST_INSERT_ID(), '$description')";
if(isset($grade) && isset($difficulty) && isset($subject)) {
    $query .= "INSERT INTO test_filters (test_id, grade, subject, difficulty)
    VALUES (LAST_INSERT_ID(), '$grade', '$subject', '$difficulty')";
}
if(mysqli_multi_query($con, $query)) {
    echo 'Go <a href="../create">back</a> to start creating questions.';
}
else {
    echo "An error occurred! Try again later.";
    echo mysqli_error($con);
}

When I try executing the code I recieve this MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @id = (SELECT LAST_INSERT_ID())INSERT INTO test_descriptions (test_id, descr' at line 2 Not sure what was done wrong, all the syntax seems correct. Thanks.

Answer 1

You're missing semi-colons in your mutli-query statement.

You can add them in front of the queries you are concatenating (.=) for consistency, since the if statement may or may not add a query into the mix.

//insert user input into db
$query = "INSERT INTO test_details (test_title, user_id, likes)
VALUES ('$title', '$user_id', '0')";
$query .= ";INSERT INTO test_descriptions (test_id, description)
VALUES (LAST_INSERT_ID(), '$description')";
if(isset($grade) && isset($difficulty) && isset($subject)) {
    $query .= ";INSERT INTO test_descriptions (test_id, grade, subject, difficulty)
    VALUES (LAST_INSERT_ID(), '$grade', '$subject', '$difficulty')";
}
if(mysqli_multi_query($con, $query)) {
    echo 'Go <a href="../create">back</a> to start creating questions.';
}
else {
    echo "An error occurred! Try again later.";
    echo mysqli_error($con);
}

Or as andrewsi mentioned, the implode method:

//insert user input into db
$query[] = "INSERT INTO test_details (test_title, user_id, likes)
VALUES ('$title', '$user_id', '0')";
$query[] = "INSERT INTO test_descriptions (test_id, description)
VALUES (LAST_INSERT_ID(), '$description')";
if(isset($grade) && isset($difficulty) && isset($subject)) {
    $query[] = "INSERT INTO test_descriptions (test_id, grade, subject, difficulty)
    VALUES (LAST_INSERT_ID(), '$grade', '$subject', '$difficulty')";
}
if(mysqli_multi_query($con, implode( ';', $query ))) {
    echo 'Go <a href="../create">back</a> to start creating questions.';
}
else {
    echo "An error occurred! Try again later.";
    echo mysqli_error($con);
}