php - PDO 无法返回last_insert_id

Question

   <?php
    session_start();


            $userid = $_SESSION['userid'];
            $categorynumber=0;
            $categorynumber = create_category($userid);
            //keep the category number for future reference
            if($categorynumber > 0){
                $_SESSION['categorynumber'] = $categorynumber;
            }

            echo $categorynumber;



    ?>

echo $categorynumber 打印 0

    function create_category($userid)
{

    // connect to database with PDO
    $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_DATABASE;
    $dbh = new PDO($dsn, DB_USER, DB_PASSWORD);

    // insert category and get the category_id
    $categorynumber = 0;
    $stmt = $dbh->prepare("INSERT INTO categories (userid, name) VALUES (:userid, :name);SELECT LAST_INSERT_ID();");
    $stmt->bindValue(':userid', $userid, PDO::PARAM_STR);
    $stmt->bindValue(':name', 'test7', PDO::PARAM_STR);
    if ($stmt->execute())
    {
        $result = array();
        while ($row = $stmt->fetch()) {
            array_push($result, $row);
        }

        if (isset($result[0][0]))
        $categorynumber = $result[0][0];

    }   

    // close database and return null 
    $dbh = null;
    return $categorynumber;
}

我在这里做错了什么，以至于我无法检索所创建条目的最后一个 ID？我怎样才能返回last_insert_id();并将其分配给 $_SESSION['categorynumber']??

Answer 1

我已经成功地通过以下行实现了我想要的目标:

$categorynumber = $dbh->lastInsertId();

完整示例:

    function create_category($userid)
{

    // connect to database with PDO
    $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_DATABASE;
    $dbh = new PDO($dsn, DB_USER, DB_PASSWORD);

    // insert category and get the category_id
    $categorynumber = 0;
    $stmt = $dbh->prepare("INSERT INTO categories (userid, name) VALUES (:userid, :name);");
    $stmt->bindValue(':userid', $userid, PDO::PARAM_STR);
    $stmt->bindValue(':name', 'test7', PDO::PARAM_STR);
    $stmt->execute();
    $categorynumber = $dbh->lastInsertId();

    // close database and return null 
    $dbh = null;
    return $categorynumber;
}