”;
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 yiidbActiveRecord::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 −
-
yiidbActiveRecord::findOne() − Returns a single Active Record instance
-
yidbActiveRecord::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 yiidbActiveRecord::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 = ''[email protected]''; $user->save(); var_dump($user->attributes); // update an existing row of data $user = MyUser::findOne([''name'' => ''MyCustomUser2'']); $user->email = ''[email protected]''; $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 yiidbActiveRecord::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 yiidbActiveRecord::deleteAll() method to delete multiple rows of data, for example.
public function actionTestDb() { MyUser::deleteAll(''id >= 20''); }
”;