access LAST_INSERT_ID() from php after calling a stored procedure

Question

hi all,

i have defined a stored procedure (let's call it proc_create_node(parent INT)) in mysql which does an insertion.

when i call it from the mysql cli, doing the following works just fine:

CALL proc_create_node(12);
SELECT LAST_INSERT_ID();

and i get the last inserted id:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|               15 | 
+------------------+

now, when i call these lines from php, or if i use the mysql_insert_id() php function, i always get back 0.

what am i missing? how could i get the last inserted id within php?

yours ... pierre

Answer 1

it is working here:

$link = mysql_connect('localhost', 'root', '');
if (!$link) {
   die('Not connected : ' . mysql_error());
}

$db_selected = mysql_select_db('test', $link);
if (!$db_selected) {
   die ('Can\'t use test : ' . mysql_error());
}
$result = mysql_query('CALL blub(12)');
if (!$result) {
   die('Invalid query: ' . mysql_error());
}
$result = mysql_query('SELECT LAST_INSERT_ID()');
if (!$result) {
   die('Invalid query: ' . mysql_error());
}

print_r(mysql_fetch_assoc($result));

prints an increasing integer. mysql 5.1.30 and php 5.2.9-1.

so the problem must be in your php code, in your stored procedure (are you passing valid parameters from php?) or in your versions of php or mysql (where there happen to be quite some bugs).

btw: replacing SELECT LAST_INSERT_ID() with mysql_insert_id() is not working here.

Answer 2

It might be something to do with the database connection - doesn't LAST_INSERT_ID() function work on a per connection basis?

Do you think it would be a good idea to add the SELECT LAST_INSERT_ID() into the stored procedure and modify the stored procedure to return said id?

Answer 3

Check out the usage of mysqli_multy_query here: http://ua.php.net/manual/en/mysqli.multi-query.php

This function is designed to work with multi-statement queries like yours. But to use that you need to work through php_mysqli module and not through php_mysql which is not simple if you support old application.

Second - you can put SELECT LAST_INSERT_ID() in to your stored procedure and receive it in execution result.

Both ways are worth to try but second one is much simple and works with php_mysql.