存储过程和mysql_insert_id问题 [英] stored procedures and mysql_insert_id issue

问题描述

我的问题是这样的: 我创建了一个从php到mysql的存储过程.现在，在有人对这种做法说些什么之前，该程序可以正常工作. 问题是这样的，当我从php调用该过程时，我知道已输入一条记录mysql_insert_id()，返回0 ...为什么有任何想法?

My problem is this: I Created a store procedure from php to mysql. Now before someone says something about that practice, The procedure works fine. The problem is this, when I call the procedure from php and I KNOW that a record has been entered, mysql_insert_id(), returns 0... Any ideas why?

哦，是的，我的id字段是AUTO_INCREMENT和PRIMARY KEY

Oh, and yes, my id field is AUTO_INCREMENT and PRIMARY KEY

p.s 如果这里有格式化我的代码的简便方法，请告诉我.我将pre放在html中，但效果似乎不太好.

p.s If there's an easy way to format my code in here, pls tell me. I put pre in html, but it doesn't seem to work very well.

请提前

$PROCEDURE_INSERT_UPDATE_BOARD = 
"
CREATE PROCEDURE InsertUpdateBoard(IN `pNewBoardUUID` CHAR(36),
                                   IN `pOldBoardUUID` CHAR(36),
                                   IN `pSimName` VARCHAR(40), 
                                   IN `pOwnerName` VARCHAR(64),
                                   IN `pOwnerUUID` CHAR(36),
                                   IN `pLandmark` VARCHAR(80),
                                   IN `pVersion` VARCHAR(10),
                                   IN `pManagerUUID` CHAR(36),
                                   IN `pBoardURL`    CHAR(80),
                                   IN `pPassword` CHAR(8))
    BEGIN   
        CALL InsertUpdateSims(`pSimName`); 
        CALL InsertUpdateAvatars(`pOwnerName`, `pOwnerUUID`);

        INSERT INTO boards(`boardUUID`, `simId`, `ownerId`, `landmark`,
                                   `version`, `managerId`, `boardURL`, `password`)
                            VALUES(`pOldBoardUUID`,                                      
                            (SELECT rS.id FROM 2starsglobal.sims AS rS
                                    WHERE rS.name=`pSimName`),
                            (SELECT rA.id FROM 2starsglobal.avatars AS rA
                                    WHERE rA.UUID = `pOwnerUUID`),
                            `pLandmark`, `pVersion`,
                            (SELECT rA2.id FROM 2starsglobal.avatars AS rA2 
                                    WHERE rA2.UUID = `pManagerUUID`),
                            `pBoardURL`, 
                            `pPassword`)
        ON DUPLICATE KEY UPDATE `boardUUID`=`pNewBoardUUID`, 
                    `landmark`=`pLandmark`, `version`=`pVersion`,
                    `boardURL`=`pBoardURL`; 
    END
";

还有Php代码

function InsertUpdateBoard($boardNewUUID, $boardOldUUID, $simName, 
                           $ownerName, $ownerUUID, $boardLandmark,
                           $versionNumber, $managerUUID, $boardURL)
{ 
    $password = generatePassword(8);

    $query = "CALL InsertUpdateBoard('$boardNewUUID', '$boardOldUUID', '$simName', 
                                         '$ownerName', '$ownerUUID', '$boardLandmark', 
                                         '$versionNumber', '$managerUUID', '$boardURL',
                                         '$password')";

    mysql_query($query) or die("ERROR:QUERY_FAILED " . mysql_error());  

    if(mysql_affected_rows() > 0)
    {
        if(mysql_insert_id() > 0)
        {    
            echo "SUCCESS,$password";
        }
        else 
        {
            echo "SUCCESS"; 
        }
    }
    else
    {
        echo 'FAILED:BOARD_REGISTRATION_UPDATE'; 
    }       

}

推荐答案

与Google的十秒钟发现

Ten seconds with google found this page

请注意，如果您调用MySQL存储过程以插入新记录 然后引用$ db-> insert_id;你会得到0，而不是最后一个 插入的ID.

Note that if you Call a MySQL stored procedure to insert a new record and then reference $db->insert_id; you will get 0 back, not the last inserted ID.

因此有必要在您的MySQL存储过程中添加一行 例如

It is therefore necessary to add a line to your MySQL Stored Procedure such as

选择last_insert_id()作为intRecordKey;

select last_insert_id() as intRecordKey;

在插入之后，以便查询将返回新的键值.

after the insert so that the query will return the new key value.

这篇关于存储过程和mysql_insert_id问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！