mysqli-affected_rows does not work with stored procedure

Question

mysqli-affected_rows does not work with stored procedure

Navigation

#1 by (2 votes)

3

I have a piece of code that inserts a record in the persona (id_persona, apellidos, nombres) table, I'm using MySQLi and PHP5 and I have the following:

$query="CALL persona('".$nombres."','".$apellidos."')";
$mysqli->query($query);
$filas = $mysqli->affected_rows;   //Aquí siempre me muestra 1, se inserte o no el registro
$id_insertado = $mysqli->insert_id //Siempre me muestra 0

pd. if the record is inserted with that stored procedure.

But if I change for example to this:

$query="INSERT INTO persona(nombres,apellidos)values('".$nombres."','".$apellidos."')";
$mysqli->query($query);
$filas = $mysqli->affected_rows;    //Funciona bien, devuelve el número de registros insertados
$id_insertado = $mysqli->insert_id; //Me muestra el id insertado

My procedure is as follows:

CREATE PROCEDURE persona(
        IN pnombres TEXT,
        IN papellidos TEXT,
    )
    COMMENT ''
BEGIN

INSERT INTO 
  persona
(
  nombres,
  apellidos
VALUE (
  pnombres,
  papellidos);

END;

I would like to please support me with this doubt, am I doing something wrong, or do I have to add something to the stored procedure, or is there some other way to be able to know that the record was actually inserted? Whether in MySQL or PHP, apart from consulting the latest ID, with MySQL functions.

mysql php mysqli

asked by MasSoft 28.12.2015 в 06:22

source

1 answer

Android - Toolbar with opacity and blur Send token Jwt from php

score 2 · Answer 1

As indicated by ratlab in another answer, the code of the stored procedure is incorrect, but by doing tests I verified that even with the corrected code, the record was inserted but mysqli->insert_id returned 0 instead of the last id. The problem is not the code of the stored procedure itself, but how MySQL works when you insert from a procedure.

As specified in this StackOverflow response (in English) and that you can find in Google Code Files ( is no longer available in the link given in that answer), the error you describe occurs because the INSERT occurs within a stored procedure.

I leave here an approximate translation of the original text:

Note that if you call a procedure stored in MySQL to insert a new record and then you do $db->insert_id; you will get a value of 0 and not the last ID that was inserted.

It is therefore necessary to add a line to the stored procedure such as
select last_insert_id() as intRecordKey;
after the INSERT so that the query returns the new value.

To solve this problem you would have two options:

A) Select and return the last ID inserted in the stored procedure

As suggested in the comment, the idea would be to return the result of a SELECT of the last inserted ID, which can be obtained directly in the function LAST_INSERT_ID() .

Thus, the stored procedure would change slightly:

DELIMITER ;;

CREATE PROCEDURE persona (
        IN pnombres TEXT,
        IN papellidos TEXT
   )
BEGIN
    INSERT INTO persona (nombres, apellidos) VALUES (pnombres, papellidos);
    SELECT LAST_INSERT_ID() AS ultimoid;
END

;;

And PHP would also need a small modification:

$query = "CALL persona('".$nombres."','".$apellidos."')";
$resultado = $mysqli->query($query);
$filas = $mysqli->affected_rows;
$id_insertado = $resultado->fetch_assoc()["ultimoid"];

B) Return the last ID as an output parameter of the stored procedure

Personally I would go with option A, but as an alternative you could also change the stored procedure so that it had an output parameter (eg: OUT ultimo_id INT ), initialize it using SET and function + and then read the value of said parameter in a new query() .

In this case the stored procedure would change to:

DELIMITER ;;

CREATE PROCEDURE persona (
        IN pnombres TEXT,
        IN papellidos TEXT,
        OUT ultimoid INT
   )
BEGIN
    INSERT INTO persona (nombres, apellidos) VALUES (pnombres, papellidos);
    SET ultimoid = LAST_INSERT_ID();
END

;;

And the PHP would change to:

$query = "CALL persona('".$nombres."','".$apellidos."', @ultimoid)";
$mysqli->query($query);
$filas = $mysqli->affected_rows;
$result = $mysqli->query("SELECT @ultimoid");
$id_insertado = $result->fetch_assoc()["ultimoid"];