PHP, MySql, mysqli insert_id Returns 0 Always

Question

System / development specs;

Windows 7 Ultimate x64 SP1 + all updates
PHP Version 5.5.15 Non-Thread Safe (x64 Experimental)
MySQL Server 5.6 (x64)
Php mysqli

I'm executing a stored procedure that will insert a user name and password into a table with an AUTO_INCREMENT id INT(11) PK field.

PROCEDURE `user_account_create`(IN userName VARCHAR(32), IN userPasskey VARCHAR(254))
BEGIN
    START TRANSACTION;
    INSERT INTO user_account (`name`, passkey) VALUES (userName, userPasskey);
    IF (ROW_COUNT() = 1) THEN COMMIT; ELSE ROLLBACK; END IF;
    SELECT ROW_COUNT() AS affected_rows; -- Used for PHP mysqli's Connection and Statement affected_rows, and num_rows (Statement) fields.
END

I have wrapped mysqli in my own class, in brief;

namespace DataAccess\Broker {

    final class MySqliDb {
        private $conn;

        public function __construct($dbHost, $dbUser, $dbPass, $dataBase) {
            $this->conn = new \mysqli($dbHost, $dbUser, $dbPass, $dataBase);}

        public function ExecuteStatement($cmdText, array $paramValue = null) {
            $affected = -1;
            $stmt = $this->CreateStatement($cmdText, paramValue);
            $stmt->execute();
            // echo 'insert_id' . $this->conn->insert_id;
            $stmt->store_result();
            $affected = $stmt->affected_rows;
            stmt->close();
            return $affected;
        }

        // ... other functions that utilse CreateStatement below

        private function CreateStatement($cmdText, array $paramValue = null) {
            $stmt = $this->conn->prepare($cmdText);

            if ($paramValue !== null) {
                $params = [];
                foreach ($paramValue as $p => &$v) {$params[$p] = &$v;}
                call_user_func_array([$stmt, 'bind_param'], $params);
            }
            return $stmt;
        }

    }  // class
} // namespace

Testing this on an index.php page;

use \DataAccess\Broker\MySqliDb as mysqldb;
$db = new mysqldb('127.0.0.1', 'root', '', 'thedb');
$types = 'ss'; $user_name = 'its_me'; $pass_key = 'a-hashed-password';
echo 'Affected Rows: ' . $db->ExecuteStatement('CALL user_account_create(?,?)', [$types, $user_name, $pass_key]);

Will yield, Affected Rows: 1.

The insert is successful. I require the insert id from this command too, but insert_id for both the mysqli connection and statement are 0. From a var_dump;

var_dump for the connection:

object(mysqli)#2 (19) {
["affected_rows"] => int(1)
["client_info"] => string(79) "mysqlnd 5.0.11-dev - 20120503 - $Id: xxx$" ["client_version"] => int(50011)
["connect_errno"] => int(0) ["connect_error"] => NULL
["errno"] => int(0) ["error"] => string(0) "" ["error_list"] => array(0) { }
["field_count"] => int(1)
["host_info"] => string(20) "127.0.0.1 via TCP/IP" ["info"] => NULL
["insert_id"] => int(0)
["server_info"] => string(6) "5.6.20" ["server_version"] => int(50620)
["stat"] => NULL
["sqlstate"] => string(5) "HY000"
["protocol_version"] => int(10)
["thread_id"] => int(6)
["warning_count"] => int(0)}

var_dump for the Statement:

object(mysqli_stmt)#3 (10) {
["affected_rows"] => int(1)
["insert_id"] => int(0)
["num_rows"] => int(1)
["param_count"] => int(6)
["field_count"] => int(1)
["errno"] => int(0) ["error"] => string(0) "" ["error_list"] => array(0) { }
["sqlstate"] => string(5) "00000"
["id"] => int(1)}

Interestingly there is a field "id", that is returing the required id, from the field id in my table. Can anyone see why insert_id is returning 0.

Thanks and regards, njc

I think the `if` statement affects `row_count()`. Assign the value to a variable right after the update and just use the variable. — Gordon Linoff, Aug 14 '14 at 02:47
@hjpotter92, SELECT LAST_INSERT_ID() does give me the (AUTO_INCREMENT) id. I have adjusted my stored procedure after reading the resposes below from Gorden and Spencer. I'll add to the responses below. — njc, Aug 14 '14 at 03:24

score 1 · Answer 1 · answered Aug 14 '14 at 02:49

1

Try writing the stored procedure with a variable instead of calling row_count() twice:

PROCEDURE `user_account_create`(IN userName VARCHAR(32), IN userPasskey VARCHAR(254))
BEGIN
    START TRANSACTION;
    INSERT INTO user_account (`name`, passkey) VALUES (userName, userPasskey);
    IF ((@rc := ROW_COUNT) = 1) THEN COMMIT; ELSE ROLLBACK; END IF;
    SELECT @rc AS affected_rows; -- Used for PHP mysqli's Connection and Statement affected_rows, and num_rows (Statement) fields.
END;

I think the second call refers to the if statement.

answered Aug 14 '14 at 02:49

Gordon Linoff

1,242,037
58
646
786

+1. The ROW_COUNT() function should be referenced immediately after a SQL statement. A subsequent statement can alter or "clear" the value returned by the `ROW_COUNT()` function. I think it's actually the `COMMIT` statement that's clearing it. Could also use a procedure variable to hold the result, instead of a user-defined variable. – spencer7593 Aug 14 '14 at 02:55
Immediately after the INSERT INTO I entered the following, SET in_id = LAST_INSERT_ID() and SET row_c = ROW_COUNT(), and before the commit. At the end of the procedure, SELECT in_id AS insert_id, row_c AS affected_rows. From MySqlWorkbench, the correct results are yielded. But insert_id is still not coming through in the php. Thank you both. I'll keep trying with your suggestions given. – njc Aug 14 '14 at 03:29

PHP, MySql, mysqli insert_id Returns 0 Always

1 Answers1