How to fix syntax error in PHP when statement works fine in MySQL?

Question

I'm using PHP/MySQL to store data collected from a web page. I'm getting

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 'INSERT INTO narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_02

When I take the statement produced by PHP and paste it into the MySQL console, the statement works fine.

Here's the PHP code:

foreach ($files['pictures']['final_name'] as $key => $final_name) {
    $sql .= "INSERT INTO narrative_photos ";
    $sql .= "(`filename`, `narrative_id`) ";
    $sql .= "VALUES (";
    $sql .= "'" . db_escape($db, $final_name) . "', ";
    $sql .= "'LAST_INSERT_ID()'); ";
}

It produces something that looks like this:

INSERT INTO narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_020819-140257.png', 3);

If I paste that into MySQL it works. But if I comment out the PHP code and substitute:

 $sql .= "INSERT INTO narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_020819-140257.png', 3);";

it continues to throw the MySQL error.

I've been playing with this for a couple of hours and I can't figure out where my mistake is. I would appreciate a second set of eyes. Thanks!

EDIT: Here's the entire function for context.

function insert_narrative($narrative, $files) {
    global $db;

    $sql = "INSERT INTO narratives ";
    $sql .= "(date, positive_thing, what_you_did, goals, plan, entered_by, library_id) ";
    $sql .= "VALUES (";
    $sql .= "'" . db_escape($db, $narrative['sqldate']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['positive_thing']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['what_you_did']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['goals']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['plan']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['entered_by']) . "', ";
    $sql .= "'" . db_escape($db, $_SESSION['library_id']) . "'";
    $sql .= "); ";

    if (!empty($files['pictures']['final_name'])) {
        foreach ($files['pictures']['final_name'] as $key => $final_name) {
            $sql .= "INSERT INTO narrative_photos ";
            $sql .= "(`filename`, `narrative_id`) ";
            $sql .= "VALUES (";
            $sql .= "'" . db_escape($db, $final_name) . "', ";
            $sql .= "LAST_INSERT_ID()); ";

        }
    }
    $result = mysqli_query($db, $sql);
    if ($result) {
        return true;
    } else {
        echo mysqli_error($db);
        db_disconnect($db);
        exit;
    }
}

EDIT #2: I just realized that independent of the syntax error my approach isn't going to work because that LAST_INSERT_ID is probably going to pick up the ids for each of those inserts instead of just using the id from the main table. I've modified the function but I'm still getting a syntax error at SET @narrative_id. Here's the code.

$sql = "INSERT INTO narratives ";
$sql .= "(date, positive_thing, what_you_did, goals, plan, entered_by, library_id) ";
$sql .= "VALUES (";
$sql .= "'" . db_escape($db, $narrative['sqldate']) . "', ";
$sql .= "'" . db_escape($db, $narrative['positive_thing']) . "', ";
$sql .= "'" . db_escape($db, $narrative['what_you_did']) . "', ";
$sql .= "'" . db_escape($db, $narrative['goals']) . "', ";
$sql .= "'" . db_escape($db, $narrative['plan']) . "', ";
$sql .= "'" . db_escape($db, $narrative['entered_by']) . "', ";
$sql .= "'" . db_escape($db, $_SESSION['library_id']) . "'";
$sql .= "); ";
$sql .= "SET @narrative_id = LAST_INSERT_ID()";

if (!empty($files['pictures']['final_name'])) {
    foreach ($files['pictures']['final_name'] as $key => $final_name) {
        $sql .= "INSERT INTO narrative_photos ";
        $sql .= "(`filename`, `narrative_id`) ";
        $sql .= "VALUES (";
        $sql .= "'" . db_escape($db, $final_name) . "', ";
        $sql .= "@narrative_id); ";

    }
}

Answer 1

If you're trying to make one query to insert all the values, you need to not include the INSERT part of the query each time, just adding a new set of values instead. You could use something like this. Note that LAST_INSERT_ID() should not be enclosed in quotes, as that will insert the literal string "LAST_INSERT_ID()" instead of the value.

$sql  = "INSERT INTO narrative_photos (`filename`, `narrative_id`) VALUES ";
$values = array();
foreach ($files['pictures']['final_name'] as $final_name) {
    $values[] = "('" . db_escape($db, $final_name) . "', LAST_INSERT_ID())";
}
$sql .= implode(', ', $values);

Notes

I'm assuming that you actually want all of these filenames to end up with the same value in narrative_id, which is going to link back to another table.

Although from the look of it these values have been filtered already (I presume they are actual system filenames), the code is still potentially vulnerable to SQL injection. This question and this question offer some good advice as to how you can use prepared statements with arrays of parameters.

Answer 2

I don't understand why you concatenating each line to a variable instead of just doing an entire statement. Also, in your examples you have used VALUES twice which is incorrect. I can't understand why you are creating the SQL statement inside a loop but you never execute it, which you need to. I don't know what API you are using but here's an example.

if (isset($files['pictures']['final_name'])) {
  foreach ($files['pictures']['final_name'] as $key => $final_name) {
    $sql = "INSERT INTO narrative_photos (`filename`, `narrative_id`) 
            VALUES ('".db_escape($db, $final_name)."', LAST_INSERT_ID())";
    if (!$mysqli->query($sql)) {
      echo "SQL failed: (".$mysqli->errno.") ".$mysqli->error;
    }
  }
} else {
  echo "final name does not exist";
}