Syntax error with simple mysql transaction

Question

I can't seem to figure out what the issue is. I am fairly new to transactions, it seems I need one for inserting the next auto incremented ID.

Here's the query:

START TRANSACTION;
INSERT INTO posts (title, text, user, club, time) 
VALUES ('$title', '$text', '$name', '$club', '$time');
INSERT INTO post_likes (user, post) 
VALUES ('$name', LAST_INSERT_ID());
COMMIT;

The syntax error cuts me off right before INSERT INTO posts.

Answer 1

PDO's query() and prepare() methods only support single SQL statements per execution, but by calling START TRANSACTION/COMMIT, together with the error message pointing to incorrect syntax near INSERT, we must assume you are attempting to execute all of those statements at once. That won't work, as PDO won't separate them on the ; delimiter before execution.

PDO supplies API methods to handle the transaction in code, rather than needing to execute SQL statements, via PDO::beginTransaction() and PDO::commit(). So you must separate the two INSERT statements, and wrap them in the transaction methods, calling query() or prepare()/execute() twice.

// Assuming your PDO connection is $pdo...
$pdo->beginTransaction();
$pdo->query("INSERT INTO posts (title, text, user, club, time)  VALUES ('$title', '$text', '$name', '$club', '$time')");
$pdo->query("INSERT INTO post_likes (user, post)  VALUES ('$name', LAST_INSERT_ID())");
$pdo->commit();

Now, not seeing your original PDO code, I am making an assumption about how you're currently executing it using variables directly. We can make a major improvement.

PDO has excellent support for prepared statements, which offer strong protection against SQL injection. Let's switch out the two query() calls for prepare()/execute() using named parameters. Additionally, if you wish, you may substitute $pdo->lastInsertId() for MySQL's native LAST_INSERT_ID() if you wish.

$pdo->beginTransaction();

// Prepare the first statement
$stmt = $pdo->prepare('INSERT INTO posts (title, text, user, club, time) VALUES (:title, :text, :user, :club, :time)');
// Execute the statement with an array of bound values
$stmt->execute(array(':title' => $title, ':text' => $text, ':user' => $user, ':club' => $club, ':time' => $time));

// Prepare & execute the second one
$stmt = $pdo->prepare('INSERT INTO post_likes (user, post)  VALUES (:name, :post)');
$stmt->execute(array(':name' => $name, ':post' => $pdo->lastInsertId()));

// Commit it
$pdo->commit();