Retrieve last_insert_id when performing multiple queries

Question

I have read the MySQL-bug list, where I found out, that last_insert_id() returns the FIRST auto_increment-value when you're performing multiple insert statements.

(Link: http://bugs.mysql.com/bug.php?id=34319)

My problem is, that in my code, I actually only perform 1 insert query after I've performed some select to get the values for my nested-sets. But the last_insert_id() still returns "0" as ID even though I only have 1 insert query. Do you know what could be the problem? :)

$query = $this->prepare("
    SELECT @myRight := c.Rgt FROM ".DB_PREFIX."C_Categories c
    WHERE c.CategoryID = :cat_id;

    UPDATE ".DB_PREFIX."C_Categories c1 SET c1.Rgt = c1.Rgt + 2 WHERE c1.Rgt >= @myRight;
    UPDATE ".DB_PREFIX."C_Categories c2 SET c2.Lft = c2.Lft + 2 WHERE c2.lft > @myRight;

    INSERT INTO ".DB_PREFIX."C_Categories
        (CategoryName, MetaTag, Description, ParentID, Lft, Rgt)
        VALUES (:cat_name, :cat_tag, :desc, :parent_id, @myRight, @myRight+1);
");
$params = array(
    array('cat_name', $data['name'], 'STR'),
    array('cat_tag', $data['metatag'], 'STR'),
    array('desc', $data['description'], 'STR'),
    array('parent_id', $data['parentid'], 'INT'),
    array('cat_id', $parent_id, 'INT')
);

$this->exec($query, $params);

$id = $this->lastInsertId();

return $id;

Can you put multiple statements in a single query like that? — andrewsi, Sep 30 '13 at 12:37
Well, it doesn't give me any errors - and it performs the INSERT query exactly as I want it to, so yes, I'm quite certain, I can :) — denlau, Sep 30 '13 at 12:38
@andrewsi http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — h2ooooooo, Sep 30 '13 at 12:40
@h2ooooooo - I've not even had my first coffee of the day, and I've already learned something. Thank you for that link! — andrewsi, Sep 30 '13 at 12:44
Can't you put all these multiple statements in separate calls to avoid any possible inconvenience? — Your Common Sense, Sep 30 '13 at 12:51
@YourCommonSense: I don't know - I think my variable-setup with the "myRight" would be the problem. I need this value to calculate the right/left values for the new record. Hmm.. — denlau, Sep 30 '13 at 12:53
@denlau If it's the same MySQL session then the variables you set should remain. — h2ooooooo, Sep 30 '13 at 13:09
@h2ooooooo So I could actually execute all the first queries before executing the insert without losing the possibility of retreiving the data in the variables? :) — denlau, Sep 30 '13 at 13:11
@denlau Yes - that should be possible AFAIC. As long as you don't reconnect to the DB in the meantime (in the same PHP code). That said, I'm unaware if using keepalives in the DB connection would mean that these variables are shared (if you get two requests at the same time). I'd check that simply to make sure. (Some sleep magic and multiple requests should tell you whether or not it's for the single session even though you might use keep alives/reconnects). [Here's the MySQL manual page](http://dev.mysql.com/doc/refman/5.0/en/user-variables.html). *User-defined variables are session-specific* — h2ooooooo, Sep 30 '13 at 13:12
@h2ooooooo if I used a transaction, then I should be clear of those problems, right? :) — denlau, Sep 30 '13 at 13:35

score 0 · Answer 1 · answered Sep 30 '13 at 14:15

Could you use a stored procedure to do all that work? Something like this?

DELIMITER //  

CREATE PROCEDURE spUpdateAndInsertCategory (
    IN cat_id INT, 
    IN parent_id INT, 
    IN cat_name VARCHAR(100), 
    IN cat_tag VARCHAR(100), 
    IN cat_desc VARCHAR(100), 
    OUT lastInsertId INT
)  
BEGIN  

    SELECT @myRight := c.Rgt FROM C_Categories c
    WHERE c.CategoryID = cat_id;

    UPDATE C_Categories c1 SET c1.Rgt = c1.Rgt + 2 WHERE c1.Rgt >= @myRight;
    UPDATE C_Categories c2 SET c2.Lft = c2.Lft + 2 WHERE c2.lft > @myRight;

    INSERT INTO C_Categories
        (CategoryName, MetaTag, Description, ParentID, Lft, Rgt)
        VALUES (cat_name, cat_tag, cat_desc, parent_id, @myRight, @myRight+1);

    SELECT lastInsetId AS SELECT MAX(cat_id) from C_Categories;

END //

score -1 · Answer 2 · answered Sep 30 '13 at 13:58

-1

You can create a #tempTable and put last_insert_ids there and at the end of method/procedure you can return that table.

Hope it helps.

answered Sep 30 '13 at 13:58

Saeedses

109
1
1
8

Retrieve last_insert_id when performing multiple queries

2 Answers2