当我在mysql中调用STORED PROCEDURE时,为什么pdo-> lastInsertId()返回0? [英] why pdo->lastInsertId() return 0 when i call STORED PROCEDURE in mysql?
本文介绍了当我在mysql中调用STORED PROCEDURE时,为什么pdo-> lastInsertId()返回0?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要数据库中最后插入的行的返回ID:
i need return id of last inseted row in my database :
我有一个名为DatabaseHandler的类来使用pdo
i have a class named DatabaseHandler to use pdo
<?php class DatabaseHandler
{
private static $_mHandler;
private function __construct()
{}
private static function GetHandler()
{
if (!isset(self::$_mHandler))
{
try
{
self::$_mHandler =
new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD,
array(PDO::ATTR_PERSISTENT => DB_PERSISTENCY));
self::$_mHandler->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
self::$_mHandler->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
}
catch (PDOException $e)
{
self::Close();
trigger_error($e->getMessage(), E_USER_ERROR);
}
}
return self::$_mHandler;
}
public static function Close()
{
self::$_mHandler = null;
}
public static function Execute($sqlQuery, $params = null)
{
try
{
$database_handler = self::GetHandler();
$statement_handler = $database_handler->prepare('set names utf8; ' . $sqlQuery);
$statement_handler->execute($params);
$result=$database_handler->lastInsertId();
}catch(PDOException $e){
self::Close();
trigger_error($e->getMessage(), E_USER_ERROR);
}
return $result;
}?>
和mysql db中的存储过程:
and STORED PROCEDURE in mysql db :
CREATE PROCEDURE `sp_users_Insert`(IN _username VARCHAR(50), IN _email VARCHAR(50), IN _password CHAR(32), IN _ip VARCHAR(15), IN _reg_date INT, IN _ref_id MEDIUMINT)
BEGIN
INSERT INTO n_users
(username , email , password , ip , reg_date , ref_id )
VALUES(_username , _email , _password , _ip , _reg_date , _ref_id );
END$$
当我这样调用时,将创建数据库记录,但lastInsertId()返回0:
and when i call that like this , the database record is created but lastInsertId() return 0:
$username =me;
$email=me@me.com;
$password =msadss;
$ip =12.0.0.1;
$reg_date =1347284764;
$ref_id =12;
$sql = 'CALL `sp_users_Insert`(:username,:email,:password,:ip,:reg_date,:ref_id)';
$params = array (':username' => $username, ':email' => $email, ':password' => $password, ':ip' => $ip, ':reg_date' => $reg_date, ':ref_id' => $ref_id);
DatabaseHandler::Close();
return DatabaseHandler::Execute($sql, $params);
我做错了什么?
推荐答案
尝试作为您过程中的最后一个命令:
Try as last command in your procedure:
SELECT LAST_INSERT_ID();
这篇关于当我在mysql中调用STORED PROCEDURE时,为什么pdo-> lastInsertId()返回0?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文