从最后插入的行中获取最后一个插入ID [英] Get last insert id from last inserted row

问题描述

我又在这里，我快疯了！ 我有这个存储过程:

I'm here again and I'm getting crazy! I have this stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `aggiungi_categoria`(IN   `id_categoria_principale` INT, IN `nome_categoria` VARCHAR(150), IN `sigla_categoria`    VARCHAR(10), IN `stato_categoria` INT) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY   DEFINER
begin

select @rgt := rgt,@lft := lft from categorie where id = id_categoria_principale;

if @rgt - @lft = 1 then

UPDATE categorie SET rgt = rgt + 2 WHERE rgt > @lft;
UPDATE categorie SET lft = lft + 2 WHERE lft > @lft;

insert into categorie (nome,sigla,stato,lft,rgt)
values(nome_categoria,sigla_categoria,stato_categoria,@lft+1,@lft+2);

 ELSE

 UPDATE categorie SET rgt = rgt + 2 WHERE rgt > @rgt-1;
 UPDATE categorie SET lft = lft + 2 WHERE lft > @rgt-1;

 insert into categorie (nome,sigla,stato,lft,rgt) values
(nome_categoria,sigla_categoria,stato_categoria,@rgt,@rgt+1);

 end if;


 end

我如何获得最后的插入ID ???我尝试了所有操作，没有参数，或者在表上使用max(id)进行了选择，或者选择了"set last_id = last_insert_id()"，但是我不知道如何通过php来获取它?

how can i get the last insert id??? i tried all, with out param or doing a select with max(id) on the table or "set last_id = last_insert_id()" but i don't know how to get it via php?

当我在phpmyadmin中调用该过程时，我得到的是类似的东西

when i call the procedure in phpmyadmin i'm getting something like

@rgt:=number,@lft:=number2

在php中我显然在数组中得到了相同的结果.

and in php obviously i get the same result in array.

我做错了什么?

推荐答案

好，我解决了！

我添加了一个名为last_id的int输出参数， 在结束标记之前，我加入了以下语句:

i added an int output parameter called last_id, before the end tag i included this statement:

select last_insert_id() into last_id;

在PHP中，我执行:

$db -> execQuery('call aggiungi_categoria('.$parametri['id_categoria_principale'].',"'.$parametri['nome'].'","'.$parametri['sigla'].'",'.$parametri['stato'].',@lastId);');

最后

$lid = $db ->execQuery('select @lastId;');

这篇关于从最后插入的行中获取最后一个插入ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！