Yii Framework 2 : SQL Select Query

You can use different type of methods to update the database records in yii2.0 framework. You can use model methods, execute() etc.

save() OR update()

Using model methods, You can load existing record and update the necessary changes in that.

$model = User::find($id);
$model->name = 'YII';
$model->email = 'yii2@framework.com';
$model->save();  // equivalent to $model->update();

Update Multiple Records

Using the below methods, You can update the entire table records using given attribute values and condition.
To increment all the users age by 1,

User::updateAllCounters(['age' => 1]);
To change all the users status to active

User::updateAll(['status' => 1], ['like', 'email', '@dummy.com']);

update() command

Instead of writing plain UPDATE query, you can call update() function to change the records. execute() function will complete the update process.

        ->update('table_name', [SET_Values], 'CONDITION')
        ->update('tbl_user', ['status' => 1], 'age > 30')

Update By Sql Query

To run the plain UPDATE query, you can use `createCommand()` function.

$command = $connection->createCommand('UPDATE tbl_user SET status=1 WHERE userid=1');

  • 01011

    When using Query class, it is not necessarily to call createCommand() by yourself, $query->all() do it for you.

  • Luis M

    Hi, i need help As complete a combobox with data from the db?

    • m bala

      Try this
      echo $form->field($model, ‘name’)->dropDownList($listData, [‘prompt’=>’Choose…’]);

      • Luis M

        ty im use field($model, ‘atributename’)->dropDownList(ArrayHelper::map(model::find()->all(), ‘valatributemodel1’, ‘valatributemodel2’)) ?>

  • Saravanan Samidurai

    how to print values

  • Vipin Kumar


  • shiv

    hi guys
    i am in problome fetch two or more tables data in a single page in yii2

  • Nikhilesh Gupta

    SELECT t1.name
    FROM table1 t1
    WHERE t1.id = t2.table1_id
    FROM table2 t2
    WHERE t2.number =67
    how to write this query in active record???

    • Maksim

      * @return array
      public static function getReferrerList()
      $subQuery = (new Query())
      ->from(self::tableName() . ‘ t1’)
      ->where(‘”t1″.”referrer_user_id” = “‘.self::tableName().'”.”id”‘)
      $query = (new Query())
      ‘count’ => $subQuery,
      ‘count’ => SORT_DESC,
      // list ($sql, $params) = self::getDb()->queryBuilder->build($query);
      $result = $query->all();
      return $result;

  • tds

    very thx, it’s working:
    $connection = Yii::$app->db;
    $model = $connection->createCommand(“SELECT QUARTER(‘$date’)”);

    $users_count = $model->queryScalar();

  • kk

    join query for two tables.

  • Ajith

    how to use subqueries in yii2?

  • parthi

    how to show a particular tabledata in database using yii2 framework.

  • Masdi

    Hi, how to generate query like this:
    “SELECT fieldName FROM tableName WHERE fieldName LIKE ‘ADX%LZU’ “

  • Vinai Raj U

    how to insert multiple commands into a prepared statement?
    any one please Help me to do this