LAST_INSERT_ID() result from stored procedure

Question

I have a stored procedure in mysql which is updating a lot of rows and then inserting one (Hierarchical data structure to be specific). Don't look much at the code except for the last one where I am setting the @insert_id variable.

CREATE DEFINER=`root`@`localhost` PROCEDURE `addTaskNextTo`(IN `prev_id` INT, IN `pid` INT, IN `name` VARCHAR(100), IN `type` TINYINT, IN `uid` INT, IN `starting_date` DATE, IN `description` TEXT, OUT `insert_id` INT)
MODIFIES SQL DATA
BEGIN
    SELECT @myRight := rgt FROM tasks WHERE id = prev_id;
    UPDATE tasks SET rgt = rgt + 2 WHERE rgt > @myRight AND pid = pid;
    UPDATE tasks SET lft = lft + 2 WHERE lft > @myRight AND pid = pid;
    INSERT INTO tasks(pid, name, lft, rgt, type, uid, starting_date, description)
          VALUES(pid, name, @myRight + 1, @myRight + 2, type, uid, starting_date, description);
    SET @insert_id = LAST_INSERT_ID();
END

Now when I execute this procedure from phpMyAdmin it returns the correct last id but when I call it from php it returns the id from the previous inserted row??? What could possibly be the problem and is there any better way you suggest I should do it.

Here it is how I call it from PHP:

$sql = "CALL addTaskNextTo($last_tid, $pid, '$task_name', 0, $uid, '$task_start', '$task_desc', @insert_id)";

This itself acts as a SELECT query and I am getting 1 column "insert_id" with one row with value for example "120" which is the ID of the not current inserted row bu the previous one.

Here it's the function that recieves the sql string and returns the single value:

public static function get_single_value($sql) {
    global $db;
    $rez = $db->query($sql);
    return !empty($rez) ? mysql_result($rez,0) : false;
}

Do you `SELECT @insert_id`, or do you use a native `->lastInsertId()` kind of function of one of the drivers? — Wrikken, Mar 14 '13 at 21:15
With the first one of those I get an error "Commands out of sync; you can't run this command now" and the native one just returns zero. The stored procedure itself has the "insert_id" as OUT parameter and I am just doing "CALL procedureName(var1,var2, ..., @insert_id)" which I am treating it as select query. — Vega, Mar 14 '13 at 21:33
(Didn't mean to say to _do_, meant to ask _how_ you retrieve it ;) ). Could you post the _whole_ create procedure + how it is called in your question? — Wrikken, Mar 14 '13 at 21:36
I've placed the whole procedure and the sql string I am doing from PHP — Vega, Mar 14 '13 at 21:55
Since it's PHP, you can get the last insert ID using given methods in PHP itself. — hjpotter92, Mar 14 '13 at 22:05
Hm, I think I see something. What happens if you change `SET @insert_id = LAST_INSERT_ID();` to `SET insert_id = LAST_INSERT_ID();` ? — Wrikken, Mar 14 '13 at 22:33
@Vega please provide the code of how you run the sql in php and how you retrieve the results of the run. — eis, Mar 14 '13 at 22:54
@Wrikken I've updated again with the function that receives the sql string and returns the insert_id value — Vega, Mar 15 '13 at 02:32

score 1 · Answer 1 · edited May 23 '17 at 11:57

I'm not sure how you do it, but you should do it something like

$dbh->query("CALL addTaskNextTo($last_tid, $pid, '$task_name', 0, $uid, '$task_start', '$task_desc', @insert_id)");
$dbh->query("SELECT @insert_id");

note that you need a separate SELECT query to get the updated value of insert id. Otherwise you'd just get the old one.

Or, better yet, something like:

$stmt = $dbh->prepare("CALL addTaskNextTo(?, ?, ?, ?, ?, ?, ?, @insert_id)");
$stmt->execute(array($last_tid, $pid, $task_name, 0, $uid, $task_start, $task_desc));
$dbh->query("SELECT @insert_id");

It's also possible to use bind to have the output param in a PHP variable directly (see the third example in manual).

Edit: If you get a message "Commands out of sync; you can't run this command now", it means you have unbuffered queries turned on. This means that you either need to read the result in between or use buffered queries. To help with that, you can use $stmt->store_result() after the first query, but before running the next one. See more details at this thread.

When trying to run any kind of query after calling the stored procedure I get this error: Database query failed: Commands out of sync; you can't run this command now — Vega, Mar 15 '13 at 02:36

LAST_INSERT_ID() result from stored procedure

1 Answers1