Search the Community

I have not seen any examples that show how to return the value for the last Identity that was last generated, how do i get the last generated ID. I need example for calling stored procedures? The current INSERT a Record into the database but doesn't retrieve the ID that Generated. Sample SQL ran from work bench, record is created SET @Id := 0; call uspIns_ExcelExport(@Id, 'Admin', 'Filename', 0,0,null,''); SELECT LAST_INSERT_ID(); Table delimiter $$ CREATE TABLE `excelexport` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `userId` varchar(100) DEFAULT NULL, `filename` varchar(256) DEFAULT NULL, `success` bit(1) DEFAULT NULL, `started` bit(1) DEFAULT NULL, `finished` datetime DEFAULT NULL, `error` varchar(2000) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8$$ Store Procedure: DELIMITER $$ /**** INSERT Stored Procedures ****/ CREATE PROCEDURE `uspIns_ExcelExport`( OUT OUT_Id BIGINT, IN IN_userId VARCHAR(100), IN IN_filename NVARCHAR(256), IN IN_success INT, IN IN_started INT, IN IN_finished DATETIME, IN IN_error NVARCHAR(2000)) BEGIN INSERT INTO `ExcelExport` (`userId`, `filename`, `success`, `started`, `finished`, `error`) VALUES (IN_userId, IN_filename, IN_success, IN_started, IN_finished, IN_error); SELECT * FROM `ExcelExport` WHERE `Id` = LAST_INSERT_ID(); END$$ PHP MySQL Code public function Create() { $returnValue = false; if (isset($this->userId)) { $mysqli = new mysqli(__MYSQL_HOST__, __MYSQL_USERNAME__, __MYSQL_PASSWORD__, __MYSQL_DBNAME__); if (!$mysqli->connect_errno){ $Id = -1; $stmt = $mysqli->prepare("call uspIns_ExcelExport (?,?,?,?,?,?,?)"); if ( !$stmt ) { die('mysqli error: '.$mysqli->errno); } if (!$stmt->bind_param('issiiss', $Id, $this->userId, $this->filename, intval($this->success), intval($this->started), $this->finished, $this->error)){ die( 'stmt error: '.$stmt->errno ); } if (!$stmt->execute()){ die( 'stmt error: '.$stmt->errno ); } $rtnId = 0; $stmt->store_result(); $stmt->bind_result($rtnId); while ($stmt->fetch()){ printf ("%s\n", $rtnId); } $stmt->free_result(); $stmt->close(); // $this->Id = $stmt->insert_id; } else{ echo "Failed to connect to MySQL: " . mysqli_connect_error(); } /* close connection */ mysqli_close($cn); if ($this->Id > 0) $this->exist = true; if ($this->Id > 0) $returnValue = true; } return $returnValue; }