Databases
StackOverflow2013
Postsdbo
POstored procedures and mysql_insert_id issue
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POstored procedures and mysql_insert_id issue
    text
    copied!<p>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?</p> <p>Oh, and yes, my id field is AUTO_INCREMENT and PRIMARY KEY</p> <p>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.</p> <p>Ty in advance.</p> <pre> $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 "; </pre> <p>And the Php code</p> <pre><code>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() &gt; 0) { if(mysql_insert_id() &gt; 0) { echo "SUCCESS,$password"; } else { echo "SUCCESS"; } } else { echo 'FAILED:BOARD_REGISTRATION_UPDATE'; } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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