'How to detect the last insert ID within a transaction in Yii using DAO?

That's the source code, I need to detect the ID (see the marked position between the two queries below).

$connection = Yii::app()->db;
$transaction=$connection->beginTransaction();
try {

    $q = "INSERT INTO `someTable1` .... ";      
    $connection->createCommand($q)->execute(); // Single Row Inserted

    // HERE!! How to get the last insert ID from query above

    $q = "INSERT INTO `someTable2` ....
          WHERE id = LAST_INSERT_ID_FROM_FIRST_QUERY ";
    $connection->createCommand($q)->execute();

    $transaction->commit();

} catch (Exception $e) {
    // react on exception   
    $trans->rollback();
}

What would be the most suitable way to do that?

phpyiitransactionsdaolastinsertid


Solution 1:[1]

you can try both way,here getLastInsertID is method and lastInsertID is property

$lastInsertID = $connection->getLastInsertID();

or 

$lastInsertID = $connection->lastInsertID;

for more info http://www.yiiframework.com/doc/api/1.1/CDbConnection

Solution 2:[2]

i created this to solve that issue

public static function getAutoIncrement($table_name)
{
    $q = new Query();
    $res = $q->select("AUTO_INCREMENT")
        ->from('INFORMATION_SCHEMA.TABLES')
        ->where("TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" . $table_name . "'")
        ->one();
    if ($res)
        return $res["AUTO_INCREMENT"];
    return false;
}

Solution 3:[3]

For anyone yet interested:

ActiveRecord in saveMethod does this like

\Yii::$app->db->schema->insert($tableName, $values)

It results like

["id" => 1]

Schema in it's way does this:

public function insert($table, $columns)
{
    $command = $this->db->createCommand()->insert($table, $columns);
    if (!$command->execute()) {
        return false;
    }
    $tableSchema = $this->getTableSchema($table);
    $result = [];
    foreach ($tableSchema->primaryKey as $name) {
        if ($tableSchema->columns[$name]->autoIncrement) {
            $result[$name] = $this->getLastInsertID($tableSchema->sequenceName);
            break;
        }

        $result[$name] = isset($columns[$name]) ? $columns[$name] : $tableSchema->columns[$name]->defaultValue;
    }

    return $result;
}

I suggest rather use schema->insert. It supports composite identifiers and uses sequence name for fetching last ID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Always Sunny
Solution 2 yousef
Solution 3

Related Questions