0

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

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?

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

@rgt:=number,@lft:=number2

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

what i'm doing wrong?

Marco C
  • 45
  • 1
  • 8
  • 2
    `mysql_insert_id()` in PHP doesn't work? (or `$mysqli->insert_id` if you use MySQLi) – Martin Majer Dec 11 '13 at 18:31
  • Is it a MySQL database? If so, what extension are you using, ie mysql, mysqli, pdo etc? – Rwd Dec 11 '13 at 18:34
  • @MartinMajer i used it directly at the end of the procedure, like select last_insert_id() into last_id or set set last_id = last_insert_id(), where last_id is the out param. – Marco C Dec 11 '13 at 19:22
  • @RossWilson yes it is, anyway i'm using mysqli – Marco C Dec 11 '13 at 19:25

3 Answers3

1

You should be able to use mysql_insert_id‎ to fetch the id for the last insert statement.
Reference: http://php.net/mysql_insert_id‎

However, mysql_* functions are, now, deprecated (read below) and you should use an equivalent mysqli_* function or method. The equivalent for the above function would be: $mysqli->insert_id()
Reference: http://www.php.net/manual/en/mysqli.insert-id.php

The mysql_* category of functions have been deprecated, and the warning is reproduced here:

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

Stoic
  • 10,536
  • 6
  • 41
  • 60
  • yes i'm sorry i miss to tell you i'm using mysqli, i have function in my db class that return $mysqli->insert_id() but it doesn't work , result 0. Instead when i do an insert query (without calling procedure) it works – Marco C Dec 11 '13 at 19:30
  • @MarcoC If you look at my answer below, I believe it will help you out. – Rwd Dec 11 '13 at 22:14
  • @RossWilson thank you , but in my case it doesn't work because i'm using "insert" inside the procedure. So i tried both and (in this case) my answer is the correct one. However your answer is correct but just when you execute the insert command as you suggest. – Marco C Dec 19 '13 at 14:41
1

If you're using MySQLi then your would have something like this.

Procedural:

$link = mysqli_connect($db_host, $db_user, $db_pass, $db_table);

$query = "INSERT INTO table VALUES($val, $val2, $val3)";
mysqli_query($link, $query);
$id = mysqli_insert_id($link);

OOP:

$mysqli = new mysql($db_host, $db_user, $db_pass, $db_table);

//query
$id = $mysqli->insert_id;

Hope this helps!

Let me know if you have any issues.

Rwd
  • 34,180
  • 6
  • 64
  • 78
0

Ok i solved it!

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

select last_insert_id() into last_id;

In PHP i execute:

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

and finally

$lid = $db ->execQuery('select @lastId;');
Marco C
  • 45
  • 1
  • 8