8

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?

user3702874
  • 83
  • 1
  • 3
  • Have you tried using getLastInsertID() from CDbConnection class? (http://www.yiiframework.com/doc/api/1.1/CDbConnection#getLastInsertID-detail) – AleksanderKseniya Jun 03 '14 at 11:44

4 Answers4

6
$lastInsertID = $connection->getLastInsertID();
  • this doesnt work when using after batch insert because batch insert works like stack and even if you getLastInsertID it returns the records which is actually the first new record in database – Mike Ross Oct 19 '15 at 03:33
  • but this is an another use case –  Oct 19 '15 at 08:31
2

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

A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
1

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;
}
yousef
  • 1,240
  • 12
  • 13
1

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

Oleg
  • 45
  • 6