Databases
StackOverflow2013
Postsdbo
POLAST_INSERT_ID() result from stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLAST_INSERT_ID() result from stored procedure
    primarykey
    data
    text
    <p>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.</p> <pre><code>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 &gt; @myRight AND pid = pid; UPDATE tasks SET lft = lft + 2 WHERE lft &gt; @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 </code></pre> <p>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.</p> <p>Here it is how I call it from PHP: </p> <pre><code>$sql = "CALL addTaskNextTo($last_tid, $pid, '$task_name', 0, $uid, '$task_start', '$task_desc', @insert_id)"; </code></pre> <p>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.</p> <p>Here it's the function that recieves the sql string and returns the single value:</p> <pre><code>public static function get_single_value($sql) { global $db; $rez = $db-&gt;query($sql); return !empty($rez) ? mysql_result($rez,0) : false; } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USVega
    1. USVega
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLAST_INSERT_ID() result from stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLAST_INSERT_ID() result from stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo you `SELECT @insert_id`, or do you use a native `->lastInsertId()` kind of function of one of the drivers?
      singulars
      1. POLAST_INSERT_ID() result from stored procedure
      1. USWrikken
    2. COWith 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.
      singulars
      1. POLAST_INSERT_ID() result from stored procedure
      1. USVega
    3. CO(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?
      singulars
      1. POLAST_INSERT_ID() result from stored procedure
      1. USWrikken
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload