如何从PDO获取最后插入的行ID

Question

我正在使用PHP的MVC结构，想要检索最后插入的行ID。

我已经创建了以下SQL代码：

$sql = "INSERT INTO song (artist, track, link) VALUES (:artist, :track, :link)";
$query = $this->db->prepare($sql);
$query->execute(array(':artist' => $artist, ':track' => $track, ':link' => $link));

echo $query->lastInsertId(); // To retrieve last inserted row ID.

但不幸的是，我遇到了这个错误：Fatal error: Call to undefined method PDOStatement::lastInsertId()

我也尝试过这个stack链接，但对我没有用，所以如果您能帮我检索ID，我将非常高兴。

我还在此处分享我的controller.php文件。

/**
 * This is the "base controller class". All other "real" controllers extend this class.
 */
class Controller{
    /**
     * @var null Database Connection
     */
    public $db = null;

    /**
     * Whenever a controller is created, open a database connection too. The idea behind is to have ONE connection
     * that can be used by multiple models (there are frameworks that open one connection per model).
     */
    function __construct(){
        $this->openDatabaseConnection();
    }

    /**
     * Open the database connection with the credentials from application/config/config.php
     */
    private function openDatabaseConnection(){
        // set the (optional) options of the PDO connection. in this case, we set the fetch mode to
        // "objects", which means all results will be objects, like this: $result->user_name !
        // For example, fetch mode FETCH_ASSOC would return results like this: $result["user_name] !
        // @see http://www.php.net/manual/en/pdostatement.fetch.php
        $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);

        // generate a database connection, using the PDO connector
        // @see http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/
        $this->db = new PDO(DB_TYPE . ':host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS, $options);
    }

    /**
     * Load the model with the given name.
     * loadModel("SongModel") would include models/songmodel.php and create the object in the controller, like this:
     * $songs_model = $this->loadModel('SongsModel');
     * Note that the model class name is written in "CamelCase", the model's filename is the same in lowercase letters
     * @param string $model_name The name of the model
     * @return object model
     */
    public function loadModel($model_name){
        require 'application/models/' . strtolower($model_name) . '.php';
        // return new model (and pass the database connection to the model)
        return new $model_name($this->db);
    }
}

- Mr.Happy

---

尝试使用$this->DB-lastInserId() - 您不是在语句上使用它，而是在数据库连接上使用。 - andrewsi

现在我遇到了两个错误：1.注意：未定义属性：listings_for_sale_model :: $ DB 2.致命错误：调用未定义的函数lastInserId()。 - Mr.Happy

$this->DB-lastInsertId()（他忘记了insert单词末尾的t） - Tanatos

叹气 - 那只是打错字了。应该是 $this->db->lastInsertId()。您已经使用了正确的函数，只是没有在正确的对象上调用它。 - andrewsi

@andrewsi，工作完美。现在我可以获取最后插入的行ID :) - Mr.Happy

@andrewsi 我建议你把你的评论发表为答案 :) - Barranka

Answer 1

你离目标很近了。

如果你查看lastInsertId的手册页面，会发现它是在数据库句柄上调用的 - 而你当前正在对语句进行调用。

你只需要调用：

$this->db->lastInsertId();

- andrewsi

---

@Alagaros - 谢谢。我已经修改了 - 我不确定它是否不区分大小写，但确保答案与文档匹配没有坏处。 - andrewsi

这是被接受的答案，但它不能与所有PDO驱动程序一起使用。例如：Firebird。 - migli

Answer 2

您可以尝试以下操作 -

$query = "INSERT INTO song (artist, track, link) VALUES (:artist, :track, :link)";
$stmt = $pdo->prepare($query);

$params = array(
    "artist" => $artist,
    "track" => $track,
    "link" => $link,
);

$data = $stmt->execute($params);

$insert_id = $pdo->lastInsertId();

- Mithun Sen