Yii - get auto increment id

Question

I wish to get the auto increment id from my db and call inside my controller.

The 'id' in db is primary key with auto increment.

I tried:

$id = $model->id;
$id = $model->find('id');
$id = $model->findByPK('id');

But the value is blank, any suggestion for me to get the correct id?

Reason why I need the id value is because I need id mix with other value and save into other column.

Thanks.

Answer 1

$newId = YourModel::find()->max('id') + 1;

Answer 2

AUTO_INCREMENT is a special value which may differ from the ID of the last record.

• If records deleted, the AUTO_INCREMENT will still continue.
• If records failed to insert, the AUTO_INCREMENT will still continue.

Assume you are using MySQL, you can see its value in phpMyAdmin.

1. Open a database, then a table.
2. Switch to the Operations tab.
   
3. Find the AUTO_INCREMENT field under Table options.

So you should obtain its value, especially when you depend on the AUTO_INCREMENT of your database.

For inserting one record, getting the LAST_INSERT_ID() could be an option. But I still don't prefer to do triple operations (insert, read ID, update). Better to minimize the I/O operations (read AUTO_INCREMENT, insert).

In my case, I need to import data from an Excel document. Surely, when performance is important, I cannot depend on the ActiveRecord model. So I use batchInsert() command.

I am adopting this answer for getting the AUTO_INCREMENT value in MySQL.

$lastModelId = Yii::$app->db->createCommand("
    SELECT `AUTO_INCREMENT`
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
    AND   TABLE_NAME   = 'my_table'
")->queryScalar();

If using table prefix:

$lastModelId = Yii::$app->db->createCommand("
    SELECT `AUTO_INCREMENT`
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
    AND   TABLE_NAME   = :TableName
")->bindValues([
    ':TableName' => MyModel::getTableSchema()->name,
])->queryScalar();

Answer 3

The easiest way would be to get the new ID after saving the record. After that you can do what you need and save it again. Sth like this:

<?
$model = new YourModel;
$model->field1 = 'some value';
$model->field2 = 'some value 2';
//...
$model->save();

// now you have the new ID and you can use it
$id = $model->id;

// do what you need e.g.
$model->field3 = $field2 + $id;
$model->save();
?>

Answer 4

this worked for me.

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;

    //or use this

    //$q = "SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" . $table_name . "'";
    //$auto_increment = Yii::$app->db->createCommand($q)->queryScalar();
    //return $auto_increment;
}

Answer 5

I found my own aswer:

$max = Yii::app()->db->createCommand()->select('max(id) as max')->from('TABLENAME')->queryScalar();

$id = ($max + 1);

print_r($id);

Thanks.

Answer 6

You are going about this the wrong way...

$model->id This is available if you have instantiated the $model prior so:

$model = new Model();
$id = $model->id; //this will work if id is the exact name of the column in db

$model->find('id') This finds elements in DB based on the id... but the syntax in wrong so it would throw an error

$model->findByPK('id') Again... find by pk returns a single row from DB if a match with the primary key (which you should have before making this call)

In conclusion, I would recommend the first way but do not forget to instantiate the model first. Otherwise, you can do something like...

$model = Model::model()->findByAttributes(array('some_attribute' => $attribute_value));
$id = $model->id;

Hope this helps!
Keep on coding!
Ares.