获取带有准备好的语句和存储过程的InsertId? [英] Get the InsertId with Prepared Statements AND Stored Procedures?

问题描述

我有一些使用MySQLi在PHP中运行的Prepared Statements.需求已更改，现在我应该将它们作为存储过程移至数据库.对于大多数PS来说，这很好用，除了一对读取insertId进行进一步处理的夫妇. 例如:

I had some Prepared Statements working in PHP using mysqli. The requirements changed and now I'm supposed to move them to the DB, as Stored Procedures. This worked fine for most of the PSs, except for a couple that read the insertId for some further processing. Ex:

$idAsdf = $stmtAsdf->insert_id;

其中PS执行INSERT操作. 我尝试在该过程上使用OUT参数，该参数在PHPMyAdmin上可以正常工作，但是无法将其与数据库外部的PHP服务器代码连接.我还没有找到完成此元素组合的任何示例.如何同时使用SP和PS获得此insertId?

where the PS performs an INSERT operation. I've tried using an OUT parameter on the procedure which works fine on PHPMyAdmin, but can't connect it with the PHP server code outside the DB. I haven't found any example of this combination of elements being done. How can I get this insertId using both SPs and PSs?

谢谢

推荐答案

对于PDO准备语句，您可以使用PDO :: lastInsertId-

For PDO Prepared Statement you can use PDO::lastInsertId -http://php.net/manual/en/pdo.lastinsertid.php

<?php 
try { 
$dbh = new PDO('mysql:host=localhost;dbname=test', 'username', 'password'); 

$stmt = $dbh->prepare("INSERT INTO test (name, email) VALUES(?,?)"); 

try { 
    $dbh->beginTransaction(); 
    $tmt->execute( array('user', '[e-mail]')); 
    print $dbh->lastInsertId();
    $dbh->commit(); 
} catch(PDOExecption $e) { 
    $dbh->rollback(); 
    print "Error!: " . $e->getMessage() . "</br>"; 
} 
} catch( PDOExecption $e ) { 
print "Error!: " . $e->getMessage() . "</br>"; 
} 
?>

请记住，使用事务时，在提交之前返回lastInsertId或存储lastInsertId.

Just remember when using transaction return lastInsertId or store lastInsertId before commit.

对于存储过程-使用LAST_INSERT_ID();

For Stored Procedure - use LAST_INSERT_ID();

BEGIN
  INSERT INTO test (name, email) VALUES ('value1', 'value2');
  SET out_param = LAST_INSERT_ID();
END

如果您使用的是MySQLi，请使用mysqli_insert_id- http://php.net /manual/en/mysqli.insert-id.php

If you using MySQLi - then use mysqli_insert_id - http://php.net/manual/en/mysqli.insert-id.php

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$stmt = mysqli->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "[e-mail]";
$stmt->execute();

printf ("New Record has id %d.\n", $stmt->insert_id);

/* close connection */
$mysqli->close();

如果out_param遇到问题，请使用select返回最后一个插入ID作为结果.

If facing problem with out_param, use select to return last insert id as result.

BEGIN
  DECLARE last_id INT DEFAULT 0;
  INSERT INTO test (name, email) VALUES ('value1', 'value2');
  SET last_id = LAST_INSERT_ID();
  SELECT last_id;
END

如果在检索存储过程结果集时遇到问题，请使用以下代码-

If you are facing problem in retrieving Stored Procedure result set use following code -

if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
           $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

要使用以下代码访问out参数-

To access the out param use follwing code -

// execute the stored Procedure
// @uid - IN param, @userCount - OUT param
$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter
$r = $connect->query('SELECT @userCount as userCount');
$row = $r->fetch_assoc();               

$toRet = ($row['userCount'] != 0);

这篇关于获取带有准备好的语句和存储过程的InsertId?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！