active record provides an object-oriented api for accessing data. an active record class is associated with a database table.

yii provides the active record support for the following relational databases −

• mysql 4.1 or later
• sqlite 2 and 3:
• postgresql 7.3 or later
• microsoft sql server 2008 or later
• cubrid 9.3 or later
• oracle
• elasticsearch
• sphinx

additionally, the active record class supports the following nosql databases −

• redis 2.6.12 or later
• mongodb 1.3.0 or later

after declaring an active record class(myuser model in our case) for a separate database table, you should follow these steps to query data from it −

• create a new query object, using the yii\db\activerecord::find() method.
• build the query object.
• call a query method to retrieve data.

step 1 − modify the actiontestdb() method this way.

public function actiontestdb() {
   // return a single user whose id is 1
   // select * from `user` where `id` = 1
   $user = myuser::find()
      ->where(['id' => 1])
      ->one();
   var_dump($user);
   // return the number of users
   // select count(*) from `user`
   $users = myuser::find()
      ->count();
   var_dump($users);
   // return all users and order them by their ids
   // select * from `user` order by `id`
   $users = myuser::find()
      ->orderby('id')
      ->all();
   var_dump($users);
}

the code given above shows how to use activequery to query data.

step 2 − go to http://localhost:8080/index.php?r=site/test-db, you will see the following output.

querying by primary key values or a set of column values is a common task, that is why yii provides the following methods −

• yii\db\activerecord::findone() − returns a single active record instance

• yi\db\activerecord::findall() − returns an array of active record instances

example −

public function actiontestdb() {
   // returns a single customer whose id is 1
   // select * from `user` where `id` = 1
   $user = myuser::findone(1);
   var_dump($user);
   // returns customers whose id is 1,2,3, or 4
   // select * from `user` where `id` in (1,2,3,4)
   $users = myuser::findall([1, 2, 3, 4]);
   var_dump($users);
   // returns a user whose id is 5
   // select * from `user` where `id` = 5
   $user = myuser::findone([
      'id' => 5
   ]);
   var_dump($user);
}

save data to database

to save data to the database, you should call the yii\db\activerecord::save() method.

step 1 − modify the actiontestdb() method this way.

public function actiontestdb() {
   // insert a new row of data
   $user = new myuser();
   $user->name = 'mycustomuser2';
   $user->email = 'mycustomuser@gmail.com';
   $user->save();
   var_dump($user->attributes);
   
   // update an existing row of data
   $user = myuser::findone(['name' => 'mycustomuser2']);
   $user->email = 'newemail@gmail.com';
   $user->save();
   var_dump($user->attributes);
}

step 2 − go to http://localhost:8080/index.php?r=site/test-db, you will see the following output.

to delete a single row of data, you should −

• retrieve the active record instance

• call the yii\db\activerecord::delete() method

step 1 − modify the actiontestdb() method this way.

public function actiontestdb() {
   $user = myuser::findone(2);
   if($user->delete()) {
      echo "deleted";
   } 
}

step 2 − type http://localhost:8080/index.php?r=site/test-db in the address bar of the web browser, you will see the following output.

step 3 − you can also call the yii\db\activerecord::deleteall() method to delete multiple rows of data, for example.

public function actiontestdb() {
    myuser::deleteall('id >= 20');
}