34562

stored procedures and mysql_insert_id issue

Question:

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?

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

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.

Ty in advance.

$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 ";

And the Php 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() > 0) { if(mysql_insert_id() > 0) { echo "SUCCESS,$password"; } else { echo "SUCCESS"; } } else { echo 'FAILED:BOARD_REGISTRATION_UPDATE'; } }

Answer1:

Ten seconds with google found <a href="http://php.net/manual/en/mysqli.insert-id.php" rel="nofollow">this page</a>

<blockquote>

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.

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

select last_insert_id() as intRecordKey;

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

</blockquote>

Recommend

  • Win32 async client incoming data processing
  • Memory usage of storing strings as varchar in MySQL
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • How to find data from last week in MySQL
  • Identifier too long in Oracle
  • How do I display a dialog that asks the user multi-choice questıon using tkInter?
  • Cast between interfaces whose interface signatures are same
  • Custom preprocessing in caret
  • how does System.Web.HttpRequest::PathInfo work?
  • Why use database factory in asp.net mvc?
  • List images(01.png) and descriptions(01.txt) from directory
  • Eliminate partial duplicate rows from result set
  • Instantiate interface in JAVA?
  • Sequential (transactional) API calls in angular 4 with state management
  • Access Android Market through SSH tunnel
  • Insert new calendar with SyncAdapter- Calendar API Android
  • xtable package: Skipping some rows in the output
  • How integrated is Collada to OpenGL ES
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • Blackberry - Custom EditField Cursor
  • Test if a set exists before trying to drop it
  • D3 nodes and links from JSON with nested arrays of children
  • How to create a file in java without a extension
  • Alert pop up with LWUIT
  • NHibernate Validation Localization with S#arp Architecture
  • ilmerge with a PFX file
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • Adding a button at the bottom of a table view
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Read text file and split every line in MSBuild
  • req.body is undefined - nodejs
  • Counter field in MS Access, how to generate?
  • Get object from AWS S3 as a stream
  • Java applet as stand-alone Windows application?
  • MySQL WHERE-condition in procedure ignored
  • Adding custom controls to a full screen movie
  • Comma separated Values
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • how does django model after text[] in postgresql [duplicate]